How to Move Data from Transactional Databases to a Master Database with SSIS

Question

I am very new to SSIS and I need to write a package that will move data from transactional databases to a master database. We have a transactional database per plant and the schema for all of these is identical. I need to go through each table in each database and copy all the data that hasn't been marked as exported to its corresponding table in the master database. After the records are successfully copied to the master database they should be marked as exported in the transactional database.

So far I've gotten my SSIS package to where I can iterate through the plant databases and read from one of the tables. I'm currently storing the resuls from that table into a variable. I accomplished the iteration part by using an expression in the For Each Loop Container's Connection Manager that sets the Initial Catalog to the current database name in the loop.

However, I'm not sure how to proceed after that. Here's a picture of my package's current state:

I've tried creating another Execute SQL Task that takes the results from Get New Apples and copies them to the master database. However, from what I've googled so far there doesn't seem to be an easy way to accomplish this.

A different approach I've tried is to create an OLE DB Source using the same connection manager as the For Each Loop Container. When I do that I get an error saying that the Apple table is not a valid object(My query being select * from Apple where exported = 0;).

Any suggestions as to how I can read a result set from a variable or get the OLE DB Source to work with the aforementioned Connection Manager would be very helpful.

I'm also open to alternate methods to accomplishing this. Like I said, I'm new to SSIS and am still feeling my way around it.

Originally I tried to make this as a stored procedure but it started to grow unmanagable and ugly very quickly:

SELECT * 
INTO   #tempapple
FROM   (SELECT * 
        FROM   [Plant1].[dbo].[Apple] 
        WHERE  exported = 0 
        UNION 
        SELECT * 
        FROM   [Plant2].[dbo].[Apple] 
        WHERE  exported = 0) AS x; 

INSERT INTO [Master].[dbo].[Apple] 
SELECT id, 
       NAME, 
       description, 
       active, 
       plant 
FROM   #tempapple
WHERE  id NOT IN (SELECT id 
                  FROM   [Master].[dbo].[Apple]); 

UPDATE [Plant1].[dbo].[Apple] 
SET    exported = 1 
WHERE  id IN (SELECT id 
              FROM   #tempapple); 

UPDATE [Plant2].[dbo].[Apple] 
SET    exported = 1 
WHERE  id IN (SELECT id 
              FROM   #tempapple); 

DROP TABLE #tempapple; 

Answer 1

I've decided to settle for a mix of my two approaches. The SSIS package remains mostly the same with the logic to iterate through each plant database. Within the loop I now have several Execute SQL Tasks to import data from the various tables. The logic for the import apples task looks something like this:

SELECT * 
INTO   #tempapple 
FROM   (SELECT * 
    FROM   apple 
    WHERE  exported = 0); 

INSERT INTO [Master].[dbo].[apple] 
SELECT id, 
   NAME, 
   description, 
   active, 
   plant 
FROM   #tempapple 
WHERE  id NOT IN (SELECT id 
              FROM   [Master].[dbo].[apple]); 

UPDATE apple 
SET    exported = 1 
WHERE  id IN (SELECT id 
          FROM   #tempapple); 

DROP TABLE #tempapple; 

This allows me to not have reduntant SQL since each task will be executed once per plant database.

Answer 2

I'm writing this directly so you may need to modify it slightly.

declare  @dbname as varchar(100) -- dbname
declare @SQL varchar(max)

declare db_cursor cursor for
[ this is where you insert your code for getting DBnames]

OPEN db_cursor
fetch next from db_cursor into @dbname

while @@fetch_status = 0
BEGIN
    set @SQL = "Select * into #temptable from " + @dbname + ".[dbo].[Apple] where exported = 0
INSERT INTO [Master].[dbo].[Apple] 
SELECT id, 
       NAME, 
       description, 
       active, 
       plant 
FROM   #tempapple
-- no where clause needed

UPDATE " + @dbname + ".[dbo].[Apple] 
SET    exported = 1 
from " + @dbname + ".[dbo].[Apple] a
join #temptable tt  on a.id=tt.id

DROP TABLE #tempapple; "

exec(@sql);
fetch next from db_cursor into @dbname
END

close db_cursor
deallocate db_cursor

Answer 3

I've got to make a few assumptions here:

1. The variable is type 'Object'
2. the foreach loop is on an ADO.Object enumerator setting the db name to a variable
3. insert an expression before the dataflow
4. in the expression set a new variable type string to "Select * from " + [dbname] + ".[schema].[tablename] where exported = 0" 4a. Note that dbname comes the enumerable set in #2
5. In your dataflow, set your source to variable and use that variable in #4.

This should get your data at least loaded.

You have options, for updated the isExported column in the source.