SSIS Foreach Loop: map image variable to which SSI

问题:

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?

回答1:

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.

回答2:

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