Kayda:
(FYI, there is now a dedicated SSIS Tech net section that may get you more
responses than posting in the DTS section.)
Anyway:
Does this job need to be done only once? If so, I would just run it once
while connected to ServerA, then switch the ConnectionManager to ServerB and
run it again.
If not, then you can propably do it with a ForEach loop with one data flow
inside it.
On the Collection page of the ForEach Loop editor, select ForEach Item
Enumerator. Then add one column in the item list of type String and add two
values to the list with "ServerA" and "ServerB".
Inside the Loop, create a script task the will assign the ServerName portion
of the appropriate Connection Manager to the value in the list. Next inside
the Loop, create one Data Flow. In that , you can have your 15 separate data
streams, each with its own Source and Destination adapter.
HTH
--
Todd Chittenden
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]
[quoted text, click to view] "Kayda" wrote:
> Hi:
>
> I need to move data from 15 tables from Server A to SQL Server 2005
> database C, and then move data from 15 tables from Server B to SQL
> Server 2005 database C. (For the record, Servers A and B are different
> database products). The 15 tables on server A and server B are exactly
> the same structure, I'm baically merging the data into database C. So
> here is a simplified version of 1 of the tables:
>
> ServerA - Table 1
> Field Site
> "Bill Smith" SiteA
>
> ServerB - Table 1
> Field Site
> "Jane Doe" SiteB
>
> DatabaseC- Table 1
> Field Site
> "Bill Smith" Site A
> "Jane Doe" Site B
>
> So I have two SQL Server 2005 tables, one with DSN connection
> information, (one record for each DSN). And another database that will
> have the table names (which are the same remember for Server A and
> Server B. So I want to create a loop within a loop, the outer loop will
> loop through the DSN connection information (DSN name, user and
> password) and set the source connection logon information. The inner
> loop will loop 15 times, setting the source tablename and the output
> table target.
>
> I've done this before in DTS but want to do it in SSIS 2005. I know the
> outer loop is possible in SSIS 2005, I just don't know how to do it
> with the For Each Container. The inner loop I'm not sure about, maybe I
> have to create 15 separate data flow tasks?
>
> Thanks for any help,
> Kayda
>
