March 12, 2012 at 5:05 am
I performed a Load of a SQL Server Staging area from an AS400 DB2 Database.
I did not expect good results but I experimented with Linked query to perform the insert.
As expected performance was horrible and in some cases that load would not finish.
So I used the SSIS Data Flow Task and that worked fine.
If I can't use the Linked Server I can't perform a merge or an OUTER JOIN what combination of task could I use to accomplish in SSIS?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 12, 2012 at 7:26 am
Stage everything to SQL Server first, then do your ETL work.
March 12, 2012 at 8:15 am
You could use the Lookup component to determine of a row is an insert or an update.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 12, 2012 at 11:49 am
What do you mean by this:
If I can't use the Linked Server I can't perform a merge or an OUTER JOIN what combination of task could I use to accomplish in SSIS?
We performs loads from DB2 into SQL Server every night across multiple systems. Our standard method is to us an SP to pull the data in the way that we want it - including joins to SQL Server tables. While this process is certainly no speed demon it gets the job done in a reasonable amount of time.
Basic pseudocode:
INSERT INTO [table1]
(
col1
, col2
, col3
SELECT oq.c1
, oq.c2
, oq.c3
FROM OPENQUERY(DW_DB,
'
SELECTDISTINCT
c1
,c2
,c3
FROM dw.sourcetable
') oq
LEFT OUTER JOIN SSDB.dbo.table1 AS t1
ON oq.c1 = t1.col1
WHERE t1.col1 IS NULL
We generally do the above to a work / load table then load the data into the main DB/table. Or you can just do the select part within the source of a data flow component.
If you can't touch your SQL Server DB/table for the length of time that it takes, you can always do something like what we do for some of our no down time DB's - create a backup/load copy of the DB, do all your work/heavy lifting on the copy DB, then merge the copy back into the main DB (or in some of our cases since the DB is lookup only we just drop the original DB and make the copy the new "original" - transactions are stored in a separate DB entirely.)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply