Deploying SSIS when using Excel Connections

Question

This is not so much a question as an Answer, I have had an issue now for the past week that I have been struggling with and could not find the correct answer to as there are a lot of issues for the same error code 0xC0202009 and 0x80004005.

The SSIS package was developed in Visual Studio 2019, so the Provider had to be 32-bit (accessdatabaseengine.exe) for debugging and running the package in VS environment. Then for testing I used a SQL Server 2019 and from get go I had issues but relatively easy to fix as SQL Server 2019 is 64-bit by default so checking run in 32-bit solved the issue:

And packet ran as expected, so just for good measure I installed the 64-bit provider (accessdatabaseengine_X64.exe) and tested running in 64-bit mode and work as expected.
So thought now ready to deploy to production, however customer failed to inform me they run a SQL Server 2017 so had to recompile the project for SSIS 2017.

So started of installing the Provider and as it was production, I thought I only need the 64-bit. I deployed the package using the Integration Service Deployment Wizard and proceeded setting up the SQL Job that will be running the package on a schedule.
In the Connection Managers I updated to the correct Data Sources however the Excel connection gets its path during runtime so though no need to change this as it will change during runtime. But as soon as I ran the package it was complaining on the Excel Connection that it cannot validate it (Yes I set the DelayValidation property on this connection), as the Variable that holds the path gets changed via an Experssion Task it seems SSIS when deployed to a SQL Server validates the connection before running any other Task so it never gets changed before Validation (This in my mind is a bug or design flaw, if DelayValidation is set it should wait with Validation until it’s time to run that Task using the connection).

So the fix seemed pretty easy we change the path in the connection string to point to the TEMPLETE Excel (as the ExcelFilePath property of the Excel Connection was set to an Expression it is not available in Connection Managers) that gets copied during runtime so that the connection has a file to look at during Validation.

However once touching the connection string the SQL Sob Step starts complaining about that a property is not valid (as it was the only one changed it was not hard to figure out which one it was referring to). After some googling I find out from a post (sorry do not remember by who, but kudos to you) that I have to put the “Extended Properties” value in “” (double quotes), so I did that and voila SQL Job Step was again happy.

But this is where the insanity starts and the only way I can explain it is a “Catch 22 Bug”, after reading forum post for the errors codes received and finding multiple reasons but most pointing to the Provider and that SSIS cannot find it, I tried
• uninstalling the Providers
• installing only 32-bit
• installing only 64-bit
• all kinds of property combinations in the Execl connectionstring

Probably read ever single forum post regarding Excel Provider issues since 2008 and nothing was working, so I went back to my SQL Server 2019 where I got it to work and damaged by all the connectionstring fiddeling I opened up the connection string property and made no changes and again SQL Job Step complains a property is not valid???

So I go in again and change the “” for the Extended Properties and close the dialog run it and to my surprise the Package fails to execute with same strange provider error with no Description as on the production SQL Server 2017.

So now I’m starting to think the requirement for the “” is screwing up the connectionstring when SQL Job is executing the packet. So I reset the SQL Job Step and configure the SSIS Packet again from scratch not touching the connectionstring and the packet again executes successfully.

So now it is apparent that you can’t touch the connections string of an Excel connection in Connection Managers, but if the path used in development does not match the production environment you end up with validation problems. So to get around this I go into the Dev environment and match the variable holding the ExcelFilePath to the TEMPLATE file location on the production (PROD Server), build the package and redeploy to PROD server. I reset the SQL Job Step again and configure all connections except for the Excel one and run the package, but to my surprise it fails but now with an understandable error:

Image 5

Now its points to it cannot find the 64-bit Provider, so I thought I will just uninstall them and just install 64-bit, finally I have a running package on the PROD Server.
For good measure I thought let’s do some test to get a full understanding of this, so I install the 32-bit Provider again in \quiet mode and try again. Again the package execution fails with same error as before (absolutely no changes to Package Configuration).
So I’m thinking does the 32-bit Provider overwrite the 64-bit registration somehow so uninstall them both again and install just the 64-bit, again everything works just fine. As the stubborn individual I am I install the 32-bit on top again and it fails. So uninstall both Install 32-bit first and then 64-bit and now the 64-bit works.

Conclusion you have to install the 32-bit first if you want the 64-bit to work, but now does the 32-bit one work? To test this I go to my Dev Test SQL Server 2019 where I also have the VS2019 environment installed requiring the 32-bit Provider and uninstall then both. Install 64-bit and successfully execute the package in SQL Server 2019. But opening VS the Task complains there is no 32-bit provider.
So again, I just install the 32-bit on top of the 64-bit and now the VS environment is happy but the SQL Server 2019 SQL Job fails to execute the SSIS Package.

So I uninstall them both again and install first the 32-bit verify in VS it is happy and then add the 64-bit and again SQL Job executes the SSIS Package successfully.

Now finally I can execute the package in 32-bit in VS and in 64-bit on SQL Server 2019.

I just can’t understand an issue like this can exist as the product has been around for over 10 years and that I could not find anybody that has run into this before. How can nobody ran into this issue before as anybody developing on one computer and deploying to a separate production server would have this issue.