My First SSIS Project

I just finished my first SSIS (SQL Server Integration Services) project. It was a simple project, but covered most of the basic stuff in SSIS. 

Tasks

Here are the tasks I have used in this project.

Foreach Loop Container

The first thing in this project is looping through a collection of zipped files and processes them. So it started with a Foreach Loop Container. You need to specify the enumerator and its configuration. Because I need to pass the file name to the processes in this container, I mapped the file name to a variable.

Execute Process Task

I have used “Execute Process Task” to unzip a file before loading the data from it to the database. It was very straight forward. There are three things need to be specified: The Executable, Arguments and WorkingDirectory. I was using WinZip, so the executable was the WinZip32.exe in its installed directory. Because the file is encrypted using a password. So the arguments looked like this “-e -o -s’password’ filename”. I used expression and variable for the arguments because I don’t want to hardcode the password.

 

-e: extract
-o: overwrite existing file
-s: password

Script Task

I used Script Task to call our existing logic in a .NET DLL to generate the primary key for one of the table I was loading. To be able to reference the .NET DLLs, I have to not only add the DLLs in GAC, but also copy them to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (We are using .NET 2.0)

I also used two variables to pass and get data from the script. The read only one is for setting the configuration file for the .NET DLL. The other read/write variable is for getting the primary key the script has generated.

Below are the screen shot of the Script Task Editor and the script itself.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain

    Public Sub Main()

        Dim totalDetailRowCount As Integer
        Dim totalInvoiceCount As Integer

        totalDetailRowCount = Convert.ToInt32(Dts.Variables.Item("TotalDetailRowCount").Value)
        totalInvoiceCount = Convert.ToInt32(Dts.Variables.Item("TotalInvoiceCount").Value)

        If (totalDetailRowCount <> totalInvoiceCount) Then
            Dts.TaskResult = Dts.Results.Failure
        Else
            Dts.TaskResult = Dts.Results.Success
        End If

    End Sub

End Class

This is how you will set a variable:

Dts.Variables.Item("SomeIdentifier").Value = idReturned

This is how you will convert an object to string:

CType(Lease.Configuration.ConfigurationSettings.AppSettings("SSISBranchId"), String)

Data Flow Task

The Data Flow Task is the primary task in this project. It contains a flat file source, several OLE DB destinations. I was using SQL Server destination originally when I test it on my local machine. Then I found out that the SQL Server destination connects to a local SQL Server database only and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination. I also used some data transformations including Derived Column, Data Conversion, Conditional Split and Script Component. All of them are pretty easy to use. The only thing worth mention is the script for the Script Component. The AudStampOut field was specified in the “Inputs and Outputs” in Script Component configuration.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Dim auditStamp As Decimal
    Dim configFileUrl As String = String.Empty
    Dim idMgr As OurNameSpace.Data.IdentityManager

    'Get config file from SSIS Variable and set the path for the configuration manager
    OurNameSpace.Configuration.ConfigurationSettings.ConfigFileUrl = CType(Variables.ConfigFileURL, String)

    SetUserBranchIdentity(idMgr)

    auditStamp = idMgr.GetDecimalID(String.Empty, "aud")

    Row.AudStampOut = auditStamp

End Sub

 

Execute SQL Task

After loading the data, I used a couple of Execute SQL Task to retrieve the total row count from database and validate it. The General tab of the Task Editor looked like below. It also had variables setup in Parameter Mapping and Result Set tabs.

File System Task

When we are done with the file, I want to move them to an archive folder. First I tried to use “Move file” operation. But I couldn’t get it to work if I use variable for the source. Even I set DelayValidation to true, it would still complain “couldn’t find the source file” or something like that. I might be doing something wrong there. But after spending a couple of hours on it, I gave up. Finally I used “Rename file” operation which actually did the moving. I set the destination variable to my archive folder plus file name. That did the trick.

Send Mail Task

There is really nothing special about Send Mail Task. You just create a SMTP Connection Manager, specify your mail server. In the task, you specify mail from, mail to and other properties. And you are done.

Error Handling

OnError event

There are several ways to handle errors in SSIS. In this project, the data flow task was the most important one. So I added an event handler for it. Also because we don’t want to send out tons of emails, so I added a For Loop to make sure we only send out one error email for each run.

Transaction

I wanted the data flow task run in a transaction, so when one of the components fails, it would roll back everything. The default setting for the task is “Supported” which means the task will enlist in an existing transaction. Since we don’t have an existing transaction, I need to change it to “Required” which will create a transaction if none exists and enlist in an existing one.

Deployment

Configuration File

Before deploy a SSIS package, you will generally need to create a configuration file. You can then store your database connection, file location, email server connection and etc. This information should be configurable.

Here are the steps to create a configuration file:

1. Right click on any blank space in the control flow tab
   
   



2. Choose “Package Configurations…” from the pop-up menu.
   
   



3. Check the “Enable package configuration” checkbox
   
   



4. Click on “Add..” button and the configuration wizard will show up.
   
   



5. Follow the steps in the wizard to choose configuration type, file name and variable or properties you want to put in the configuration.

Note: after I had it set up, I occasionally run into an issue with opening the configuration editor. It would give me a message saying could not load the configuration file or something. If I just close the package and re-open it, everything will be fine. It is kind of strange.

The ProtectionLevel property under “Security” is default to “EncryptSensitiveWithUserKey”. Since we want to have the control of which user will be using to run this package, we have to choose “DontSaveSensitive”. Then when we deploy the package, we will manually enter the sensitive data, such as password for database access.

To build the deployment package, you need to open the properties window by right clicking the project and choose the "Properties" from the popup menu

Click on "Deployment Utility" and change the "CreateDeploymentUtility" to "True". Set the DeploymentOutputPath if you want.

The build process will generate the package file *.dtsx, the configuration file *.dtsConfig(if you choose to use xml configuration file) and the deployment manifest file *.SSISDeploymentManifest under \bin\Deployment\ folder. You can double click on the manifest file and a package installation wizard will help you to deploy the SSIS package. You will have a chance to change the configuration in the wizard.

Now my first SSIS package is ready to run. To trigger it, I used the following command.

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec" /file "SSISyoupackage.dtsx"