Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rockstar283
Yak Posting Veteran
96 Posts |
 Posted - 2012-01-26 : 13:30:29
|
| 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 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.Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-26 : 13:34:26
|
| just give some default values to a variables so that connection string has a default value. Then provided that you're getting values correctly inside loop you use over object variable, it should work fine.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-01-26 : 14:05:10
|
| Thanks for replying mate..@[User::Plan_ID] is an object variable..so cannot give it a default value..in the connection manager..I have provided a default file name..and in regular expression..I have provided the expression given above..still it is failing..the next time I open the connection manager, instead of the default file name, i see the result of the regular expression.. :( |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-01-26 : 14:18:38
|
| 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] |
|
|
|
|
|
|
|
|
Dynamic flat file connection manager for creating