Variable in flat file destination

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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)

  • 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)

  • 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

  • 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)

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

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