Home Forums Data Warehousing Integration Services SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb) RE: SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb)

  • September 24, 2012 at 7:15 am

    #1541390

    opc.three (9/21/2012)

    Magical Horn (8/28/2012)

    Hello All,

    I'm just about trearing my hair out with a Connection problem when I'm writing to an Excel Binary Workfile. This is the situation:

    I have two Excel 2010 Spreadsheets (RoadTypes.xlsx and RoadTypes.xlsb). They are identical in format (same colums etc.). I have an SSIS Package in Visual Studio 2008 which simply does an OELDB Input Data Flow into a Multicast, which then Outputs to both of the spreadsheets.

    This works perfectly. The Connections are properly defined with the correct strings:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\file.entplc.netmig1\Compswap\DWTEST\DimRoadTypes.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES"; and

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\file.entplc.netmig1\Compswap\DWTEST\DimRoadTypes.xlsx;Extended Properties="EXCEL 12.0;HDR=YES";

    for the appropriate package.

    However, when I deploy the package to my IS Server and run it as SQL Job, the Initialaisation of the .xlsb spreadsheet fails (the .xlsx spreadheet is OK). The package log shows the following errors:

    ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\file.entplc.netmig1\Compswap\DWTEST\DimRoadTypes.xlsb;Extended Properties="EXCEL 12.0;HDR=YES";,...)'.

    ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.

    ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.

    ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.

    SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RoadTypes_xlsb" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    component "DimRoadTpye_xlsb" (67) failed validation and returned error code 0xC020801C.

    The SQL Agent Job is running on the same server that Visual Studio connects to when I run it from there.

    The SQL Agent job is being run under the service account, but it has all of the correct privileges to access both of the spreadsheets. It is being run using teh 32bit runtime option on the Job Step.

    If I remove the .xlsb output from the multicast (so it's only outputting to the .xlsx spreadsheet) and then deploy the package back to the server, it works OK.

    I've created this single job step a number of times and I've also started and stopped the SQL Engine and SQL Agent services on the server, but to no avail. Almost everything I've read out on the Internet tells me this should work.

    Does anyone have any ideas?

    Best Regards

    Need more info. Are the two files pre-existing in the destination locations? The Excel driver will not create new Workbook files and Worksheets for us on the fly the same way a database driver will not create a database and table for use on the fly. Depending on what else you're doing in the package those files may need to already exist in the structure you need. Or are you creating them on the fly in the package with an Execute SQL Task? If so then check your 'Delay Validation' and 'Valdiate External Metadata' settings.