none
SSIS export to Excel 2016 fails RRS feed

  • Question

  • I have two SSIS packages that are exactly the same except for the data being exported. Both run manually in Visual Studio 2017\SSDT 15.1.61901.24070. One of them runs just fine in a SQL 2014 Agent job, the other does not. Both are running 32-bit, I have the ACE 16.0 ODBC 32 bit engine installed, permissions the same, same proxy account. delay validation set to true. I turned on verbose logging, the last message in the one that fails is : DFT_SalesInventoryCosts: Data flow engine will call a component method.  The one that runs has a small output data set (about 300 rows), the one that fails has 204,000 rows. But - I changed the stored procedure to only pass the first 1000 records, and that failed too, so I don't think it is the size of the output. ANY ideas are appreciated!

    Log:

    DFT_SalesInventoryCosts:Data flow engine will call a component method.
    DFT_SalesInventoryCosts:Data flow engine has finished a call to a component method.
    ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.
    ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.
    ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.
    ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
    ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
    ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\spdcsvxsqlbxp01\w\ssis\anaplan_extracts\ExcelWorkFiles\Costs_work.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";,...)'.
    DFT_SalesInventoryCosts:Data flow engine will call a component method.
    DFT_SalesInventoryCosts:Progress: Validating - 25% complete.
    DFT_SalesInventoryCosts:Data flow engine has finished a call to a component method.
    DFT_SalesInventoryCosts:Data flow engine will call a component method.
    The Costs_csv spent 0 milliseconds in Validate.
    DFT_SalesInventoryCosts:Data flow engine has finished a call to a component method.
    DFT_SalesInventoryCosts:Data flow engine will call a component method.
    DFT_SalesInventoryCosts:Data flow engine has finished a call to a component method.
    DFT_SalesInventoryCosts:Data flow engine will call a component method.
    DFT_SalesInventoryCosts:Progress: Validating - 0% complete.
    DFT_SalesInventoryCosts:Information: Validation phase is beginning.
    DFT_SalesInventoryCosts:Validation has started.
    Anaplan_Costs:Validation has started.

    Lori

    Monday, August 19, 2019 9:58 PM

