October 3, 2007 at 12:25 pm
Hi,
Here is my problem :
I work on a SSIS package with SQL SERVER 2005
I need to extract data from a table and put these data in csv files
But... the flat files name should be dynamic and assigned by a variable ...
Here's an example of my table :
Column header :
Id, Name, Number
1 TOM 22
2 TOTO 44
3 SAM 44
4 RADIO 55
I expect to have 3 csv files :
USER_22.csv
USER_44.csv
USER_55.csv
for example : USER_44.csv contains :
2;TOTO;44
3;SAM;44
if there's 50 different number, i expect 50 files
can i do that in a dataflow ?
thanks for answering
October 3, 2007 at 1:59 pm
You cannot change your destination connection based on the record you are in in your data flow.
You will need to use a ForEach loop container using an ADO recordset for the loop. Make the recordset the distinct list of user numbers. Then, put your data flow within the loop container and set the select statement for your data source and the destination file name using expressions and/or variables.
October 4, 2007 at 5:44 am
edony44,
Please provide table structure and some sample data. There is a number of ways in SSIS for SQL Server 2005 to accomplish what you need.
Regards,
Wameng Vang
MCTS
October 5, 2007 at 8:14 pm
You can use the Export Column component to do this. You will need to construct a single Text Derived Column containing the Comma-Separated values, and use the Number column to create another Derived Column containing the output file name. This component will then enable you to write the Derived CSV attributes to the different files by Number.
Export Column is really intended for exporting blobs & unstructured text from the pipeline, but it can also do exactly what you are looking for without using a For Each loop.
October 5, 2007 at 8:23 pm
October 5, 2007 at 11:09 pm
mengus (10/4/2007)
edony44,Please provide table structure and some sample data. There is a number of ways in SSIS for SQL Server 2005 to accomplish what you need.
Regards,
Wameng Vang
:blink: I'm thinking that was done in the very first post on this thread 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2007 at 11:11 pm
edony44,
Does it have to be in SSIS?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2007 at 1:49 am
thanks you all for answering.
i've solve my problem by using a recordset of distinct number value and a fro each loop container
to change filename, i've use a variable and Expression
October 6, 2007 at 11:04 am
Ok... and thanks for the feedback about what you did to solve it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply