...
Deploy the solution tables to the DB.
Populate the PTG_ExportDeploy and PTG_ExportConfig tables with the table names, file names, file paths and active flag.
Check that the server has access to the destination File path.
Deploy the queuing procedure (dbo.QueueExport).
Deploy the package as a scheduled SQL Agent Job, running every 15 or 30 mins.
Troubleshooting
Error "ACE.OLEDB.12.0 Provider Not Registered on local machine"
If you receive the error above, you need to ensure the host machine has the drivers installed. See Microsoft’s Guide: ACE.OLEDB.12.0 provider not registered on local machine | Microsoft Docs
These drivers are on the TFS: \TFS\Conversion\Integration_Pipeline\Export Job\Troubleshooting\
64-bit and 32-bit, Depending on which version of Office is installed on the host machine. If after installation of one, the error persists, please uninstall and install the other one.
Error “Cannot reach the [export] location”
For the current iteration of the Exporter, we have not added support to create the File location directory, so we need to ensure the folder exists prior to execution.
Checking if the Server can see the File Path
We can use the following commands to check if the SQL Server can access the file location we want to set as the export location.
...
SQL Server Deployment mismatch
The Script Task "XXX" uses version XX.0 script that is not supported in this release of Integration Services. This error is due to the script deployment targeting a later SQL Server version than the current one. i.e. Script deployment looking for SQL Server 2022, but the server is 2019.
...
To fix, we’ll need to update the Project deployment settings on the package to match the target SQL Server version.
Right click the Project > Properties > Configuration Properties > General > TargetServerVersion > Select version from dropdown.
...
From here we can save our package and if working locally, manually deploy it to our customer’s Server, or if working on the customer environment, deploy it directly from Visual Studio.
...