Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Integration Services. Exporting to multiple CSV Files based on a Column in the table as a variable Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 9:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 15, 2013 7:58 AM
Points: 423, Visits: 635
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
Posted Monday, February 18, 2013 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 2,818, Visits: 2,552
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
Posted Monday, February 18, 2013 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 15, 2013 7:58 AM
Points: 423, Visits: 635
thats just what I wanted!!!!

thankyou for that Its becoming much clearer now.
Post #1421239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse