August 28, 2012 at 7:51 am
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
September 21, 2012 at 10:52 am
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
September 24, 2012 at 7:15 am
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.
September 24, 2012 at 7:21 am
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
September 24, 2012 at 11:25 am
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
September 25, 2012 at 12:38 am
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
September 25, 2012 at 6:35 am
You're welcome.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 10, 2020 at 8:59 pm
k I believe I am getting close, different error messages now, I uninstalled the Office Access 2010, and re-installed it... rebooted, and made sure it was 32 bit... now I am getting error message attached in a screen shot... I believe its error due to sheet name, not sure if the default sheet name would be sheet1$ or sheet$1 or just sheet1? any ideas? or maybe the error message is barking at something else I am missing? also just a heads up, I am using environment parameters.
also I did notice the last error message where it says "cannot start your application. The workgroup information file is missing or opened exclusively by another user" but I made sure my VS is not opened, and made sure there is no excel opened... I even rebooted my laptop to make sure nothing was holding onto the excel file... so not sure why that message/error part would come up...
I also attached the log file called Logs.txt, maybe I am missing something else?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply