SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with Dynamic Column Mapping


Problem with Dynamic Column Mapping

Author
Message
SwedishOrr
SwedishOrr
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 657
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.
n79799
n79799
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 122
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search