Nov 24, 2010

SSIS - Script Component, Split single row to multiple rows - Sudeep's Domain

We have a text file and we need to read each row and the output would have multiple rows per row in the input file.

Input File:


Expected output:

2010-01-01 01
2010-01-03 03
2010-02-01 01
2010-02-02 02
2010-02-03 03
2010-02-04 04

Nov 23, 2010

SSIS - Expression to get File Name from File Path - Sudeep's Domain

Scenario: How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.

Typical example: You have a foreach loop set to traverse sub folders and you set the retrieve file name to "fully qualified" and in parameter mapping you map this to a string variable strFilePath. You use this variable to set up the connection string for may be a flat file source(will not be discussing that). Now lets say you need to retrieve the file path for logging purpose or sending a mail. How do we do this??

Oct 3, 2010

SSIS - Script Component as Source ADO.NET - Sudeep's Domain

How do we use Script Component as the source making use of ADO.NET connection that has been added to the package.

Sep 29, 2010

SSIS–Dynamically set File Mask : FileSpec - Sudeep's Domain

SSIS–Dynamically set File Mask : FileSpec - Sudeep's Domain

In the SSIS Forum I cams across the following query and thought of writing a post on the same.

“Can anyone give guidance on how to set the mask for a Foreach File Enumerator programmatically? I have a DB that has a list of masks. I am retrieving that into an ADO recordset. For each record I retrieve the value into a variable. I would like to look at a directory and copy all files that begin with the chars for each item in my recordset variable.”

The solution I provide uses a Flat File to store the various file mask in stead of a Table as required in the above case.


The above scenario could be broken into 3 parts:

  • Read the flat file and save the values to a object type variable.
  • Loop through each row of the object variable(which contains the file masks).
  • For each of the mask traverse the folder and copy/move(or any other operation you need) the files that match the current file mask.

Sep 9, 2010

Loop through each record in a text file : Recordset Destination

In most ETL we use Foreach loop to iterate through each file in a folder or records fetched from a table. Now the question arises how do we use Foreach loop based on each record in a text file.

Scenario: We have a text file having 2 columns, one containing source file relative path and 2nd column having the destination file relative path. The objective is to fetch each file from the source folder that are there in the our text file and move them to destination based on the connections available in the text file on the same record.

File Layout:


The overall package:


The first Data Flow Task(DFT) uses a Flat File Source to read the data and send it to an object type user variable using Recordset Destination.


Sep 2, 2010

Script Component–Source Part2 Retain last record

So keeping my focus on SSIS Script Component as Source I show another example. Mostly Script component is needed when source is Flat file and we need to retain the previous record value(s) to be used in the next record(s). Keeping this in mind I present the scenario:

The source file layout

Requirement: Add a third column to data and the values need to be set base on the existing 2 columns. If Column 2 value 1 set column 3 as Column 1, else set Column 3 value to the Column 1 value of the previous record.

Expected Output:

Col1 Col2 Col3
1 1 1
2 1 2
3 0 2
4 1 4
5 0 4
6 0 4
7 1 7



Aug 30, 2010

To use Script Component or Derived Column Transformation?? Is the question..

This is an old debate and yet not resolved. To use Script Component or Derived Column Transformation??
Yes Derived Column Transformation can NOT do all that can be done in Script Component.
Advantages of Script Component:

Read more..

Script Component–Source Part1

One of the most advance feature of SSIS is Script design capability provided be it Script Task in Control Flow or Script Component in Data Flow Task. I have seen people use the Script task pretty often but avoid Script Component because of various reasons.  Earlier I had shown how to set up user variable in Script component. Today I will demonstrate how to use Script Component as a Source for the below flat file:


Notice that the Column values are coming in new rows and the column header is present in all the all the rows.

While the output should be in the following format:
Name     Age        Sex
Rohit      28          Male
Mohit      20          Male
Ram       28          Male
Shyam   20          Male


SSIS - Managing Control Flow based on the input file name

Many times during ETL we have a requirement where based on the file name we have to perform certain operations. Normally within a For Each Loop with File enumerator in Control Flow Task there is no direct way of doing this. If the file mask matches, well and good but nothing beyond that. I demonstrate this with a scenario.... read more

Aug 16, 2010

Less known/used property: ExecValueVariable

At times I have seen in SSIS forums people ask about the way they can know the number of records updated by a query or deleted by a query in an Execute SQL Task in control flow. Even I replied at times that its not possible(in a direct manner), until I read about the property ExecValueVariable.

Read more here.

May 6, 2010


In SSIS Data Flow Task (DFT) is the most used task as almost all the ETL is done in DFTs. Situation do arise when we need to use data between two or more DFTs. So how can this be achieved? Well there are a number of ways you can save you data in one DFT to so that you access the same data in another DFT. Below are the different ways to store the data in the intermediate process, to be used again in another DFT or even another package.
  • Raw File
  • Flat File
  • SQL Table
  • Excel File
  • Recorset destination with Script component as the source
For details check this out: DFT 2 DFT

Apr 7, 2010

SSIS - Delete files in a folder older than a specified number of days

