SSIS Troubleshooting

SSIS is case-sensitive and will remember the old column name even if it no longer exists. So say you have a column “CHANGE” and map it through. Then next time you use “Change” then SSIS will try to map “CHANGE” and throw errors. Try changing the column name to something completely different and then map through.

Advertisements

SQL Agent Job – SSIS Integration package which uses VFPOLEDB and SQL 2012 fails

Various error messages received including:

Description: ADO NET Source has failed to acquire the connection DataReaderSrc with the following error message: “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”

Solution:

  1. Create a SQL Credential
    1. SSMS > Security > Credentials
  2. Create a SQL Server Agent Proxy
    1. SSMS > SQL Server Agent > Proxies > SSIS Package Execution > New Proxy
  3. Return to job step in question > Run as > new proxy
  4. Execution options > Use 32-bit tick

Using SSIS to import data from SQL Server on premises to SQL Azure

This assumes prior knowledge of SSIS, but just hi-lights the difference

  1. SSIS
  2. Data Flow Task
  3. OLE DB Source
    1. As normal
  4. ADO Net Destination
      1. .Net Providers\SqlClient Data Provider
      2. Login

SSIS fails 64-bit dts_e_cannotacquireconnectionfromconnectionmanager acquireconnection method call to the connection manager “sourceconnectionoledb” failed

Occured trying to connect on 64-bit computer to MSAccess
ssis error code dts_e_cannotacquireconnectionfromconnectionmanager.  the acquireconnection method call to the connection manager “sourceconnectionoledb” failed with error code 0xc0209303.  there may be error messages posted before this with more information on why the acquireconnection method call failed.
Answer:
Got to SSIS Project property page > Configuration Properties > Debugging > Run64BitRuntime change from True to False
Superhero answer here: