Tag: SSIS

I need to set the Folder in Foreach loop to a variable location. I tried just adding the location to my package configuration, and it worked.

To make Folder dynamic so that it can get values from variable, Create a variable;

Click on Expressions under Foreach Loop Editor

This setting will take precedence to the folder value in Folder text box. Optionally you can set the FileSpec to a user variable pointing to the files (which will take precedence to the Files in Files text box.

Recently I have to move varbinary(max) data from one database to another database using script component.

When dealing with varbinary(max), there are two scenarios:

• the length of the data is moderate
• the length of the data is big

GetBytes() is intended for the this scenario, when we are using CommandBehaviour.SequentialAccess to ensure that we are streaming the data, not buffering it. In particular, in this usage we would usually be writing (for example) in a stream, in a loop. For example:

// moderately sized buffer; 8040 is a SQL Server page, note
byte[] buffer = new byte[8040]; 
long offset = 0;
int read;
while((read = reader.GetBytes(col, offset, buffer, 0, buffer.Length)) > 0) {
    offset += read;
    destination.Write(buffer, 0, read); // push downstream
}

However! If we are using moderately sized data, then use this code:

byte[] data = (byte[])reader[col];

Obviously the output column data type would be “image [DT_IMAGE]” in script component.

Finally to make it part of output;

OutputBuffer.FileData.AddBlobData((byte[])sqlReader["FileData"]);

Sources

https://stackoverflow.com/questions/11135245/what-length-parameter-should-i-pass-to-sqldatareader-getbytes

When an excel data source is used in SSIS, the data types of each individual column are derived from the data in the columns. How do we override this behaviour?

Ideally we would like every column delivered from the excel source to be string data type, so that data validation can be performed on the data received from the source in a later step in the data flow.

Just go into the output column list on the Excel source and set the type for each of the columns. Let excel do its guessing game. We are interested in output column and type. This works.

To get to the input columns list right click on the Excel source, select ‘Show Advanced Editor’, click the tab labeled ‘Input and Output Properties’.

A potentially better solution is to use the derived column component where you can actually build “new” columns for each column in Excel. This has the benefits of

1. You have more control over what you convert to.
2. You can put in rules that control the change (i.e. if null give me an empty string, but if there is data then give me the data as a string)
3. Your data source is not tied directly to the rest of the process (i.e. you can change the source and the only place you will need to do work is in the derived column)

This is another work around but it does not work at run time. You can see the data at design time though;

Write your SQL command and convert columns to text.

You can verify this on Advance tab / Input and Output Properties tab of Excel source. All of converted columns under External Column would be changed to “Unicode text stream” data type

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