January 23, 2009 at 12:41 pm
I created a package in SSIS that uses a SQL Command in an OLE DB Source task to extract data and then insert values into a table. This was working well when I was connecting a SQL Server 2000 DB. We just upgraded to SQL Server 2005 and the OLE DB Source task will no longer work. I traced the problem to the Insert statement in my SQL Command statement.
Basically, my query uses a cursor to run through a recordset returned from a select statement. In the While loop, if a certain value is null, I issue the following insert statement:
Insert into BP_TempObjects.dbo.BP_TimesheetImportList (NAME, ID, Description,
Status, Permissions, Notes, Client)
Values(@Name, Cast(@ID_Ctr as varchar(80)), @Description, @status, @Permissions, @Notes, @Client)
I had no problem executing this statement to a SQL Server 2000 db, but now that it's on a SQL Server 2005 DB it has a problem. When I open up the Advanced Editor for the OLE DB Source object, the errors are pretty bogus. In essence, if it doesn't like the query it won't resolve the columns. So the Column Mapping tab does not have column in it. If I remove the insert statement, it resolves the columns and all is good. I tried using a simple insert to a table on my main DB rather than BP_TempObject, but any insert statement seems to cause a problem.
Does anyone know what in SQL Server 2005 would cause the insert statement to create this problem? Thanks
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply