Search results matching tag 'Dynamic File Connection'

Loop through each record in a text file : Recordset Destination

sudeep — Wed, 08 Sep 2010 19:43:00 GMT

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.

Caution: Do NOT use this method to read each record in the file if it caontain data and try to emulate a cursor. Use a data flow task instead.

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. The Foreach loop consumes this object variable and iterates through each record in the object variable. In the foreach loop the variable are mapped to 2 string user variables one to store the source file relative path and the other to store the Destination file relative path. At the end of the package execution all the files mentioned in the 1st column of the text file needs to be moved to the destination based on the 2nd column value.

Variables used in the package:

obj: Object type variable to store the records from the text file as an object.

strBaseFolder: The base folder where all the other files and folder reside.(not necessary to have this inn case the source and destination locations are in different place)

strInputFile: Store the relative file path of the source file. Details will be discussed when I talk about the Foreach Loop.

strOutputFile: Store the relative file path of the destination file. Details will be discussed when I talk about the Foreach Loop.

Connections used in the package:

All the connection used are Flat File Connections

Connections: It provides the connection to connect to the flat file having all the connections. The connection string is set by the expression @[User::strBaseFolder] + “\\Connections.txt”

Input: It provides the connection to the input file which needs to be moved to the output file. The connection string is set by the expression @[User::strBaseFolder] + "\\" + @[User::strInputFile]

Output: It provides the connection for the destination file. The connection string is set by the expression @[User::strBaseFolder] + "\\" + @[User::strOutputFile]

DFT1 settings:

In the Flat file source we use a connection to read the text file as a comma separated file.

Recordset Destination:

Component Properties tab

Note: The variable obj is a user variable of object type.

In the Input Properties tab only check the columns you wish to sent to the object variable.

No need to make any changes in the 3rd tab “Input and output properties”. We now have the obj variable which can be used in the foreach loop to iterate through each record. The remaining logic is same as applied to any other foreach loop.

Now we come to the Foreach loop and set it up. Set the enumerator to “"Foreach ADO Enumerator”. Select the ADO obj Source variable from the drop down box and set it to “User::obj” variable. The Enumerator mode is set to default “Rows in first table”.

We now need to read the value present in each row and assign it to string variables to be consumed later. Add the user variables by selecting them from the drop down list the user variables. Set the index to 0 for the first column data and index to 2 for the 2nd column variable.

In the DFT 2 we have a flat file source and a flat file destination. I have used no transforms in this scenario which you could include as per your requirement.

The Flat file source uses the connection “Input” while the destination uses the connection “Output”mentioned earlier.

The package is ready for execution.

I have uploaded the package Recordset for you reference.

SSIS - Sample SSIS Packages

sudeep — Tue, 16 Feb 2010 04:32:00 GMT

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.

SSIS - Script Component, Split single row to multiple rows

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

Package download

SSIS–Dynamically set File Mask : FileSpec

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.

Package download.

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.

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.

For detailed description continue……

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. For more details on this package check my blog.

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

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')

DeleteOldestFile.dtsx
This package will delete the oldest file from a given folder. The filename is of the format File_MMDDYYYY.txt.

All the Packages