Posts Tagged ‘and script components’

Script Component in SSIS

October 12, 2011 1 comment

In the following link, the script component is described with some examples.

The above link notes the two types of transformation in the script component:

Synchronous Transformation

The output is synchronized with the input and the input data will be processed on a row by row basis.

Asynchronous Transformation

The output is not synchronized with the input. All the input data will be fetched initially, then all the rows will be read and followed by the output generation.

An example of using asynchronous Script transformation:

Cleaning Address Data with SSIS Using a Web Service

In the above link, we have the following:
To call a web service from within an SSIS script component you have two options:

  1. Create a proxy class, in any .NET language, using the command prompt utility wsdl.exe and compile this into a strong-named assembly which is then registered in the GAC.
  2. Alternatively create a Visual Basic proxy class using wsdl.exe and import the new class directly into your VSA project.

In order to implement the first option, we need to register the assembly in GAC and also sign it with a strong name and it may not be the preferred way. The need for a strong name pair key and GAC registry is emphasised in the following link.

In general, to generate a proxy class, locate the wsdl.exe file in the system and in the visual studio command prompt type the following:

C:\Program Files\Microsoft Visual Studio 10.0\VC>
wsdl /l:vb /out:proxyfilename.vb http://serviceaddress/service.svc

The difference between synchronous and asynchronous script components

The following links explains how to make a script component asynchronous and also the differences between the two.

Let me take this opportunity to paste some more general info about

Asynchronous Method Overloads

There are potentially two overloads for the asynchronous operations: single-invocation and multiple-invocation. You can distinguish these two forms by their method signatures: the multiple-invocation form has an extra parameter called userState. This form makes it possible for your code to call Method1Async(string param, object userState) multiple times without waiting for any pending asynchronous operations to finish. If, on the other hand, you try to call Method1Async(string param) before a previous invocation has completed, the method raises an InvalidOperationException.

The userState parameter for the multiple-invocation overloads allows you to distinguish among asynchronous operations. You provide a unique value (for example, a GUID or hash code) for each call to Method1Async(string param, object userState), and when each operation is completed, your event handler can determine which instance of the operation raised the completion event.

see under the heading Asynchronous Method Overloads in the link:

Problems with async version:

Async script components work well as long as the pipeline is not closed.

So it fails when the package debugging is stopped after successful execution or when run through a sql server agent job.

Sometimes you have some pretty complex ETL’s going in SSIS, and you might have multiple projects/solutions that need to call other SSIS Packages or SQL Agent Jobs and you have a pretty big production going on. You might have an ETL solution that needs to kick off other packages, and you can either import those into your solution or call them where they lie on the file system/SQL server, etc. You might have to call some SQL agent jobs, and most often they are async calls (you dont need to wait for them to come back) and this works nicely, I do this all the time. The Execute SQL Agent Task in SSIS works nice, or you can just call the SQL statement to execute a job, either way, it kicks off the job and then just comes back successful right away, and doesn’t care if the job actually succeeds.