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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Integration Services. Exporting to multiple...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Debbie Edwards

Posted Friday, February 15, 2013 9:25 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 422, Visits: 632

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.

Post #1420620

Daniel Bowlin

Posted Monday, February 18, 2013 8:07 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 2,672, Visits: 2,417

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.

Post #1421237

Debbie Edwards

Posted Monday, February 18, 2013 8:09 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 422, Visits: 632

thats just what I wanted!!!!

thankyou for that Smile

Its becoming much clearer now.

Post #1421239

« Prev Topic | Next Topic »

Permissions