Loading images and text files in to database using Import Column Transformation

Import Column Transformation is used to add Text, image and xml content from files to a data flow stream. It allows users to read content available in flat files and loads it in to a column like Image, Text, NText and XML. Lets see an example on how to load content of images into to a database table using this component.

1. First search your computer and locate few images and copy them in to folder. I assume that you are copying files a.jpg, b.jpg, c.jpg and d.jpg in to folder ‘C:\InputImages’.

2. Then prepare a sample input file with the following content

ImageId, ImageFilePath

a.jpg, C:\InputImages\a.jpb
b.jpg, C:\InputImages\b.jpb
c.jpg, C:\InputImages\c.jpb
d.jpg, C:\InputImages\d.jpb

3. Drag and drop a Flat File Source in to a data flow task.

4. Configure the flat file source so that it points file which contains the above content(step 2).

5. Then drag and drop a Import Column Transformation in to the data flow and attach output of flat file source to Import Column transformation.

6. Now we have to configure the Import Column transformation. Double click on Import Column Component; it opens a dialog similar to the one below. Switch to InputColumns tab and select the column ImagePath as input column. We have to select a column which holds path of the images as input column, so that the Import Column transformation can read the files content and load it in to memory.

7. After configuring the input column, then switch to ‘Input and Output Properties’ tab to add an output column and link the output column to input column.

8. Browse and select the node ‘Import Column Output->Output Columns ‘ and click the Add Column button. Name the newly added column as ImageContent.

9. After adding the output column, remember value of ID property(in the above image it is shown as 49) and enter the value in FileDataColumnId property of input column(show in the image below)

10. After configuring Import Column Transformation, then Drag and drop an OLE DB Destination and attach output of Import Column transformation to OLE DB Destination

11. Make sure that your OLE DB Destination is going to point to a table which can hold ImageId and ImageContent and map the columns

12. That’s all your package is ready to import data available in flat files in to database tables

Adding description of error codes to an error output

When an error output of a SSIS component (ex: Lookup, Derived column, etc) is directed to another downstream components, SSIS automatically adds ErrorCode and ErrorColumn columns. The ErrorCode column of every row holds an integer values which represents an error occurred in the associated component. If you want to generate a report of failed records and send it to your colleagues, error codes may not make any sense in your report. It is always a good idea to give description which says what is the problem associated with every row. In order to get description of the error codes, we can use the method Me.ComponentMetaData.GetErrorDescription()
in a script component and append the description of error to every bad row. The
following piece of code in a script component can be used to get description of
every error and assign it to a input row column ErrorDescription.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ErrorDescription = _
Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
End Sub


Note: It is assumed that a Script component is attached to an error output with an output column ErrorDescription.

The following image shows a sample ssis data flow tasks

Update values of a variable in a Script Task or Script Component

Recently I tried updating value of a variable inside a Script Task, but application was hanging. There was no response from the system when ever the script task tried to update value of the variable.The code which I was using to update the variable was

Public Sub Main() Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("nCounter", vars) vars("nCounter").Value = 1980 Dts.TaskResult = Dts.Results.Success End Sub

When the code is debugged using a break point, we noticed application is hanging while trying to execute the line Dts.VariableDispenser.LockOneForWrite("nCounter", vars). After analyzing some time we identified the problem as a deadlock caused by execution of the above statement. A deadlock is created by the above statement as we have mentioned the variable nCounter
name in ReadWriteVariables property of Script Task. When a variable name is mentioned in ReadWriteVariables property, SSIS automatically locks the variable for writing. So if you would like maintain locking of the variables in your script then don’t mention the variable name in ReadWriteVariables property.

For simplicity if you would make use of scripting task locking mechanism then use the following the code to update values of the variables

Public Sub Main() Dts.Variables("nCounter").Value = 100 Dts.TaskResult = Dts.Results.Success End Sub

SSIS Package persistance locations

