Post reply

Integration Services. Exporting to multiple CSV Files based on a Column in the table as a variable

  • February 15, 2013 at 9:25 am

    #150263

    Hi

    I seem to have completely forgotten how to do this and cant make it work

    I have a SQL Table and I want to export to data CSV based on a specific column in the table.

    Firstly I have an execute SQL Task with one column in it DFES_Number

    For example

    DFES_Number

    0001

    0002

    0003

    0004

    ResultSet = 0 ResultName = CreateVariable (0 because its using the whole table in this case just the one column at the moment)

    Next I have a Foreachloop container. In this case in

    Collection and Enumerator = Foreach ADO Enumerator

    The Ado Source variable is User::CreateVariable (As above)

    The variable Mappings in DFES_Number with an index of 0

    Within this for look I have a data flow task.

    The source is a SQL Database. I have tried to set it up so it goes

    SELECT A, B, C, D, E,F From source table

    WHERE Dfes_Number = @DFES_Number

    But in all cases Im getting Statement could not be prepared ‘Must declare the scalar variable DFES_Number’ I thought I had done this as within my variables list I have a DFES_Number set as string.

    Im now at a loss. Im trying to find some good guides on how to use a SQL Destination and export to multiple CSV files but Im not doing a very good job.

    If anyone could help it would be much appreciated.

  • February 18, 2013 at 8:07 am

    #1588868

    In your query

    SELECT A, B, C, D, E,F From source table

    WHERE Dfes_Number = @DFES_Number

    change the @DFES_Number to a question mark

    SELECT A, B, C, D, E,F From source table

    WHERE Dfes_Number = ?

    Click the Parameters button on your Source Editor and put your @DFES_Number in there.

    Another approach I like to take in a situation like this is to create an expression driven variable for my query and put the query together with the expression. For example a new variable named SQLQuery with an expression something like

    "SELECT A,B,C,D,E,F, FROM SourceTable WHERE DFES_Number =" + (DT_STR, 4, 1252)@DFES_Number

    Then in the Source editor use a SQL Command from Variable and select the SQLQuery variable as your query source.

  • February 18, 2013 at 8:09 am

    #1588870

    thats just what I wanted!!!!

    thankyou for that 🙂 Its becoming much clearer now.

Viewing 3 posts - 1 through 2 (of 2 total)

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