All replies

  • Hi Lori,

    So you are using Microsoft ACE OLEDB provider 16.0 32-bit to generate two Excel files.

    Unfortunately, the log entries you shared show no error or warning message.

    It looks like that the Excel file should be created at the following location:

    \\spdcsvxsqlbxp01\w\ssis\anaplan_extracts\ExcelWorkFiles\Costs_work.xlsx

    Question: what about the successful SSIS package, does it generate smaller Excel file in the same directory just with a different name?


    Monday, August 19, 2019 11:06 PM
  • Hi Lori,

    1. Could you change some related settings in the successful package so that it outputs the Excel file which has failed in another package?
    2. SSIS can't append to Excel files unfortunately. You could however alter the SSIS package and make the output-filename date depended to something like the following for example: OutputFilename_yyyymmdd_hhmmss.xlsx. Please refer to SSIS Tutorial Part 17-Create Excel File with DateTime On Each Package Execution in SSIS Package.

    Also, in order to solve your issue better, could you share us some error messages?

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, August 20, 2019 2:27 AM
  • Hi Lori,

    You should add logging to your package and capture the exact error and component that fails. Post the findings here.

    To troubleshoot, try to dump the data to a text file.

    It would be helpful if you could share the package design and image so anyone could reproduce.


    Arthur

    MyBlog


    Twitter

    Tuesday, August 20, 2019 1:41 PM
    Moderator
  • Thanks for the responses.

    Yes, both SSIS packages should be writing to the same directory, one does, the other does not. Actually, I have 75 SSIS packages all the same, just with different stored procedures as DFT sources, and different .csv and .xlsx files for output. 65 of them work just fine, all of them the smaller result sets. Only the 10 that produce large datasets are failing.

    Yes, I know about the issues with Excel work files, step one is to copy a template to my work directory, write to that, and then a following FST to copy and rename the work file with the date stamp in the file name. Again, this works just fine for 65 of the packages. The FST to copy the template to the work file works in all of them. It is failing in the write to the excel file in the DFT. If I remove that output and write just the .csv, that works just fine for all of the packages.

    I understand that the 'verbose' logging that I attached in my question isn't helpful. That is why I posted for help here. I added logging to the package, that worked just fine in the interactive mode via Visual Studio.  Here is the pertinent text:

    OnPipelinePreComponentCall,SPVMIT08,CORP\LHOperator,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,0x,Data flow engine will call a component method. : 26 : Costs_xlsx : AcquireConnections
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\spdcsvxsqlbxp01\w\ssis\anaplan_extracts\ExcelWorkFiles\Costs_work.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";,...)'.
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.
    Diagnostic,SPVMIT08,CORP\LHOperator,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,,ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.
    OnPipelinePostComponentCall,SPVMIT08,CORP\LHOperator,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,0x,Data flow engine has finished a call to a component method. : 26 : Costs_xlsx : AcquireConnections : 132107966217848555 : 132107966218318631
    OnPipelinePreComponentCall,SPVMIT08,CORP\LHOperator,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,0x,Data flow engine will call a component method. : 26 : Costs_xlsx : Validate
    OnPipelinePostComponentCall,SPVMIT08,CORP\LHOperator,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,0x,Data flow engine has finished a call to a component method. : 26 : Costs_xlsx : Validate : 132107966218348587 : 132107966218368632
    User:PipelineComponentTime,SPVMIT08,CORP\LHOperator,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{F2EE1326-FF82-4705-9A7C-A197B7470DFD},8/20/2019 10:43:41 AM,8/20/2019 10:43:41 AM,0,0x,The Costs_xlsx spent 2 milliseconds in Validate.

    You can see that the task spent 2 millisecods in Validate.

    here is the last entries in the log from running via SQL Agent:

    OnPipelinePreComponentCall,SPDCSVXMSQLXT03,CORP\ssisql,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,0x,Data flow engine will call a component method. : 26 : Costs_xlsx : AcquireConnections
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\spdcsvxsqlbxp01\w\ssis\anaplan_extracts\ExcelWorkFiles\Costs_work.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";,...)'.
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.
    Diagnostic,SPDCSVXMSQLXT03,CORP\ssisql,Costs_xlsx,{77FF889A-31B0-4F36-B752-3606947614E5},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,,ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.
    OnPipelinePostComponentCall,SPDCSVXMSQLXT03,CORP\ssisql,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,0x,Data flow engine has finished a call to a component method. : 26 : Costs_xlsx : AcquireConnections : 132107971539183357 : 132107971539964713
    OnPipelinePreComponentCall,SPDCSVXMSQLXT03,CORP\ssisql,DFT_SalesInventoryCosts,{42733509-02A3-47BA-B4D2-9AD0ED2A8DD0},{3CB4FB89-021B-4B3E-9473-6B38F0DD33F6},8/20/2019 10:52:33 AM,8/20/2019 10:52:33 AM,0,0x,Data flow engine will call a component method. : 26 : Costs_xlsx : Validate

    And that is the end of the log file. No errors. Just like is shown in the SSISDB verbose logging. SSIS appears to be calling excel to validate, but never receives back the 'validated' message, like it gets when running via Visual Studio.

    FWIW - the DFT tasks are pretty simple, stored procedure source, multicast into .csv file and .xlsx file.

    

    So....where do I go next to get this to work for larger excel files?

    Again, thank you for responding, I hope someone will have some insight on what to try next.

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Tuesday, August 20, 2019 6:13 PM
  • Lori,

    instead of using the remote share, can you write to the Excel files locally (where the SSIS is)?


    Arthur

    MyBlog


    Twitter

    Tuesday, August 20, 2019 7:14 PM
    Moderator
  • Hi Lori,

    • There is no reason to run 32-bit software on the 64-bit servers.
      I would recommend to install and use 64-bit Microsoft ACE OLEDB provider due to large volume of your data.
    • You can easily check what 64-bit OLEDB providers are installed by issuing the following command in SSMS: EXEC master.dbo.sp_MSset_oledb_prop;
    • Download link:
      Microsoft Access Database Engine 2016 Redistributable
      https://www.microsoft.com/en-us/download/details.aspx?id=54920
      It could be installed with /quiet mode parameter.


    Tuesday, August 20, 2019 7:23 PM
  • Yes, the ssis that works writes to the same directory using the same proxy account. Just a smaller file.

    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:13 PM
  • Hi Lori,

    Please don't miss my reply with the bulleted list.

    The overall goal is to start using 64-bit ACE OLEDB provider.

    Thursday, August 22, 2019 9:19 PM
  • Hi - I tried uninstalling the 32-bit driver, installing the 64-bit driver, rebooting the server, rebuilding the solution with 'Run as 64-bit' = True, deploying that, unchecking the 'Run 32 bit' box on the job, and --no dice. Got driver is not registered error. So, since I know that the 32-bit driver works for more than half my jobs, I uninstalled 64-bit driver, reinstalled the 32-bit driver, rebooted the server, changed my SSIS solution back to run as 32-bit, etc etc, restested one of the jobs that used to work, and that is back to working. So, I'm leaving it at the 32-bit version, because at least some of my packages run that way. Thanks Lori

    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:39 PM
  • Tried this, no dice, same error. 

    Thanks,

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:40 PM
  • I changed the stored procedure that is the source for the failing DFT to only return the top 1000 rows, thinking that the problem is the larger file. But, no dice, got the same non-error failure. 

    The jobs that run and the jobs that fail are all pointing to the same directory, running under the same proxy account. Only difference in the packages is the stored procedure source and the excel file output.

    Obviously, based on the above test, it isn't the size of the output file, although that was one of my early thoughts on why it was failing and tested that before I even submitted the question here.

    Thanks,

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:43 PM
  • I copied the SSIS package that is successful, and changed just the source and output in the DFT, and that still failed.

    I played around with row counts, buffer sizes, etc, that made no difference.

    and yes, I know about using a template, copy that to a work file, then rename the work file with the datetime appended to it. 

    Thank you,

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:45 PM
  • Hi Lori,

    1. "...It could be installed with /quiet mode parameter..."
      That was the crucial line to allow you to install and have both 32-bit and 64-bit ACE providers in parallel.
    2. I also had scenarios where ACE 12.0 was working and both ACE 15.0 and 16.0 were failing.
      So this is another avenue for you to explore.
    3. If you could share the outcome of the following command in SSMS:
      SSMS: EXEC master.dbo.sp_MSset_oledb_prop;
    4. As I recall, you tried SELECT TOP(1000). Could you try TOP(10) and TOP(100)

    P.S. You could find me on LinkedIn and Skype.



    Thursday, August 22, 2019 9:47 PM
  • I added logging as you suggested, see below - there aren't any errors being returned from the process. It gets as far as 'validating' and then....nothing.

    I already use a multicast task to write the same output to both a .csv file and the .xlsx work file. If I remove the excel output from that, the job runs just fine. Unfortunately, my requirements are to produce BOTH a .csv and .xlsx file. 

    I don't think I'm running into the 1,048,567 row limit for excel 2016, because I ran into that particular problem in another SSIS package, the failure was not the same as what I am getting here.

    Thanks

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Thursday, August 22, 2019 9:48 PM
  • Hi Lori,

    Please don't miss the numbered list ##1-4 in my previous reply.

    There is one more method to troubleshoot your scenario in SSMS. 

    This method requires as a prerequisite to create upfront an empty Excel file and fill in its header line:

    emp_id, fname

    I used Microsoft's pubs DB. And after that in SSMS issued the following command:

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=e:\temp\employee.xlsx;HDR=YES;',
    'SELECT emp_id, fname FROM [Sheet1$]')
    SELECT emp_id, fname
    FROM [pubs].[dbo].[employee];

    Output Excel file:


    Thursday, August 22, 2019 10:03 PM
  • Thanks for more info Yitzhak. I tried multiple times on different machines to do the /quiet install, kept getting 'not a valid program executable file'. I don't have unlimited time to debug this thing, that's why I just went back to the 32-bit ODBC, because at least that works 80% of the time for me.

    I already am creating an empty 'work' .xlsx file with just the column headers for the DFT to write to. I've run into that particular issue before.

    Right now, I suspect that the SSIS is timing out while trying to write to the .xlsx file. So I'm going to start researching that possibility. I've already tried changing the DefaultBufferMaxRows and DefaultBufferSize parameters on the DFT properties, that didn't make a difference. But I"m thinking that plays into the issue with these larger file exports.

    Thanks again everyone.

    Lori


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Friday, August 23, 2019 6:34 PM
  • Also, here's the result of the SQL command:

    provider_name allow_in_process disallow_adhoc_access dynamic_parameters index_as_access_path level_zero_only nested_queries non_transacted_updates sql_server_like
    SQLOLEDB 0 0 0 0 0 0 0 0
    SQLNCLI11 1 0 0 0 0 0 0 0
    ADsDSOObject 1 0 0 0 0 0 0 0
    Search.CollatorDSO 0 0 0 0 0 0 0 0
    MSDASQL 1 0 0 0 0 0 0 0
    MSDAOSP 0 0 0 0 0 0 0 0
    MSOLAP 1 0 0 0 0 0 0 0


    Lori Halsey Sr Database Administrator Roseburg Forest Products

    Friday, August 23, 2019 6:36 PM
  • Hi Lori,

    >> "...I tried multiple times on different machines to do the /quiet install, kept getting 'not a valid program executable file'..."

    It seems that the executable file you are trying to install is corrupted. I would suggest to download it again. Also, you can  try the /passive switch instead of /quiet.

    Please find below what I see on my machine. I have 64-bit ACE OLEDB v.12.0 and it allows me to deal with Excel files in SSMS before I go to the SSIS. It allows both operations:

    • To query Excel files from the file system via SELECT … as a virtual DB table.
    • Generate Excel files from SELECT .. statement.


    ACE installation switches:



    Friday, August 23, 2019 7:24 PM