SSIS Performance Enhancement- Remove Sort Column Additional Steps

Writing again after a long time, today while working on a SSIS package i came across a situation where i have a Type2 Dimension and i was getting duplicates rows to the table the only difference was BUsiness Key column was behaving as a case sensitive column i.e. if any row on source was found to change in case the code was treating this business Key as a new row and inserting whereas i needed it to ignore case.

Now here is the reason why it was happening and the resolution,

Cause: I was using the best practices for my Merge Join on the package wherein i have removed the SORT column and to achieve the same i was ordering it on my SOurce Trasnform and marking the ISSorted property and sorting the dataset from source. For details on what i mean read this

https://technet.microsoft.com/en-us/library/ms141031%28v=sql.105%29.aspx

My Data Flow

DataFlow

My SQL server is case insensitive, and SSIS takes by Default windows Sorting, so i was getting the case sensitive row as a new row as the key column changed from ABC to abc,

So to get this resolved what we have to do is set the, comparison Flag property to IGnore case and this would lead to ignore of case for the Key column, as per my DFT above this would have to done on both the source transform, such that setting is identical on both of them

Comparison_Flag

The link helped me resolve it:

http://microsoft-ssis.blogspot.com/2011/03/sorting-in-sql-vs-sorting-in-ssis.html

Advertisements

Consume Webservice via SSIS Script Component

I have always believed SSIS Script component is a brilliant task and it provides for accessing data from any type of source. I have used it as a SOURCE Component very recently to consume data from Webservice, and it required a bit of a tweak to overcome the limitations on this Script component. viz:

Script Component doesn’t support reading data from a Config file, you can have a look at the problem i had published here

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/75111c36-3d92-4440-a7b3-5cfe8a648403

The scenario i will explain below, would consume a WebService which is a WCF service targeted at Framework 3.5, and use HTTPBINDING.

So how to we proceed step wise as:

1. Create a DFT and drop a DFT task.

2. Inside the DFT place the Script Component, select the SOURCE

Image

3) Now open the Script component to edit the Script, look for Add Service Reference on the Reference section if you dont see it change the project to target Framework 3.5, once done you would be able to see the AddSerivceRefrence

Image

4. Adding the Service Reference would lead to adding a file a App.config on the project, which would have the bindings and Contract settings for the web service, Genrally you would use this and from code call the settings stored on this file to access the Webservices.

Image

THIS IS THE PROBLEM WE CANT CALL THE WEBSERVICE NORMAL WAY AS SSIS COMPONENT TASK DOESNT RECOGNISE THE APP.CONFIG file,

Resolution is to define the bindings at runtime so use a code like below, wherein you define the BINDINGS via code rather then depending on the App.config file.

You would need to use the code below for BASICHTTPBINDING

BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportWithMessageCredential);
binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
binding.MaxBufferSize = 2147483647;
binding.MaxBufferPoolSize = 2147483647;
binding.MaxReceivedMessageSize = 2147483647;

// Replace <machinename> with the name of the development server.
EndpointAddress remoteAddress = new EndpointAddress(“<service Link>”);

ReportPortClient client = new ReportPortClient(binding, remoteAddress);
client.ClientCredentials.UserName.UserName = “USER NAME”;
client.ClientCredentials.UserName.Password = “Password”;

BindingElementCollection elements = client.Endpoint.Binding.CreateBindingElements();
elements.Find<SecurityBindingElement>().IncludeTimestamp = false;
client.Endpoint.Binding = new CustomBinding(elements);

// THis portion is specific to my call of web service

Execute_ReportType request = new Execute_ReportType();
response = client.Execute_Report(request);

For NETTCPBINDING and defining the bindings from code you could have a look at:

http://www.bibits.co/post/2012/08/15/Consuming-Web-Services-in-SSIS-Script-Component.aspx

Reach out to me at my Email if you some clarifications and require assistance in consuming a Webservice via the Script Component task,

 

PS: Ensure for the SSIS Script component Source to work properly the SSIS package executing account must have read/ write permissions on the TEMP(generally- C:\Windows\Temp) directory, on the box the package would execute