SSIS Data Flow Task From SS2000 to SS2012

  • I am using Visual Studio 2008 version 9.0.30729.1 SP (BIDS) to create a package that will copy data from a SS2000 database table to a SS2012 database table. It works great when I run it in BIDS from my Windows XP machine.

    When I schedule it to run as a SQL Server Agent job on the SS2012 server, it failed with the following error message.

    Message

    Executed as user: NT Service\SQLAgent$SQLPROD. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:32:36 AM Error: 2013-05-16 11:32:37.44 Code: 0xC0209303 Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: The requested OLE DB provider SQLNCLI10.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2013-05-16 11:32:37.44 Code: 0xC020F42A Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: Consider changing the PROVIDER in the connection string to SQLNCLI11 or visit http://www.microsoft.com/downloads to find and install support for SQLNCLI10.1. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "sqlprod.AOC_Applications.sa" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-05-16 11:32:37.44 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:32:36 AM Finished: 11:32:37 AM Elapsed: 0.764 seconds. The package execution failed. The step failed.

    After wading through all that text, I focused in on the following:

    Description: Consider changing the PROVIDER in the connection string to SQLNCLI11

    Since my connection string is provided through a configuration file, I went in and changed SQLNCLI10.1 to SQLNCLI11 as suggested. And great news! I got a different error.

    Message

    Executed as user: NT Service\SQLAgent$SQLPROD. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:30:49 PM Error: 2013-05-16 12:30:50.03 Code: 0xC020801E Source: SendEmail Log provider "{31503914-01FB-4A31-880A-C914E7B2A6E1}" Description: The connection manager "sqlprod.AOC_Applications.sa" is an incorrect type. The type required is "FILE". The type available to the component is "OLEDB". End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0202009 Source: SendEmail Connection manager "sqlprod.AOC_Applications.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Client unable to establish connection". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.". End Error Error: 2013-05-16 12:30:50.06 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "sqlprod.AOC_Applications.sa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-05-16 12:30:50.06 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:30:49 PM Finished: 12:30:50 PM Elapsed: 0.608 seconds. The package execution failed. The step failed.

    Out of which I extracted the following:

    Description: "SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.".

    So I googled that helpful tidbit, and found this link:

    In there, the author said the following: All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider.

    From this, I'm guessing that I have to use a different SSIS connection manager.

    So I have some questions. First, is this a surmountable issue? Second, have I diagnosed the fix correctly? And if so, what connection manager type should I use?

    Thanks, and my apologies if I'm in the wrong forum.

    Mattie

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply