shannonjk (12/7/2012)
Jeff Moden (12/7/2012)
I guess my question would be, why do you need to use SSIS for this task? Why not just grab the data using OPENQUERY from a linked server with the correct criteria and do a fairly typical and simple "upsert" from the staging table?From that logic, why even bother developing SSIS in the first place?
That IS where I was going with this. 🙂
I'm not trying to have an argument with you here, Shannon... I just want to let you know what is fairly easily possible.
I can run queries against the source using a linked server but it is much slower than using SSIS (naturally, since SSIS has engines designed for ETL) so using this method would just make things slower.
While I agree that writing a query directly against an AS400 Linked Server will be slower, I've found that OPENQUERY runs quite fast in comparison. I've not done a deep dive on why but, since I can use AS400 locking hints in the query, I believe it's because it throws the query "over the wall" and actually executes on the AS400 itself and simply passed the result set back.
Also, according to Microsoft Library, and OPENQUERY cannot accept variables for it's argument which would defeat the purpose of this.
Correct. It can't... directly. Without much complication, though, a bit of dynamic SQL goes a long way here. I do it all the time.
Last but not least I am using the parallelism ability of SSIS to load multiple concurrent streams of data. Using SQL scripts kind of defeats this purpose.
Not really. Using T-SQL, you can tell SQL Server to asyncronusly execute multiple jobs in parallel and just check for job completion. Works a treat for parallel loads.
I am not sure why no one is really answering my question. I will go back to google and see if I can find something.
I can't speak for anyone else but my reasons are all outlined above. I don't use SSIS for anything so I can't tell you how to use it.
--Jeff Moden
Change is inevitable... Change for the better is not.