SSIS Troubleshooting

07-May-1313 Leave a comment

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.

Filed under SQL SSIS

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

13-Dec-1212 Leave a comment

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

Filed under SQL Configuration, SQL Server, SQL SSIS, Troubleshooting Tagged with ,

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

20-Feb-1212 Leave a comment

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

Filed under SQL Azure, SQL SSIS

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

04-Aug-1010 Leave a comment

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:

Filed under SQL Server, SQL SSIS