ETL data from 10 different SQL Servers to one

I want to create a package that will import data from 10 different servers
all with the same source table structure. The destination table has a few
extra columns, like store number that needs to get set based on the server
that is currently being imported from. The stores are listed in a table and
I want the SSIS to loop through each record in the store master table.

Any suggestions on using SSIS? I usually use store proc's but thought I'd
try out the new SSIS tools. Server 2005 at the moment.

You should create an SSIS package with a For Loop container. For each
iteration of the loop, you can set a variable to the remote server name,
pull the rows from the remote server, and include the variable in one of the
output columns. All output is then stored into the local server.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Bradley" wrote in message
news:6F0BA75F-216F-49CE-8443-BCEF52F8C2AC-at-microsoft.com...
I want to create a package that will import data from 10 different servers
all with the same source table structure. The destination table has a few
extra columns, like store number that needs to get set based on the server
that is currently being imported from. The stores are listed in a table and
I want the SSIS to loop through each record in the store master table.

Any suggestions on using SSIS? I usually use store proc's but thought I'd
try out the new SSIS tools. Server 2005 at the moment.

Bradley:
To expound on Tom's suggestion:
Create an Execute SQL task that reads the table that has the list of servers
in it. Set it's Result Set to "Full result Set". In the Result Set page, add
a Result set, named "0", and map it to a variable of type "Object". This will
load the table from the database into the Object Variable.
Now create a For Each loop, and set the Enumerator as a "For Each ADO
Enumerator". Grab the object variable to enumerator over. Set Variable
Mapping accordingly, one new variable for each column in the table object
(indexed as 0, 1, 2, etc).

Create one Connection Manager to one of the servers. In its Expression list
(under Properties) create an Expression for the Connection String property
and use the Expression Builder to create the string utilizing the
[User::ServerName] variable from the loop and some literal strings. Example:
"ServerName=" + [User::ServerName] + ";Initialcatalog=MyDatabase;SSPI=true..."

Now, every time through the loop, the Connection String of the connection
manager will change, resulting in the package connecting to a new source.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Tom Moreau" wrote:

> You should create an SSIS package with a For Loop container. For each
> iteration of the loop, you can set a variable to the remote server name,
> pull the rows from the remote server, and include the variable in one of the
> output columns. All output is then stored into the local server.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Bradley" wrote in message
> news:6F0BA75F-216F-49CE-8443-BCEF52F8C2AC-at-microsoft.com...
> I want to create a package that will import data from 10 different servers
> all with the same source table structure. The destination table has a few
> extra columns, like store number that needs to get set based on the server
> that is currently being imported from. The stores are listed in a table and
> I want the SSIS to loop through each record in the store master table.
>
> Any suggestions on using SSIS? I usually use store proc's but thought I'd
> try out the new SSIS tools. Server 2005 at the moment.
>
>

Hi Tom & Todd,

Thanks for your quick replies. I cant figure out how to get the Connection
Manager to show the proper options to dynamically insert a variable into the
string.

Thanks,
Bradley

Bradley:
Click on the CM to highlight it. Look in the Properties page, usually
located in the right panel or lower right corner.
Click in it then click the elipsis (...). This will open the "Property
Expression Editor.
Under Property, select Connection String, then next to that, click that
elipsis to open the Expression Builder.

Good luck
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Bradley" wrote:

> Hi Tom & Todd,
>
> Thanks for your quick replies. I cant figure out how to get the Connection
> Manager to show the proper options to dynamically insert a variable into the
> string.
>
> Thanks,
> Bradley

Hi Todd,

Thanks, I think I figured that part out. What if I want the query that is
returning the store link server name to also return the store number and use
that variable in another place? Basically, can the query that is being used
by the loop return two columns and those two columns get mapped to variables?

Thanks,
Bradley

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Bradley" wrote:

> Hi Todd,
>
> Thanks, I think I figured that part out. What if I want the query that is
> returning the store link server name to also return the store number and use
> that variable in another place? Basically, can the query that is being used
> by the loop return two columns and those two columns get mapped to variables?
>
> Thanks,
> Bradley

Yes, absolutely.

The query in the Execute SQL task that populates the Object Variable will
now look like this:
SELECT ServerNumber, SeverName, ... FROM dbo.ServerList

In the Variable Mapping page of the ForEach loop task, map each column in
the SELECT statement to a Package variable of the appropriate type. Index
should start at 0 and go up from there, so if you have 3 column, they would
be indexed as 0, 1, and 2.

Here's a hint: Expand your ServerList table to include an InActive flat and
include that in a WHERE clause in your SELECT statemetn. You can now change
the behavior of your package by simply flipping a bit in a control table.

HTH

(I see I didn't mention "Expressions" in the Properties page, but it looks
like you got it figured out.)
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Bradley" wrote:

> Hi Todd,
>
> Thanks, I think I figured that part out. What if I want the query that is
> returning the store link server name to also return the store number and use
> that variable in another place? Basically, can the query that is being used
> by the loop return two columns and those two columns get mapped to variables?
>
> Thanks,
> Bradley