SQLTeam.com | Weblogs | Forums

Select Database name based on Source Folder at run time in SSIS




I came across through one complicated situation in SSIS.
I need to access 10 Folders which contains .txt files: \Root\Customer 1.txt files (File name and schema are same in all folders) and that should load in different databases. to achieve this, i am using For Each Loop and inside Foreach using Execute package tasks to point each file to the corresponding pacakge.

For Instance, From the \Root Folder if i am processing 'Customer 1' Folder files these data should be loaded into Customer 1 database.

i.e if folder is \Customer 2 --> Customer 2 DB
if folder is \Customer 3 --> Customer 3 DB and vice versa.
So, as soon as my SSIS selected Customer 1 folder target Database should be set to Customer 1 DB.

Here i am stucked with selecting database name based on customer folder .


is root folder name always the same?


Instead of looping over the directories - you want to put this information in a table and loop over the contents of the table. This way you can add a new customer to the table and your package will automatically start processing that customer - and you could use a flag to identify active/inactive customers.

You would set a variable for the database - and then use an expression to reset the connection information using the variable.