Bizarre error -- SSIS package configurations
Neeraj
I am having a strange issue while setting up package configurations. First,
I have set up an environment variable with the below value:
Data Source =
MACHINE_NAME;InitialCatalog=Database_Name;Provider=SQLNCLI10.1;IntegratedSecurity=SSPI;
Second, I created an OLE DB connection 'Config' as a pointer to SQL Server
configuration using NT authentication.
Third, I created a SQL Server configuration and stored all the values in the
table SSISConfigurations. I am using the connection 'config' for this SQL
Server package configuration.
Fourth, I copied the SSISConfiguration table to QA environment and changed
the respective connection strings to point the new QA machine
Now the issue. I built the solution and deployed the SSIS packages using
deployment manifest file. I get the below error. I did the reverse engineer
process and opened the package in QA machine. I find that the connection
manager 'Config' is getting changed to SQL Server authentication option. Any
help regarding this would be greatly appreciated.
Message
Executed as user: Corp\sqlserver. Microsoft (R) SQL Server Execute Package
Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 2:32:39 PM Error: 2010-06-07
14:32:41.90 Code: 0xC0202009 Source: ProdDataImport Connection
manager "Config" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE
DB error has occurred. Error code: 0x80004005. An OLE DB record is
available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult:
0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name
not found and no default driver specified". End Error Error: 2010-06-07
14:32:41.94 Code: 0xC0202009 Source: ProdDataImport Connection
manager "Config" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE
DB error has occurred. Error code: 0x80004005. An OLE DB record is
available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult:
0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name
not found and no default driver specified". End Error Error: 2010-06-07
14:32:42.67 Code: 0xC0047062 Source: Import affiliate table Read
affiliate from ProdDataImport [3568] Description:
System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver
Manager] The specified DSN contains an architecture mismatch between the
Driver and Application at
System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode
retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle) at
System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection,
OdbcConnectionString connectionOptions) at
System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject) at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup) at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) at
System.Data.Odbc.OdbcConnection.Open() at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String
assemblyQualifiedName, String connStr, Object transaction) at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100
wrapper, Object transaction) End Error Error: 2010-06-07 14:32:42.68
Code: 0xC0047017 Source: Import affiliate table SSIS.Pipeline
Description: component "Read affiliate from productionDB" (3568) failed
validation and returned error code 0x80131937. End Error Error: 2010-06-07
14:32:42.68 Code: 0xC004700C Source: Import affiliate table
SSIS.Pipeline Description: One or more component failed validation. End
Error Error: 2010-06-07 14:32:42.68 Code: 0xC0024107 Source: Import
affiliate table Description: There were errors during task validation.
End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:32:39 PM Finished: 2:32:42 PM Elapsed: 3.401 seconds. The
package execution failed. The step failed.