January 18, 2006 at 4:02 pm
I'm creating an SSIS package that imports from data from an OLE DB source that isn't a SQL Server, and doesn't support transactions of any sort, and inserting it into a table on my SQL Server. Currently, I'm using a Data Flow task to import the data, and it's not in a transaction.
Here's my problem - I want the import to happen within a transaction, but when I change the transaction option on the container from "optional" to "required" and execute the package, I get an error message that the DB connection (to the source OLE DB, which doesn't support transactions) has failed, and it fails the task. Since I'm only reading from that DB, I don't need it in the transaction - I just want a TRUNCATE statement (SQL Statement task before the import) and the import itself to be in the transaction, without grabbing the source DB and including it. Is there any way to do that?
Ryan
January 20, 2006 at 11:26 am
I would use the 2K method and use a Stage table. Something you can control. that way you can do all your validation locally
June 28, 2006 at 9:36 am
I've found a workaround for this, so I thought I'd post it here. It's described here:
http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx
Though it's not a real solution to the problem, it works fine for the problem I'm having. Also, I'm not required to land the data in a staging table in order to avoid the transaction problem.
I'm hoping Microsoft can offer us a "Destination Only" transaction option in a later service pack - I know I'd find that useful.
Ryan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply