Sudeep's Domain

SSIS - Foreach Loop Enumerators

noreply@blogger.com (Anonymous) — Thu, 7 Feb 2013 03:30:00 +0530

A lot of times while taking interview I feel that most people know of 2 enumerators used in Foreach Loop Container and they are “Foreach File Enumerator” and “Foreach ADO Enumerator”

So lets talk about the enumerators available in SSIS. Unless we know them we will not be able to implement them right?

What are the different types of enumerators available in SSIS Foreach Loop Container?

Foreach File Enumerator
Foreach Item Enumerator
Foreach ADO Enumerator
Foreach ADO.NET Schema Rowset Enumerator
Foreach From Variable Enumerator
Foreach NodeList Enumerator
Foreach SMO Enumerator

Continue reading @ Beyondrelational.com

SSIS - Split single row to Multiple rows

noreply@blogger.com (Anonymous) — Thu, 7 Feb 2013 01:26:00 +0530

Its been a long long time since I have blogged. So have I been away from forums hence no ideas to blog. I was back at forum today and got a nice scenario. How do you split a single row to multiple rows? The input file has only one row.

There can be 2 scenarios which arise:

Scenario 1. the number of columns expected for each output row are the same.

Input: 1*1*1~2*2*2~3*3*3~4*4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2	2
3	3	3
4	4	4

Scenario 2. The number of rows for each output row are different. We need to default values if we have less than expected columns and ignore the extra columns.

Input:

1*1*1*1~2*2~3*3*3*3*3*3*3~4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2
3	3	3
4	4

Notice the blanks in the 3rd column for 2nd and 4th row.

Continue reading here @ BeyondRelational.com.

You can now find me @beyondrelational.com

noreply@blogger.com (Anonymous) — Sat, 19 May 2012 04:54:00 +0530

Thanks for visiting my blog. I now blog at the below website, please check that out for my latest blogs on SSIS. See you there.

http://beyondrelational.com/members/sudeep/default.aspx

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

noreply@blogger.com (Anonymous) — Sun, 25 Sep 2011 23:33:00 +0530

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 (Anonymous) — Thu, 9 Jun 2011 23:44:00 +0530

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 (Anonymous) — Fri, 3 Jun 2011 10:57:00 +0530

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

SSIS – Process files in Sequence - Sudeep's Domain

noreply@blogger.com (Anonymous) — Tue, 10 May 2011 05:59:00 +0530

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 (Anonymous) — Wed, 9 Mar 2011 02:45:00 +0530

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 (Anonymous) — Wed, 23 Feb 2011 02:19:00 +0530

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

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

noreply@blogger.com (Anonymous) — Wed, 24 Nov 2010 05:09:00 +0530

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 (Anonymous) — Tue, 23 Nov 2010 05:39:00 +0530

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 as Source ADO.NET - Sudeep's Domain

noreply@blogger.com (Anonymous) — Sun, 3 Oct 2010 02:25:00 +0530

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

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

noreply@blogger.com (Anonymous) — Wed, 29 Sep 2010 01:16:00 +0530

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.

Loop through each record in a text file : Recordset Destination

noreply@blogger.com (Anonymous) — Thu, 9 Sep 2010 00:44:00 +0530

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 (Anonymous) — Thu, 2 Sep 2010 18:31:00 +0530

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 (Anonymous) — Mon, 30 Aug 2010 11:56:00 +0530

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 (Anonymous) — Mon, 30 Aug 2010 06:50:00 +0530

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 (Anonymous) — Mon, 30 Aug 2010 01:55:00 +0530

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 (Anonymous) — Mon, 16 Aug 2010 05:35:00 +0530

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 (Anonymous) — Wed, 12 May 2010 03:23:00 +0530

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 (Anonymous) — Thu, 6 May 2010 12:34:00 +0530

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 (Anonymous) — Wed, 7 Apr 2010 04:36:00 +0530

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 (Anonymous) — Wed, 10 Mar 2010 01:44:00 +0530

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 (Anonymous) — Fri, 26 Feb 2010 01:28:00 +0530

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 (Anonymous) — Tue, 12 Jan 2010 01:59:00 +0530

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