Search code examples
t-sqlssissql-server-2012sql-server-data-tools

Getting Mapping error in OLE DB Destination when use SQL Command under Data Access Mode

I am working SSIS integration using Visual Studio 2017. I have tested 'Data Access Mode' Table Or View which did work. Now I need to filter data and I am trying to use 'Data Access Mode' SQL Command and getting Mapping issue in OLE DB Destination Editor.

In Destination Data outsource, I use OLE DB Destination and under 'Component Properties' SQL Command I type following script

INSERT INTO User_Filtered
       (UserGUID
       ,IdNum
       ,FirstName
       ,LastName
       ,Email
       ,PostCode)
 VALUES
       (?,?,?,?,?,?)

In mapping getting error

error

error at user dataflow [ole db destination] no column information was returned by the SQL Command

Under the OLD DB Source I typed following script under the SQL Command dataview, which seems fine

 SELECT u.*
 FROM [AnotherServer].[dbo].[Users] AS u
 where [email] IS NOT NULL AND [org] IS NOT NULL

enter image description here

Solution

  • In the OLE DB Destination you only need to use a SELECT statement with the columns that will be inserted into, not an INSERT statement. After this you can map the columns on the Mappings page.