Scenario: How to delete files created a number of days before today. The files will have the date appended to the file name and we will not check the file properties to decide the file age. The file name would follow the naming convention: File_ddmmyyyy.txt

This can be achieved very easily in SSIS. Create an integer variable to store the value stating how old files do you wish to delete. Suppose you wish to delete all files older than 3 days, the variable would have the value 3. I name this variable intFileMaxAge. Make this variable configurable so that it can be changed as and when required.

Next I show the layout of the package.

Control Flow

                                                       Fig. 1

The Foreach Loop Container is configured to pick the files from a particular folder(you should make this connection configurable) and the “Retrieve file name” property is set to “Name and extension”. Refer fig. 2

ForEach loop

                                                       Fig. 2

Next we need to save the file name for each iteration to a string variable strFileName. For this go to the Variable Mappings tab and select the variable strFileName from the drop down box and set the index to zero. Refer fig.  3


ForEach loop_VarMapping

                                                       Fig. 3

Once the above is done put a script task in the Foreach Loop Container. Do not make any changes to the script task. It is just there so that we can have Precedence Constraint before the File system task to control the flow. We need the File System Task to be triggered only when the condition is met i.e. files created before certain days (Refer fig. 1). Double click the precedence  and set the Evaluation operation to Expression. In the Expression put the below expression:

DATEDIFF("dd",(DT_Date)(SUBSTRING(@[User::strFileName],6,2)+"-"+SUBSTRING(@[User::strFileName],8,2)+"-"+SUBSTRING(@[User::strFileName],10,4)),GETDATE()) > @[User::intFileMaxAge]

The above expression extracts the date part from the file name and converts it to date type. Once that’s done it gets the difference of the between the file date and today’s date. After this the expression checks whether the difference is greater than the value specified in the variable intFileMaxAge.

If the above expression will be evaluated to true the control will go to the File Sytem Task.

Setting up the File System Task. (refer fig. 4) The operation is set to Delete file  as we need to delete files. Set the IsSourcePathVariable to False as I create a connection to connect to the file and use expression to modify the connection at run time.

FileSystem Task

                                                       Fig. 4

In the Connection Managers select the connection manager configured above(refer fig. 4) go to its properties and in the expression set connection string to the following expression:

@[User::strFolderPath]+ @[User::strFileName]

Where the variable strFolderPath contains the folder in which the files are placed. Again make this configurable.

That completes the package.

Mar 10, 2010

Using DB Mail in SSIS

In the SSIS Forum I often find people asking if it’s possible to send mail in HTML format or Fetch data from Table and use it in the mail body. In the Send Mail Task we can only send the mails in text format or send the query result as an attachment.

Today I will explain the way this can be achieved.
If we have a table with columns as ID, CustName, Amount and we need to have the mail sent in the following format. The rows will be colored based on a particular condition which can be set in the query.

Mail Header


Source Table Script

CREATE TABLE [dbo].[tblCust](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [int] NULL

Insert Records

INSERT INTO [RAJ].[dbo].[tblCust]([CustName],[Amount])
SELECT 'Sudeep',100
SELECT 'Ankur',-10
SELECT 'Manju',3 )
First we set up the Database Mail Profile with the following query in our SQL server.

USE msdb
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

--Here I am setting up our Profile Name, Account Name, STMP server name, and the name that will display in the from field in the e-mail.

SET @ProfileName = 'Sudeep';
SET @AccountName = 'Sudeep';
SET @SMTPAddress = '';
SET @EmailAddress = '';
SET @DisplayUser = 'SQL Mail';

--The following section adds our Account, Profile, and Profile-Account association to the system.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

Next is the query that provides us our data in HTML format which will be used in the mail body.

SET @tableHTML =
N'<html><body><h1>Mail Header</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>ID</td><td>CustomerName</td><td>Amount</td></tr>' +
td = ID, '',
td = CustName, '',
'td/@bgcolor'=CASE WHEN Amount>0 THEN 'Green'
td = Amount, ''
FROM tblCust
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'EXEC msdb.dbo.sp_send_dbmail @recipients='<MailID@maildomain.Com>',
@subject = 'SQL Errors Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'SudeepTest';
In this query the HTML body is
formed in the variable @tableHTML, and the body format is set to ‘HTML’.
Depending on the number of records fetched in the query the HTML body that is created from the above query would looks like:

        Mail Header</h1>
    <table border="1" width="100%">
        <tr bgcolor="gray">
            <td bgcolor="Green">
            <td bgcolor="Red">
            <td bgcolor="Green">

If you need any kind of modification in your table look and feel you could think about the related HTML that you need and modify the above query accordingly.
Now in the SSIS Package that you have use the 2nd query in Execute SQL Task and check the mail that you receive.
It is not necessary that it can be used for tables. You can modify the select statement in the above query to return the data as HTML format without FROM clause or simply put your 
HTML code in the @tableHTML variable.

  • ·         The SMTP server should be installed and configured
  • ·         The user in the profile should have access to the SMTP Server
  • ·         Check the SMTP server for the domains to which it can broadcast mails.