Dynamic flat file connection manager for creating

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

Rockstar, It should be pretty straightforward what you are trying to do. One thing that I noticed is you referenced plan_code but are using plan_id when setting the connection to the file. However, Just to make sure what your doing to set the object variable... Are you executing a SQL task to load it up? That's what you'll need to do and then connect that to a FEL to "shred" the results. Good luck, Brian K. McDonald, sqlbigeek

What's the data type of the "@[User::Plan_ID]" variable?

I'm wondering if it's a numeric type and needs to be something like this:

@[User::FilePath] + @[User::FileName] + "_" + (DT_STR,1,1252)@[User::Plan_ID] + "" + (DT_STR,29,1252)GETDATE() 

 If the Plan_ID is numeric and SSIS is having a conversion error while trying to validate the error, this might be resulting in a NULL ( or blank ) filename - which might explain that error.

Once you have the expression builder opened, click on "Evaluate Expression" to test it and visually inspect the "Evaluated value:" output in the text box. 

The way I tested was to create a variable and put the expression on the variable.  When I defined the plan_id variable, I made its data type "Int32".

Hope it helps!

Keith Hyer

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.. also the result of my regular expression is as follows:  C:\Users\filename___2012-01-26 11:06:46.554000000  as you can see those two underscores..it is like that because the value of Plan_id will be calculated during run-time and will be put there..

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]