SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb)


SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb)

Author
Message
Magical Horn
Magical Horn
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19683 Visits: 14398
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
Magical Horn
Magical Horn
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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.

Magical Horn
Magical Horn
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19683 Visits: 14398
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
Magical Horn
Magical Horn
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19683 Visits: 14398
You're welcome.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search