Reputation: 563
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;
Upvotes: 0
Views: 215
Reputation: 563
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.
Upvotes: 0
Reputation: 5594
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
Upvotes: 1
Reputation: 5594
I've got to make a few assumptions here:
This should get your data at least loaded.
You have options, for updated the isExported column in the source.
Upvotes: 1