Bizarre error -- SSIS package configurations

Discussion in 'microsoft.public.sqlserver.dts' started by Neeraj, Jun 7, 2010.

  1. Neeraj

    Neeraj Guest

    Hi,

    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.
     
    Neeraj, Jun 7, 2010
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.