How to change flat file source using foreach loop

Question

How can i change Flat file path using foreach loop container in SSIS

I'm having flat file which is under c:\sourcefile name of the file is "Flat(Date)" for e.g Flat01aug2011.csv and every day it is generated by recent date so i need to picked up new file always and pass it to Flat File destination , so how can i traverse through folder and take new file which is having recent date.???

Answer 1

Example in the below given link shows how you can loop through files of a given extension and load them into a database using Data Flow Task.

The example loads .csv files. The package uses a Foreach loop container to loop through the files and there is a Data Flow task within the loop container. The Data Flow task contains a Flat file source which changes based on the file being looped through. This is done by altering the Flat File Connection Manager path.

How do I move files to an archive folder after the files have been processed?

Here is an example that loops through a list of directories within a given directory and picks the most recently created folder using Script Task and Foreach loop container. You need to make use of a similar logic to pick the most recently created file.

How do I pick the most recently created folder using Foreach loop container in SSIS package?

Hope that helps.

Update:

Create two string data type variables namely DirPath and FilePath. Set the value C:\backup\ to the variable DirPath. Do not set any value to the variable FilePath.

Select the variable FilePath and select F4 to view the properties. Set the EvaluateAsExpression property to True and set the Expression property as @[User::DirPath] + "Source" + (DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

Answer 2

In SSIS you can use 'ForEach Loop' container and set enumerator to file name. For each loop container takes properties where you can provide folder location and file name format and extension.

With foreach loop container you can process multiple files too based on the file name.

See the tutorial at the following link. http://www.sql-programmers.com/SQLResources/SQLServerIntegrationServiceSSIS/ForEachLoopContainer.aspx