SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2082 Visits: 696
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.
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7942 Visits: 2629
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.
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2082 Visits: 696
thats just what I wanted!!!!

thankyou for that :-) Its becoming much clearer now.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search