January 23, 2009 at 12:58 pm
One thing I forgot to mention. I can run the full query with the insert statement against the SQL Server 2005 database from Mgmt Studio without any problem. It is only within my SSIS project that I have this problem.
January 23, 2009 at 5:16 pm
You mention an OLE Source but not an OLE Destination.
My guess is you are reading data from one table and want to insert to another.
Forgive me if I'm being to basic but start by adding the needed OLE connection managers. If reading and writing to the same database you only need 1 connection manager.
Add a data flow task, double click it to open. Add an OLE source, specify the connection manager you created and either code a select statement or simply pick a table from the list to dump the whole thing.
Add an OLE destination. Now click on the OLE source so the green arrow appears, drag and connect to the OLE destination. Open the OLE Destination and select the connection manager, then pick your destination table. Go to the mappings and map the inbound columns to the destination columns.
Assuming the data types all match up this should run no problem. If you need to convert types add a Data Conversion task or Derived Column task.
January 26, 2009 at 12:20 pm
In my Data Flow task, I've got my OLE DB Source going into a Data Conversion task and then a Flat File destination. The output of the OLE DB Source is from a select of the table after inserts are performed within the query. The problem exists with the OLE DB Source query, which includes an insert statement.
I'm wondering if, for SQL Server 2005, I have to separate out the while loop and insert statement from the main select. The main select could be in the OLE DB Source, which would pass to a loop container. The problem being that there are no loop containers in the Data Flow task like there are in the Control Flow section.
This really should be a no brainer. Using a cursor to loop through a recordset and perform a task such as an insert or update in the Data Flow must be something done by SSIS programmers all the time. Does anybody have an example of how they perform such a task?
January 27, 2009 at 2:32 pm
All is well in the universe again 🙂
I got it to work by placing my full query, insert and all, in a stored procedure and executing that from the OLE DB Source.
Case closed. Thanks for your input, tvanharp.
January 28, 2009 at 10:14 am
I'm glad you got it working, looks like I was a bit off base on that one.
Just to clarify, you have a data flow with an OLE source, set to use the SQL Command and in there you have a call to the sproc, such as Exec db.dbo.mySproc and that sproc is returning a record set which is the output of your OLE Source?
January 28, 2009 at 10:29 am
You got it!
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply