Sudeep's Domain

SSIS - Script Task, when to use them? - Sudeep's Domain

noreply@blogger.com (Sudeep Raj) — Sun, 25 Sep 2011 11:03:38 PDT

SSIS - Script Task, when to use them? - Sudeep's Domain

Read on to know when you need to use Script Task or Script Component.

SSIS - Reading object variable in Script task - Sudeep's Domain

noreply@blogger.com (Sudeep Raj) — Thu, 09 Jun 2011 11:14:39 PDT

SSIS - Reading object variable in Script task - Sudeep's Domain

BI Quiz - Select distinct files from a list of files in a folder - Sudeep's Domain

noreply@blogger.com (Sudeep Raj) — Thu, 02 Jun 2011 22:27:10 PDT

Test your SSIS knowledge by solving this scenario. Gain a chance to win goodies and know SSIS..

Links for 2011-05-09 [Digg]

Tue, 10 May 2011 00:00:00 PDT

SSIS -€“ Process files in Sequence - Sudeep's Domain
How to process files in a sequence that are stored in a folder and stop in case a file is missing in the sequence.

SSIS – Process files in Sequence - Sudeep's Domain

noreply@blogger.com (Sudeep Raj) — Mon, 09 May 2011 17:32:30 PDT

I got a very interesting scenario in Ask Sudeep section of BeyondRelational.com. Let me share the scenario first and then will explain how to go about it.
Scenario:
There is a folder which contains a number of files. The file name follows the following naming convention . Alphanumeric could be any valid alphanumeric characters and the Number part would have just numeric values and both would be separated by a period [.]. Examples of file name file1Name.1, file1Name.2, file2Name.5 etc. Now the requirement says that for a given group of Alphanumeric part which are same we need to start processing the files from the file having the minimum numeric part and keep on processing the file till we get the numeric part incrementing by one for the files. Then move to the next set of files with another Alphanumeric part. Better to see an example, will give a clear picture.
List of files in the folder:

a.1
a.2
a.4
a.5
b.10
b.11
b.15
b.16

There can be 2 sets of files based on the alphanumeric part once with a and the other with b. From the above list we need to process just the following files:
a.1
a.2
b.10
b.11

As there is a break in the sequence for the file group “a” as a.3 is missing and similarly b.12 is missing so no files with larger numeric part will be processed.

Read on....

SSIS - Script Component, add missing column or ignore extra columns

noreply@blogger.com (Sudeep Raj) — Tue, 08 Mar 2011 13:15:06 PST

Today I got a query asking me if we can have a script component which reads data from a text file which is supposed to have 5 input columns. However the data that they receive has at time extra columns or less columns for certain records. So the question arises how do we go about this.
Input:

Col1,Col2,Col3,Col4,Col5

1,2,3,4,5

1,2,3,4,5,6,7,

1,2,3

Output:

Col4    Col3    Col2    Col1    Col

5   4   3   2   1

5   4   3   2   1

NULL    NULL    3   2   1

There are 3 ways that come to my mind straight away.

Script Component
Flat file source which reads all data into one column and then splits them based on index of the comma in a derived column
Flat file source with delimited settings to read just 5 columns.

READ ON

SSIS - Upload data to Oracle using SQL Loader - Sudeep's Domain

noreply@blogger.com (Sudeep Raj) — Tue, 22 Feb 2011 12:49:19 PST

SSIS - Upload data to Oracle using SQL Loader - Sudeep's Domain

Links for 2010-11-23 [Digg]

Wed, 24 Nov 2010 00:00:00 PST

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??

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

noreply@blogger.com (Sudeep Raj) — Tue, 23 Nov 2010 15:39:46 PST

Scenario:

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:

"201001","1;3"

"201002","1;2;3;4"

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

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

noreply@blogger.com (Sudeep Raj) — Mon, 22 Nov 2010 16:09:09 PST

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??

Links for 2010-10-02 [Digg]

Sun, 03 Oct 2010 00:00:00 PDT

SSIS - Script Component as Source ADO.NET - Sudeep's Domain
This blog talks about ETL scenarios and concepts of SSIS a part of MSBI using SQL Server 2005 and SQL Server 2008

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

noreply@blogger.com (Sudeep Raj) — Sat, 02 Oct 2010 13:55:41 PDT

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

Links for 2010-09-28 [Digg]

Wed, 29 Sep 2010 00:00:00 PDT

SSISâ€“Dynamically set File Mask : FileSpec - Sudeep's Domain
This blog talks about ETL scenarios and concepts of SSIS a part of MSBI using SQL Server 2005 and SQL Server 2008

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

noreply@blogger.com (Sudeep Raj) — Tue, 28 Sep 2010 12:46:48 PDT

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.

b*.txt
a*.txt
d*
*z.xlsx

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.

Links for 2010-09-08 [Digg]

Thu, 09 Sep 2010 00:00:00 PDT

Loop through each record in a text file : Recordset Destination - Sudeep's Domain
This blog talks about ETL scenarios and concepts of SSIS a part of MSBI using SQL Server 2005 and SQL Server 2008
Script Componentâ€“Source Part2 Retain previous record - Sudeep's Domain
This blog talks about ETL scenarios and concepts of SSIS a part of MSBI using SQL Server 2005 and SQL Server 2008
SSIS - Managing Control Flow based on the input file name - Sudeep's Domain
This blog talks about ETL scenarios and concepts of SSIS a part of MSBI using SQL Server 2005 and SQL Server 2008
Sudeep's Domain: 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.
Sudeep's Domain: 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 1,1 2,1 3,0 4,1 5,0 6,0 7,1 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.
Sudeep's Domain: 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.
Sudeep's Domain: 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: Name,Rohit Age,28 Sex,Male Name,Mohit Age,20 Sex,Male Name,Ram Age,28 Sex,Male Name,Shyam Age,20 Sex,Male 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
Sudeep's Domain: 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....
Sudeep's Domain: 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.
Sudeep's Domain: DFT 2 DFT
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

Loop through each record in a text file : Recordset Destination

noreply@blogger.com (Sudeep Raj) — Wed, 08 Sep 2010 12:14:45 PDT

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:

InputFilePath,OutputFilePath
\Input\Input1.txt,Output\Output1.txt
\Input\Input2.txt,Output\Output2.txt
\Input\Input3.txt,Output\Output3.txt

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.

Continue……

Script Component–Source Part2 Retain last record

noreply@blogger.com (Sudeep Raj) — Thu, 02 Sep 2010 06:04:58 PDT

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

1,1
2,1
3,0
4,1
5,0
6,0
7,1

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

Output:

READ MORE HERE...

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

noreply@blogger.com (Sudeep Raj) — Sun, 29 Aug 2010 23:26:40 PDT

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

noreply@blogger.com (Sudeep Raj) — Thu, 02 Sep 2010 06:03:45 PDT

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:

Name,Rohit
Age,28
Sex,Male
Name,Mohit
Age,20
Sex,Male
Name,Ram
Age,28
Sex,Male
Name,Shyam
Age,20
Sex,Male

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

READ ON…..

SSIS - Managing Control Flow based on the input file name

noreply@blogger.com (Sudeep Raj) — Sun, 29 Aug 2010 13:25:49 PDT

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

Less known/used property: ExecValueVariable

noreply@blogger.com (Sudeep Raj) — Sun, 15 Aug 2010 17:05:44 PDT

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.

One Div Zero: A Brief, Incomplete, and Mostly Wrong History of Programming Languages

noreply@blogger.com (Sudeep Raj) — Tue, 11 May 2010 14:53:59 PDT

Know the true HISTORY, one that really matters:

One Div Zero: A Brief, Incomplete, and Mostly Wrong History of Programming Languages

DFT 2 DFT

noreply@blogger.com (Sudeep Raj) — Thu, 06 May 2010 00:04:23 PDT

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

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

noreply@blogger.com (Sudeep Raj) — Tue, 06 Apr 2010 16:07:47 PDT

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.

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

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

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.

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.

Using DB Mail in SSIS

noreply@blogger.com (Sudeep Raj) — Sun, 04 Apr 2010 20:38:58 PDT

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

ID CustomerName Amount

1 Sudeep 100

2 Ankur -10

3 Manju 20

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
    ) ON [PRIMARY]

Insert Records

INSERT INTO [RAJ].[dbo].[tblCust]([CustName],[Amount])
    (
    SELECT 'Sudeep',100 
        UNION ALL
    SELECT 'Ankur',-10 
        UNION ALL
    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 @SMTPAddress 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 = '192.168.1.101';

SET @EmailAddress = 'rajsudeep@gmail.com';

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.


DECLARE @tableHTML NVARCHAR(MAX) ;
    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>' +
            CAST((
        SELECT
                    td = ID, '',
                    td = CustName, '',
            'td/@bgcolor'=CASE WHEN Amount>0 THEN 'Green'
            ELSE 'Red' END,
                    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:

<html>

<body>

<h1>

Mail Header</h1>

<td>

ID</td>

<td>

CustomerName</td>

<td>

Amount</td>

</tr>

<tr>

<td>

1</td>

<td>

Sudeep</td>

100</td>

</tr>

<tr>

<td>

2</td>

<td>

Ankur</td>

-10</td>

</tr>

<tr>

<td>

3</td>

<td>

Manju</td>

3</td>

</tr>

</table>

</body>

</html>

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.

Prerequisites:

· 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.

Sample Packages

noreply@blogger.com (Sudeep Raj) — Wed, 10 Mar 2010 13:26:12 PST

I am uploading a few sample packages that you could use and modify as per your requirement. Let me know if you need any specific sample or any change in these packages.

DynamicFileConnection.dtsx

This package shows an example where the file connection is changed dynamically based on inputs from the database table. This package includes Execute SQL Task, Foreach Loop with ADO Enumerator, Data Flow Task including Flat File Connection Manager, Derived Column for date transformation, OLEDB Destination and Flat File destination for error records. For step by step description check this post.

DeleteFiles.dtsx

This package picks files from folder and deletes files based on the file name. It makes use of Foreach Loop, dummy Script Task, Precedence Constraint and File System Task.

RemoveDuplicates.dtsx

This is a simple package where I just show how we remove duplicates from the records and perform lookup with no caching. This package includes Sort Transform, Lookup Transform, Flat File Source and OLEDB Destination.

QueryFromFile.dtsx
This package executes a set of queries kept in a folder using For Each Loop and Execute SQL Task

I will be uploading more samples soon.

Update1:
SQLXMLtoFile.dtsx
This package takes a XML stream from Execute SQL task as output and uploads the file to a XML File. The SQL Query could be of any form like:
SELECT * FROM Table1 FOR XML Auto
or
SELECT * FROM Table1 FOR XML PATH('Test')

Update2:
DeleteOldestFile.dtsx
This package will delete the oldest file from a given folder. The filename is of the format File_MMDDYYYY.txt For more details on this package I will be posting a blog soon.

All the Packages

Assigning variables in Script Component

noreply@blogger.com (Sudeep Raj) — Sun, 04 Apr 2010 22:54:41 PDT

One of the sore points of using Script component is assigning value to variables.
Read Write variables can only be altered in Post Execute phase. Script Component runs the code for each record and this would affect performance adversely. For details check "Coding and Debugging the Script Component".
In case I need to assign the value of system variable "PackageName" to a readWrite user variable "PkgNm"

Here is a sample code to assign value to Read write variable on the post execute phase:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

ImportsMicrosoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

' I have not added any code here

End Sub

Public Overrides Sub PostExecute()

Me.Variables.PkgNm = Me.Variables.PackageName

Msgbox(Me.Variables.PkgNm)

MyBase.PostExecute()

End Sub

End Class

Dynamic file handling and formatting dateTime.

noreply@blogger.com (Sudeep Raj) — Thu, 25 Feb 2010 14:03:57 PST

Hi,

I’m back with the second post. As I said I would be updating on how to set the file name dynamically for multiple files kept in a folder or different folder.

Point to note that all the files have the same metadata.

Scenario:

For simplicity sake I will be using a case where the file names and file paths are saved in a DB table. There is an ID auto increment field to based on which the files need to be picked and inserted into the destination Table.

The above table’s Schema is:

You can download this package from here.

CREATE TABLE [dbo].[FileConfig](

[ID] [int] IDENTITY(1,1) NOT NULL,

[FileName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FilePath] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

The input file data is comma delimited

The destination is SQL Table.

The data will be modified as per the definition given below.

Input File Columns	Output File Columns
ID -> String	ID -> unsigned Integer
FName -> String(50)	FName -> String(50)
LName -> String(50)	LName -> String(50)
Age -> String	Age -> unsigned Integer
DOB > String(DD/MM/YYYY)	DOB > DateTime

So the Package will look like this:

Create 2 string Variable: FileName and FilePath with the scope as the package(Practice in my case). Set the value of the variable to a file name and path that exists (I set it to “File1.txt” & “C:\SSIS\Practice\Files”) just while development.

Next create an object variable(ObjfileDetails) to store the result set from the initial Execute SQL Task.

Now set up the Execute SQL Task to get the file name and path. We save the full result set to an object variable.
ResultSet : Full Result Set
ConnectionType : OLEDB(that’s what I have used you could also use ADO.NET)
Connection : Select the appropriate one.

Note: it is ordered by ID column.

Set up the Result set as shown in figure

Note: The Result Name is set to 0.

Once the SQL Task will be executed this object variable will containing the entire result set returned by the query.

In the collections tab do the below settings:

Use the object variable in the For each loop.

Set the enumerator to Foreach ADO Enumerator.

Next goto the Variable mapping and do the below mentioned settings:

Select the 2 string variable to which the file name and file path will be saved for each iteration of the object variable.

Note: Set the index starting from 0 and the order should be same as the order of the columns fetched in the SELECT query above.

Once this is done we put a Data Flow Task(DFT) in the above For Each Loop.

In the DFT use:

1. Flat File Source to fetch the data from the input file.

2. Derived Column transformation to change the string date field to DB_DBDATE

3. OLEDB Destination to send the valid records to database table.

4. Flat File destination to log the error records.

Let’s have a look at the layout of the DFT.

In the Flat file source we make a new connection manager where we set browse and set the file path of an existing file.

In the Advanced tab set the Column Names as per the need and set the data type to which you can convert the data without the loss of information like the numeric columns. Leave the other to default or set the max width of the string columns.

Once the Flat File connection manager is set up select the columns you need in the flow and your done.

Next connect the Flat File Source to the Derived column to change the DOB column to DT_DBDATE format.

To do this in the derived column add a new column give it a name and set the expression to :

(DT_DBDATE)(SUBSTRING(DOB,4,2) + "-" + SUBSTRING(DOB,1,2) + "-" + SUBSTRING(DOB,7,4))

Once the expression is set click the Configure Error Output button at the bottom.

In case of error in the Derived Column transform (e.g. the date field is No Value, “00/00/000” or any invalid date) set it to redirect row so that it can be logged and analyzed.

For the correct records send the data to Table

In the OLEDB Destination:

Select the OLEDB Connection needed to connect to the destination Data base.

Select the table where the columns need to be inserted.

Goto the mapping

Go the mapping and do the mapping as required and click OK.

For the error records(red arrow) after the derived Column set a flat file destination to log the error records.

The DFT is now all set.

Now the DFT Flat File source needs to be made dynamic.

To achieve this follow the steps below.

Goto the connection manager right click-> Properties

In the properties pane for the above connection manager:

Set the DelayValidation to True

Expand the Expressions by clicking the ellipse.

On the Property Expressions Editor window:

In the Property column select the ConnectionString property from the drop down list.

Now expand the Expression column by clicking the ellipse. An Expression Builder window will pop up.

On the Expression Builder window:

Set the Expression as: @[User::FilePath]+ “\\” + @[User::FileName]

Click the Evaluate expression button at the left hand bottom to see if the expression is creating the desired output.

Click OK.

Click OK on the Property Expression Editor as well.

Save and build the package.
Now when you execute the package the package will take the filenames from the DB table and dump the data in order in the final table.

Let me know if you need any further clarification or I am not clear in any step.

Expressions

noreply@blogger.com (Sudeep Raj) — Wed, 07 Sep 2011 15:13:40 PDT

Hi,
Lets talks about EXPRESSIONS.
It is one of the most interesting and used feature available in SSIS. At times we tend to over do in out attempt to work using Expressions. One needs to decide when it is best to use an expression. A few pointers that I would like to provide.

Use expressions where they are small
Do not make the expression overtly complicated. It makes management difficult.
Avoid too many nested conditions in expressions.

Expressions can be used at various places:

Precedence constraint - Should evaluate to TRUE or FALSE
Setting up variable value - Should be of the variable data type
For Loop
Setting up properties for various objects dynamically especially Connection Strings for various Connection Managers
Derived Column Transform(most often used here)
Conditional Split

Sample expressions:

Get today’s date in the format DD/MM/YYYY

RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",31,GETDATE())),2) + "/" + (DT_WSTR,4)YEAR(GETDATE())

Pad a string with zeroes on the left(output length-10)
RIGHT((“0000000000”+“STRING”),10)or

REPLICATE("0",10 - LEN("String")) + "STRING"

Convert date of input string column to date time. Validate that if its value is NULL or empty or illegal date it should be converted to Null date.

ISNULL([Column 0]) || LEN(TRIM((DT_WSTR,10)[Column 0])) == 0 || [Column 0]==”00-00-0000” ? NULL(DT_DATE) : (DT_DBDATE)((DT_WSTR,10)[Column 0])

Create file name with the current time stamp in the format: FileName_YY-MM-DD-HHMMSS.txt
"FileName_" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10) + SUBSTRING((DT_WSTR,30)GETDATE(),12,2) + SUBSTRING((DT_WSTR,30)GETDATE(),15,2) + SUBSTRING((DT_WSTR,30)GETDATE(),18,2) + ".txt"

Get the difference of the date in a column w.r.t. today’s date

DATEDIFF("d",(DT_DATE)(SUBSTRING([Column 0],10,2) + "-" + SUBSTRING([Column 0],12,2) + "-" + SUBSTRING([Column 0],6,4)),GETDATE())

If length of a column or variable is zero set its value to “asd” else set it to the variable value.

LEN([Column 0]) == 0 ? "asd" : [Column 0]

FindString function:

FINDSTRING("111abcda","aa",1) this will give 0

FINDSTRING("111abcda","a",1) this will give 4

FINDSTRING("111abcda","a",2) this will give 8

Check if a string contains a value do Step1 else Step2

FINDSTRING("111abcda","aa",1) >0? Step1 : Step2

Set File name in the following format: FileName_MMMDD_YYYY.txt (FileName_Nov23_2009.txt)

"FileName_" + (MONTH(GETDATE()) == 1 ? "Jan" : MONTH(GETDATE()) == 2 ? "Feb" : MONTH(GETDATE()) == 3 ? "Mar" : MONTH(GETDATE()) == 4 ? "Apr" : MONTH(GETDATE()) == 5 ? "May" : MONTH(GETDATE()) == 6 ? "Jun" : MONTH(GETDATE()) == 7 ? "Jul" : MONTH(GETDATE()) == 8 ? "Aug" : MONTH(GETDATE()) == 9 ? "Sep" : MONTH(GETDATE()) == 10 ? "Oct" : MONTH(GETDATE()) == 11 ? "Nov" : MONTH(GETDATE()) == 12 ? "Dec" : "InvalidMonth") + (DT_WSTR,3)DAY(GETDATE()) + "_" + (DT_WSTR,5)YEAR(GETDATE()) + ".txt"

Functions available to create EXPRESSIONS

I will talk about some of the most commonly used functions in the next section.

Mathematical Functions
FUNCTION	Result	DataType
ROUND( 2.23 , 2 )	2	Integer
ROUND( 2.53 , 2 )	3	Integer
CEILING(2.23 )	3	Numeric
FLOOR(2.9)	2	Numeric
ABS(3.2)	3	Numeric
ABS(3.9)	4	Numeric
ABS(-3.2)	3	Numeric
ABS(-3.9)	4	Numeric

String Functions
FUNCTION	Result	DataType
LOWER( "ABD" )	abc	String
UPPER( "abd" )	ABD	String
LTRIM( " abc " )	"abc "	String
RTRIM( " abc " )	" abc"	String
TRIM( " abc " )	"abc"	String
REVERSE( "abc" )	"cba"	String
RIGHT( abc, 2 )	"bc"	String
SUBSTRING( "abcde", 2 , 3 )	"bcd"	String
REPLICATE("a",3)	"aaa"	String
FINDSTRING("abcd","ab",1)	~~"ab"~~ 1	String
REPLACE("Apple", "pp", "n app")	"An apple"	String

Date/Time Functions
FUNCTION	Result	DataType
DATEADD( "mm", 1, (DT_DATE)"12/24/2009" )	1/24/2010 0:00	DateTime
DATEADD( "dd", -1, (DT_DATE)"12/24/2009" )	12/23/2009 0:00	DateTime
DATEADD( "yy", 1, (DT_DATE)"12/24/2009" )	12/24/2010 0:00	DateTime
DATEDIFF( "dd", (DT_DATE)"12/24/2009", DT_DATE)"01/26/2010" )	33	Integer
DATEDIFF( "mm", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )	1	Integer
DATEDIFF( "yy", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )	1	Integer
DATEPART( "dd", (DT_DATE)"12/24/2009" )	24	Integer
DATEPART( "mm", (DT_DATE)"12/24/2009" )	12	Integer
DATEPART( "yy", (DT_DATE)"12/24/2009" )	2009	Integer
DAY( (DT_DATE)"12/24/2009" )	24	Integer
MONTH( (DT_DATE)"12/24/2009" )	12	Integer
YEAR( (DT_DATE)"12/24/2009" )	2009	Integer

NULL Functions
FUNCTION	Result	DataType
ISNULL( «expression» )	TRUE/FALSE	Boolean
NULL(DT_DATE)	NULL	DateTime

Type Casts

FUNCTION

(DT_STR, «length», «code_page»)

(DT_WSTR, «length»)

(DT_NUMERIC, «precision», «scale»)

(DT_DECIMAL, «scale»)

Operators
FUNCTION	Comments
?:	If then Else -> "Condition"?"True Action": "False Action"
&&	Logical AND
\|\|	Logical OR

SSIS help

noreply@blogger.com (Sudeep Raj) — Wed, 18 Nov 2009 12:42:25 PST

Hi,
If you need any kind of help on SSIS do feel free to contact me.