Category: Data Design

Sometime its kind a hard to remember different SSIS syntax and how to use them. I build up a list to help me out. Expressions used are from AdventureWorks sample provided with Microsoft SQL Server.

Converting String to Guid in Derived column expression;

(DT_GUID)("{" + [ColumnName] + "}")

If using dynamic Sql in script component or variables, get Guid from database as String;

CAST([GuidColumn] AS NVARCHAR(60)) AS GuidColumn

Database will convert string to Guid on query submission.

Boolean expression

If incoming data type is text then use this for Boolean conversion;

(DT_BOOL)((DT_WSTR,1)Rejected == “1” ? TRUE : FALSE )

This will also handle null values in incoming data.

How to Get file name and file extension in SSIS Expression?

Suppose this is the file name;

@FileName = 6be8bf19-b715-ec11-b1cb-000d3adde0a7.xlsx

This is how we will get extension and file name;

--get file extensions
REVERSE(left(REVERSE(@[User::FileName]), FINDSTRING(REVERSE(@[User::FileName]) , "." , 1 ) - 1))

Result
------
xlsx

------get file name
SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName] , "." , 1 ) - 1)

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

--same result can be achieved by using this statement

REVERSE(LEFT(@[User::FileName], FINDSTRING(@[User::FileName] , "." , 1 ) - 1))

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

How to get only Date from DateTime variable?

SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)

Parameterizing OLEDB as source in Data Flow is easier. It’s not that straight using ADO.NET.

In SSIS you can’t parametrize ADO.NET source. You have to use a workaround.

Luckily, there are few workarounds. One would be creating Script Component that acts like source and code it. However, one can’t always easily convert the existing resource into script, especially when he lacks ADO.NET programming knowledge.

There is another workaround, and that would be creating the SQL Query before the ADO.NET Source takes action. However, when you open ADO.NET source, you will notice that Data access mode doesn’t allow variable input. So, how do you proceed?

You want to dynamically set the SQL expression of the ADO.NET source, so you have to tell your data flow task to configure the SSIS ADO.NET source component by using Expression.

To make the long story short (or not-quite-so-short :), do this:

• in your package, enter your data flow task with source/destination components
• click anywhere on the background, to have Task properties shown in Property panel
• in Property panel find Expressions property, that can configure various data source/destination properties, and open it using ellipsis button (…)
• under Property, select SQL Command property of your source (e.g. [ADO.NET source].[SqlCommand]) to add one row
• click ellipsis button for the row to open Expression Builder
• build your dynamic query in the Expression Builder

The last step could be somewhat cumbersome for date/datetime parameter. However, here’s the example, for your convenience:

"SELECT * FROM YOUR_SOURCE_TABLE WHERE your_date_column = '" + 
  (DT_WSTR,4)YEAR(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)MONTH(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)DAY(@[User::VAR_CONTAINING_DATE]) + "'"

Here is a reference for using OLEDB as source;

Using Variables in SSIS: OLE DB Source Parameters

I would like to get data out of Object variable in Script task or Script component. My object variable name is VersionList;

If we have used OLE DB to load data into Object variable, we need to use this;

// Set up the DataAdapter to extract the data, and the DataTable object to capture those results. This will write the contents of the Recordset to a new DataTable object.
// Create the connector
OleDbDataAdapter da = new OleDbDataAdapter();       

// Create the output DataTable
DataTable dt = new DataTable();                     

// Load the DataTable
da.Fill(dt, Dts.Variables["VersionList"].Value);       

// Now, replace the value in the VersionList variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset

Dts.Variables["VersionList"].Value = dt;

If we have used ADO.NET to load data into Object, then we need to use this;

 try
            {
                var ds = Dts.Variables["User::VersionList"].Value as DataSet;
                DataTable dt = new DataTable();                     
                // Create the output DataTable
                dt.TableName = "VersionList";
                // Now, replace the value in the VersionList object variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset
                dt = ds.Tables[0];
                Dts.Variables["User::VersionList"].Value = dt;

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex)
            {
                var message = $"Error comparing version numbers. ERR: {ex.Message.ToString()}";
                //MessageBox.Show(message);
                Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

We can use it in script component like this;

//get version and rules from VersionList variable
DataTable dt = (DataTable)Variables.VersionList;
//iterate over rows
foreach (DataRow row in dt.Rows)
{
   string message = $@"Details: {row["VersionNumber"].ToString()} - {row["VersionCode"].ToString()}
                           - {row["TabName"].ToString()} - {row["CellRangeCode"].ToString()} 
                            - {row["CellRange"].ToString()}";
   MessageBox.Show(message);
}

If for some reasons we are using Foreach loop and keep referring to same record set, then best design is to keep it ouside Foreach loop. An example is this;

We are getting VersionList from (1). We convert it to a data table in (2). We use it inside data flow task (3) of Foreach loop container.

Open a script task or script component. Press F4 to open properties windows. You will find target .NET framework;