January 25, 2012 at 5:49 pm
I have a dynamic object variable named plan_code which contains the list of plan_codes I have in the sql table. Now the problem is that I have to export the data from the sql table to separate flat files for each of the plan_code.
e.g.
plan_code = 1,2,3,4,5
so, package should create 5 different flat files namely:
package_name_1.txt
package_name_2.txt
package_name_3.txt
package_name_4.txt
package_name_5.txt
Also, the number of plan_codes will differ..e.g. sometimes table will have 2 plan_codes, sometimes it will have 4..so correspondingly different number of flat files should be created..
Can somebody please tell me how to do this??
I am thinking of doing this using script task..but not sure what c# code will achieve this..
Thanks in advance
January 26, 2012 at 12:14 am
Put a For Each Loop and loop over the object variable. Put the plan code value into a variable and use this variable in an expression to set your flat file connection. In each iteration of the for each loop, you write the data for one plan code to it's corresponding flat file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 26, 2012 at 11:20 am
Thank you buddy for your valuable feedback..as per your suggestion..I am trying to do it using Flat_File_Destination only..so I created a new flat file connection manager..gave it a path of existing file and then set its delay validation to true and set the expression of connection string to
@[User::FilePath] +@[User::FileName] +"_"+ @[User::Plan_ID]+""+(DT_STR,29,1252) GETDATE()
As mentioned in the problem statement..@[User::Plan_ID] will be set during the run time..
Then I dragged a flat file destination to the package and set its connection manager to the one created above..but when I run the package..the value of the connection string is not setting up I think..as I am getting an error..
[Flat File Destination [476]] Error: No destination flat file name was provided. Make sure the flat file connection manager is configured with a connection string. If the flat file connection manager is used by multiple components, ensure that the connection string contains enough file names.
[SSIS.Pipeline] Error: component "Flat File Destination" (476) failed the pre-execute phase and returned error code 0xC0202010.
January 26, 2012 at 12:18 pm
anyways..the problem has been solved..the date was not casting to string properly..and thats why I was getting the error..so the following expression worked..:-D
@[User::FilePath]+@[User::FileName] +"_"+ @[User::Plan_ID]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply