Dynamic Redirection of Rows to Designated "Folders\Files"

Today, we will see how we can redirect rows from a database table to designated folder and then a file dynamically. Let me explain the scenario first -

We have a table say Test, having following instance:

Now, if you notice we have S1, S2, S3, S4 and S5 as 5 distinct ‘SubCategory’. We have one root folder, say, D:\RootFolder, inside this root folder 5 folders (S1..S5) would be created dynamically and in each folder a text file would be created for that day where rows would be copied based on the subcategory. So in folder S1 a file S1_20110321.txt would be created and all the records pertaining to ‘S1’ would be copied here. Similary all the records pertaining to Si subcateogry would be redirected to Si folder and copied to Si_today'sdate.txt file.

Let's see how we can implement it. First of all we would require three variables, objSubCat, strQuery and strSubCat. objSubCat is a Object variable to store the record set fetched from the DB, strQuery is used to store a string for creating DB query and strSubCat is used for storing the subcategory.

We also need to make sure that value of strQuery should change dynamically in order to fetch the correct recordset, hence, we'd be using expression to achieve dynamic behavior. Following screen shot shows the expression settings -

 

Below screen shot shows these variables and their default value -

Now, we would create an ExecuteSQL task to store all the distinct SubCategory in objSubCat variable. Then based on objSubCat variable ForEachLoop would process the data accordingly. Following screen shot is how our package would look like -

Now, I'll show you how to configure each task in the control flow to achieve the given scenario.

1. Exectue SQL Task: This task is used to store all the distinct SubCategory to an object variable objSubCat. Following two screen shot shows how I have configured General and Result Set of Execute SQL task.

(a) General:

(b) Result Set:

Once this task is executed in run time it will store distinct sub categories in objSubCat variable which would be used for looping the data set.

2. FOREACH Loop Task: Foreach loop will iterate based on the objSubCat variable. Since we can't use object variables in the expressions we need to assign value of objSubCat to some other 'non-object' variable and here we would use strSubCat. Following are the configuration used for this task.

(a) Collection:

(b) Variable Mappings:

3. File System Task: This task will create folder at run time dynamically. Following are the configuration which are used to achieve it.                                                                                                                

Following screen shot shows setting about Source Connection property.

 Following screen shot shows setting about Source Connection property -


"D:\\RootFolder\\" +  @[User::strSubCat]    

                                                                                                                     

4. Data FlowTask: This task is the main task which will used all the configuration we have done and would  redirect records accordingly. Following components are used -                                                             

OLEDB Source is used for fetching the records from the DB Test table dynamically based on the iteration     state. The query gets generated dynamically for the query variable. Please refer below screen shot for the      details -                                                                                                                                         

Once this is done we just need to configure Flat File destination. The setting is simple, we just need to configure Flat File Connection manager's Connection String dynamically. Following dynamic string is used to achieve this -

"D:\\RootFolder\\" +  @[User::strSubCat] +"\\"+@[User::strSubCat] +"_"+ (DT_STR, 14, 1252)  datepart ("yyyy",getdate()) + (DT_STR, 14, 1252)  datepart ("mm",getdate()) + (DT_STR, 14, 1252)  datepart ("dd",getdate()) + ".txt"

It's done. Now, just run the package and see if folders like 'S1', 'S2' etc are created inside D:\RootFolder  and there are text files with correct records.

Hope you find it interesting and please let me know your feedback, any better approach would be highly appreciating.