SSIS Packages can be persisted in three types of locations. They are File System, SSIS Package Store and SQL Server. The most common and widely used storage location is File System and saving package to a file system is pretty simple and straight forward. When ever a package is created using either BIDS or VS.NET environments, the package with the is persisted in to the location where the profile file is created.The default extension given for the package is .dtsx(DTS Extension) and content in the file are are well structured XML. The second storage location is SSIS Package Store which is similar to File System storage. When a package is stored in a SSIS Package Store then it is stored as a file in SQL Server's folder Program FilesMicrosoft SQL Server90DTSPackages. The third location to store the package is SQL Server database msdb. When a package is stored in SQL Server database, then content of the package is persisted as rows in to tables sysdtspackages90, ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog.

	File System	SSIS Package Store	SQL Server Database
Encryption Support	Yes	Yes	Yes
Backup Support	File backup	File backup	Database backup
Execution controlling	SSIS execution utilities	SSIS execution utilities	SSIS execution utilities and database jobs
Access controlling with possible	Not possible	Not possible	Supported with server roles

Error while connecting to remote SSIS Server

When a user tries to connect to a Integration Service running on a remote system, he may get an error message “Connect to SSIS Service on machine "" failed: Access is denied.”. The main reason for this problem is the current windows users of the client machine which you are using to connect to the remote SSIS Server is not part of Distributed COM Users group. To solve the problem, add the logged in user of the client to Distributed COM Users group in the database server system. Steps to follow

1. Log in to database server
2. Run ‘Compmgmt.msc’
3. Expand ‘Computer Management\System Tools\Local Users and Groups\Groups’
4. Select Distributed COM Users which is show in the right hand side panel and view properties by right clicking
5. In the properties window, use the ‘Add..’ button to add the client user as part of the group and close the dialog box

After adding the client user as part of servers Distributed COM Users group, you will be able to log in to SSIS Server remotely. If you still have problem in logging in refer to the article http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

SSIS Event Handlers

One of the reason to call SSIS as an enterprise class ETL tool is becuase SSIS supports Event Handlers. Event Handlers of SSIS are very much similar to event handlers provided in any modern languages like C# and Java. With the help of event handlers in SSIS, we can develop packages which can take necessary actions on specific events. Event handlers allow package to react to specific states of a package execution. Say for example, we can log information about the environment and its state when a package fails . We can send an e-mail on every successful execution of a task .

To know more about event handlers read the articles SQL Server 2005 - SQL Server Integration Services - Part 11 - SSIS Events and Event Handlers, Custom Logging Using Event Handlers

SSIS Expression Language Reference

SQL Server 2005 Integration services also called as SSIS introduced a new language to define expression. It is quite astonishing to see all new language defined for writing the expressions instead of using existing Microsoft languages like C#, VB.NET. Anyway we have to master this new language to learn write expressions in SSIS and here is the reference guide available in MSDN http://msdn2.microsoft.com/en-us/library/ms141232.aspx

Memory leak in Execute() method of SSIS Package class

Microsoft has confirmed that Execute() method of Microsoft.SqlServer.Dts.RunTime.Package class has memory leak and it is expected to be fixed in SP2 of SQL Server 2005. For more information have a look at the following link MSDN SSIS forum thread

Self Modifying Packages in SSIS?

"Self Modifying Packages in SSIS?
Yeah, thought that might get your attention. :)
First, packages cannot modify themselves during execution. There is no package pointer passed to the tasks any longer, so you can't traverse the package object model with the script task any longer. That is, you can't traverse the package object model for package in which the script task resides. You CAN however open and modify other packages, including those that the parent package is about to execute with the Execute Package task. This is the same model as self modifying packages in DTS, except it's safer because you're not attempting to change the package as it is running.
Here's the script from a chapter of my book that shows you how to modify a Transfer Objects Task to move some tables. There is no error handling code for clarity, bla bla bla. The usual caveats apply, check for errors, handle exceptions.
The Script
Imports System.Collections.Specialized
Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections('Tables').AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
Dim th As TaskHost
th = package.Executables('TablesToMove')
Dim sc As StringCollection = New StringCollection()
sc.Add(Dts.Variables('Tables1').Value.ToString())
sc.Add(Dts.Variables('Tables2').Value.ToString())
th.Properties('TablesList').SetValue(th, sc)
application.SaveToXml(packagename, package, Nothing)
Dts.TaskResult = Dts.Results.Success
End Sub
This is some quick and dirty code that uses the package objec"
By Kirk Haselden