September 7, 2012 at 8:09 am
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]
--90
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.
September 13, 2012 at 12:35 pm
Well, your question is quite detailed. Might i still ask another question. When you say you link src to DEST1 it automaps but when you go to DEST2 it does not automap......do you first unlink src and dest1 because you cant' really map one src to two diff dest at the same time....right? I am assuming you are using a multicast transform between your src and te two destinations....right. So assuming you are(because thats the only way you can go about it unless you have two diff dataflow tasks), maybe dest2 table changed. Go to the dest advanced editor and say refresh cols....maybe that works. If not a good way to check will be to seperately map src to dest2 and see if the prob still persists.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply