InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 23:05:53 GMT20http://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspxYou're welcome.Tue, 25 Sep 2012 06:35:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspxGood 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.RegardsColinTue, 25 Sep 2012 00:38:34 GMTMagical Hornhttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspxLook 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 [u][url=http://www.microsoft.com/en-us/download/details.aspx?id=13255]distributable driver[/url][/u].Mon, 24 Sep 2012 11:25:51 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspxHi,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?RegardsMon, 24 Sep 2012 07:21:28 GMTMagical Hornhttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Magical Horn (8/28/2012)[/b][hr]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"; andProvider=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, [b]the Initialaisation of the .xlsb spreadsheet fails[/b] (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[/quote]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.[/quote]Mon, 24 Sep 2012 07:15:01 GMTMagical Hornhttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspx[quote][b]Magical Horn (8/28/2012)[/b][hr]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"; andProvider=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, [b]the Initialaisation of the .xlsb spreadsheet fails[/b] (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[/quote]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.Fri, 21 Sep 2012 10:52:30 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1350947-364-1.aspxHello 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"; andProvider=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 RegardsTue, 28 Aug 2012 07:51:40 GMTMagical Horn