I am working on a Data Flow Task within an SSIS package. I've created three OLE DB connections, one source and two destinations (called dest1 and dest2). Each of the destination connections use a different database on the same SQL instance. My problem is with dynamically mapping columns from the source to the destinations. In the source, my data access mode is a SQL command and my query goes something along the lines of "SELECT sourceCol1 AS destCol1, sourceCol2 AS destCol2 FROM ExampleTable." destCol1 and destCol2 exist in the target table in both dest1 and dest2. Furthermore, the metadata contained in sys.columns is identical for these two columns, save for column_id and object_id. The collation is the same, the name is the same (including capitalization). When I create the link between source and destination inside the dataflow task to dest1, all columns are mapped correctly. When I create that link between source and dest2, I'm left with destCol2 not mapped, but it's available in the Input Column dropdown of the Mapping tab in the SQL Destination Editor. I would like it to by auto-mapped as well.
I ran profiler while I went to the mapping tab and the only thing I see happening are the following actions:
exec [sys].sp_bcp_dbcmptlevel [DatabaseName]
set fmtonly on
select * from [dbo].[TableName]
set fmtonly off
exec [sys].sp_tablecollations_90 '[DatabaseName].[dbo].[TableName]'
When I run the SQL above, I get the same output for the destCol2 between dest1 and dest2 databases.
This problem goes away if I drop and recreate the column in dest2. I would like to understand what is causing this inability to completely map columns from input to destination. I am unable to reproduce this situation on my own. I cannot post the package or databases, as they contain sensitive client data.
Any insight is greatly appreciated, as I'm quite perplexed.