Variable in flat file destination

Question

Variable in flat file destination

edony44

SSC Enthusiast

Points: 149
More actions
October 3, 2007 at 12:25 pm

#179881

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

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.

mengus Ten Centuries Points: 1187 More actions · Answer 2

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

Filet SSC Enthusiast Points: 184 More actions · Answer 3

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.

Cheers

Filet

Filet SSC Enthusiast Points: 184 More actions · Answer 4

P.S. You will have to include the escape sequence for Carriage Return, Line Feed "\ r \ n" [no spaces - I have to include them or the sequence disappears from the post] in your derived column to ensure separate lines per input row

Cheers

Filet

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

edony44,

Does it have to be in SSIS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edony44 SSC Enthusiast Points: 149 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Ok... and thanks for the feedback about what you did to solve it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)