Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb) Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:55 AM
Points: 17, Visits: 179
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.xlsb;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
Post #1350947
Posted Friday, September 21, 2012 10:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1362855
Posted Monday, September 24, 2012 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:55 AM
Points: 17, Visits: 179
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.
Post #1363473
Posted Monday, September 24, 2012 7:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:55 AM
Points: 17, Visits: 179
Hi,
And thanks for the reply.

The two files are both pre-existing. They exist as "empty" templates in another folder with just the column headers - these are copied into th eoutput folder and then these are the spreadsheets used to output to. I've used this method many times on .xlsx spreadsheets withotu any problems at all, and as I say, the package works fine when run from Visual Studio.
I've also tried using all combinations of 'Delay Validation' and 'Validate External Metadata', but the problem still persists.
I'm now wondering if there is a specific driver/provider for .xlsb spreadsheets that may be missing from the server. Anyone any ideas how I find this out?

Regards
Post #1363476
Posted Monday, September 24, 2012 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
Look for ACEOLEDB.DLL on your machine. It should be in an Office14 folder in one location or another depending on whether you have Office installed or whether you installed the distributable driver.



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363647
Posted Tuesday, September 25, 2012 12:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:55 AM
Points: 17, Visits: 179
Good Morning,

I installed the "Microsoft Access Database Engine 2010 Redistributable" 32bit software on the SQL Server server and my job now completes normally with the correct results.

Now I've just got to make sure the software is also on our UAT & LIVE SQL Servers.

Many thanks for your help.

Regards
Colin
Post #1363810
Posted Tuesday, September 25, 2012 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
You're welcome.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse