0

This is more of a generic question :

I have file1.sql, file2.sql , file3.sql in a folder. I can run a foreach container to loop through the files and execute it but I need the result set to go to respective tables sitting on a different server

file1 result set --> Server2.TableA file2 result set --> Server2.TableB .. etc

How can this be achieved through SSIS techniques ?

thestralFeather7
  • 529
  • 2
  • 10
  • 28
  • SSIS doesn't work that way. The results from an Execute SQL Task can't be sent to a destination. You're looking for a Data Flow Task but as I said [over here](http://stackoverflow.com/q/40893543/181965) those must be bound at design time. – billinkc Nov 30 '16 at 21:21
  • I think this can be done, but we need more information. Can you answer? You are looping over .sql files and executing the sql inside? The .sql is select queries? You have a ForEach Loop with an Execute SQL Task inside? Not using DataFlows? Can you post a picture of your loop? You mention different servers, but your example shows different tables, same server. We need some clarification. It's possible this can be done with variables. – Troy Witthoeft Dec 02 '16 at 13:11

3 Answers3

1

You can do this with a script task in the foreach loop, that analyses the result set and inserts it to the appropriate destination table.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
1

You could also put all the records into a staging table on one server with additional columns for that server they will go to and a isprocesssed bit field.

At this point you could do any clean up required of the data.

Then create a separate dataflow for each server to grab the unprocessed records for that server. After they are sent, then mark the records as processed.

This will work if you only have a few servers. If there are many possibilities or you expect the number will continue to change, I would go with @TabAlleman's suggestion.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

thestralFeather,

If you are new to SSIS, refer to msdn's tutorial on looping utilizing SSIS here. If you look at this page within the tutorial, you will see in the dataflow the output destination. @Tab Allerman and @HLGEM have provided good advice. When you look at the pages I've referred you to, just thing in terms of 2 separate loops dropping data to a single location that you can manage in a target dataflow.

plditallo
  • 701
  • 2
  • 13
  • 31