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

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.

1.b*.txt

2.a*.txt

3.d*

4.*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

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

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