I'm looping over an ADO recordset using a ForEach Loop Container in SSIS 2008. The recordset contains a column of IMAGE data type, which I mapped to a variable of object type. Then, I have an Execute SQL task for inserting data from the loop, but I can't figure out to which data type of the available ones should I map the previous image column. Any suggestion?
0
votes
Is there a reason you're taking this approach versus using a Data Flow task?
- billinkc
@billinkc Yes, I need to insert some data, get the assigned id and then reuse it in another insert, so I choose this approach. With the data flow task I don't know how to get the inserted id of certain record.
- Oscar
2 Answers
1
votes
What type of connection are you using in your Execute SQL task? If its OLE DB then unfortunately there is no way you could pass the Image type. On the other hand if you can create an ADO .Net connection to your database and use it in your Execute SQL task then you would be able to map the image type to Object type. Screenshots below.
0
votes
Oscar,
If you mean to which SQL Server datatype you should map to, my suggestion would be varbinary(max) for the image column.
See here for a using varbinary instead of image http://msdn.microsoft.com/en-GB/library/ms187993.aspx
See here for an article that might help you using SSIS with Object datatypes. http://www.timmitchell.net/post/2013/03/04/using-object-typed-variables-in-ssis/
Ash
