SSIS Script Component throws "Object reference not set to an instance of an object"

In Integration Services 2005, if you get an Object reference not set to an instance of an object exception when trying to manipulate rows from a Script Component, even after you have guaranteed that the column is set as an input for the script, try checking if the column you're accessing is not null, using the automatically available "_IsNull" property. For example, if the input column is called myInputCol, you can try something like this:

If Not Row.myInputCol_IsNull Then
'code that access the column here
End If

Access and consume a Web Service from Integration Services

Sometimes you need to consume a Web Service that is specific enough for you not be able to use the default Web Service Task available on SSIS 2005.

In this example I'm going to show how to consume a Web Service that returns a .NET object (a serialized DataSet, for example). For this, we'll have to use a Script Component that allows us to insert some custom code that is able to do what we need. The Script Component will be part of a Data Flow acting as a Source that provides rows to Transformations down the pipeline.

Acessing a Web Service from a Script Component is pretty much similar to how we'd do it in a standard .NET application:

1. You need a proxy class (in the VB language) for the Web Service you want to consume. You can create it using the wsdl.exe application like this:
   wsdl /language:VB http://yourserver/yourWebService?WSDL /out:yourProxyClass.vb
2. Access the Script Component's code and, on the code window, go to the Project menu and select "Add Existing Item". The item you have to add is the proxy class you created previously (yourProxyClass.vb, in this example). Next, go to the Project menu, select "Add Reference" and add the System.Xml and System.Web.Services assemblies.
3. Now you have to write the code to acess the Web Service and output its results onto the outputs you defined for the Script Component.
   In this example, the Web Service defines a class called Export which has a method called getDataSet(String) that outputs is a serialized DataSet. The Script Component has one output, called Output0, with two columns (colOne and colTwo) defined as a String (DT_STR)

Public Class ScriptMain
Inherits UserComponent


Dim webServiceExport As Export
Dim dsResults As DataSet

'Prepare the required connections and initialize objects
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
MyBase.AcquireConnections(Transaction)

webServiceExport = New Export()
dsResults = Nothing

End Sub


'Get the data from the WebService
Public Overrides Sub PreExecute()
MyBase.PreExecute()

webServiceExport.Timeout = 3600
dsResults = webServiceExport.getDataSet("test parameter string")

End Sub


'Go through the received data and add it to the output rows
Public Overrides Sub CreateNewOutputRows()

Dim dtResults As New DataTable

dtResults = dsResults.Tables(0)

Output0Buffer.colOne = dtResults.Columns(0).ToString()
Output0Buffer.colTwo = dtResults.Columns(1).ToString()

Output0Buffer.AddRow()

End Sub

End Class

If you need some Web Services for testing you can search at http://www.webservicex.net/WS/default.aspx

Detailed information links:
Wsdl.exe: http://msdn2.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx
Referencing Assemblies in a script: http://msdn2.microsoft.com/en-us/library/ms136007.aspx
Using the Script Component as a source: http://msdn2.microsoft.com/en-us/library/ms136060.aspx

Convert Access' Memo to a VARCHAR string

The problem: the Memo data type in Access is a Unicode text stream (i.e. no fixed width) and the VARCHAR type is a non Unicode text string (fixed width). So, if you're transferring data from a Memo column on an Access file to a database column of the VARCHAR(n) type, you'll have to use a Data Conversion transformation to convert the Memo to String (DT_STR, which maps to VARCHAR) and when you do, Integration Services will throw the following error

Conversion from DT_NTEXT to DT_STR is not supported

This means that you cannot directly convert a Unicode stream to a non Unicode string.
What you can do in this situation is create a Data Conversion that converts the Memo column to DT_TEXT (non Unicode stream) and after that use another Data Conversion to convert that same column from DT_TEXT to DT_STR. Keep in mind that truncation may occur if the conversion to DT_STR doesn't reserve enough width on the column to fit the whole stream contents (i.e. if the stream has at most 500 characters you'll need to set up the Length of the column to 500 in the Data Conversion transformation)
Another option could be using a Derived Column to perform both casts in a single transformation (haven't tried this solution).

Custom logging with Integration Services

Integration Services 2005 allows you to insert custom log entries from a Script Task by sending events of a specific type to your log provider. You can use something like this from within a script:

Dts.Events.FireInformation(0, "CustomInfoHere", "This is a custom log entry thrown by a script task")

You can also use this technique to raise errors, warnings, progress information, etc...

If you need help on how to set up logging for a package see this: http://msdn2.microsoft.com/en-us/library/ms141727.aspx

Execute SQL Task (SSIS) with parameters

On Integration Services 2005, when using a query with parameters in the Execute SQL Task, mind that in the Parameter Name field the counting starts at 0 (if, at least, the connection type is OLE DB). Therefore, if you are using multiple parameters the first Parameter Name must be 0, the second must be 1, and so on.
Otherwise, you'll get this error:
[Execute SQL Task] Error: Executing the query "DELETE FROM myTable WHERE key = ?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Check here for more information: http://msdn2.microsoft.com/en-us/library/ms187685.aspx

Changing system culture programatically in C# .NET

This is useful when handling dates in US or other format in C# .NET:

Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US", False)
Row.date = MonthName(Row.datahorainicio.Month(), True) & " " & Row.datahorainicio.Day() & " " & Row.datahorainicio.Year()

'reset the culture to the system's default
Thread.CurrentThread.CurrentCulture = New CultureInfo("pt-PT", False)

Multiple-step OLE DB operation generated errors

When running an Execute SQL Task on SSIS 2005 you get the following error:

Error: Executing the query "your SQL code here" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The last time I ran into this error it turned out that I had (yes, it was copy/paste ;D) some comments in the middle of the T-SQL code - deleting the comments got it working, hope this method helps you.