Miss-mapped columns in SSIS

Question

I have a simple ETL that is selecting columns from tables in one SQL Server database and inserting them into a table in another SQL Server db. The ODBC Source uses a SQL Query. The ODBC Destination is loading directly to the table. All is working perfectly, no error messages, except that the data is being inserted into the wrong columns in two cases.

The select: SELECT SerialNumber, IPAddress... loads the serial number into the IPAddress field in the destination and the IPAddress into the SerialNumber field.

The mapping tab is correct, as far as mapping SN to SN and IP to IP, but there is something odd about it. The destination table columns are not shown in the order they should be, i.e. the table's first column is SN and the second is IP, but the ODBC Destination mapping tab shows IP first and SN second. So when I map SN to SN I suspect that what I'm actually doing is mapping SN from the query to the second column of the table, which is actually IP (and vice versa). The columns are different length varchars, so I can switch the mapping and it'll work, but it will warn me that the data may not fit in the column due to the size difference. It would also be damn confusing for anyone trying to troubleshoot my code at some future date.

Any thoughts?

ENV:
Visual Studio 2019
SQL Server 2016

Answer 1

@Jeff I felt your pain brought by the mischievous miss-mapped behavior. I spent the whole day to change the source and destination, using the data viewer. When I previewed my source, the columns are ok. But the data viewer showed shifted columns, until I saw this thread.

@Jeffrey Williams provided a few possible solutions. The second one lightened me up, i.e. stale mapping information, to refresh it, use total different query to flush it. When I put back my original query, the data viewer showed the right columns. It works like a magic!

Thank you, both of you, Jeff and Jeffrey, for brighten my day. Nothing couldn't make me happier than solving a problem. But I have to say I don't like that SSIS is so playful. Hate it!

Answer 2

There are no create statements or transformations. This is a simple query and insert. I've attached a screenshot of the mapping tab and the resulting data in the database.

Everything works perfectly - except the data ends up in the wrong columns.

And to show I haven't mis-labeled the data - here's the preview:

Answer 3

Thanks for sharing screen shots.

Your SSMS screen shot in the middle shows very questionable data in the Results pane.
Pump_SN vs. IPAddress. They are flip-flopped.

It seems that the SELECT statement assigns wrong aliases to the columns. That could be the root cause reason for confusion.
Please share that SELECT statement, and its table CREATE statement.

UPDATE
You can try to add SSIS Data Viewer for debugging purposes.
It will help you to troubleshoot data that is flowing through the SSIS pipeline.
Check it out here: use-ssis-data-viewer-dataflow-task

Answer 4

Hi @Jeff ,

I have a simple ETL that is selecting columns from tables in one SQL Server database and inserting them into a table in another SQL Server db.

The ODBC Source uses a SQL Query. The ODBC Destination is loading directly to the table.

1. Could you please share the result of "Select [Col1],[Col2] From YourSourceTable;" ?
2. Could you please share the SQL Query and the Preview Query Results in your ODBC Source ?
   a.If we use the following sql query in ODBC Source , we'll get wrong results:
   SELECT [IPAddress] AS [Pump_SN],[Pump_SN] AS [IPAddress]
   FROM [MyDB].[dbo].[MyTable];
   
   b.If we use the following sql query in ODBC Source , we'll get correct results:
   SELECT [Pump_SN], [IPAddress]
   FROM [MyDB].[dbo].[MyTable1];
   
   

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 5

I have the same issue with my ODBC destination connection to MySQL when send the data from OLE DB SQL Server source, it randomly happens and is sometimes messed up the work since there's no error from the output especially when the columns are the same data type.
My workaround is dropping the source or the destination and recreate so that the meta data is "refreshed" until the order of the column in the DB matches the ones showed in SSIS metadata then I do mapping columns.