Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

Loop through each record in a text file : Recordset Destination

Sep 9 2010 4:43AM by Sudeep Raj   

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.

Tags: Connection Manager, For EAch loop, Dynamic File Connection, MSBI, Flat File Destination, BRH, Flat File Source, SQL Server 2008, Recordset Destination, SQL Server 2005,


Sudeep Raj
12 · 13% · 4303
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Loop through each record in a text file : Recordset Destination" rated 5 out of 5 by 1 readers
Loop through each record in a text file : Recordset Destination , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]