Excel Export Configuration
- Juan Brea (Deactivated)
The Excel export is a table configured job solution that is used to dynamically export the contents of a SQL table into excel. This is particularly useful for capturing configuration work or any other ‘manual’ data work that is not observed within the data packages.
How to use
After following the deployment instructions , the user can call the dbo.QueueExport proc to queue the tables to be exported. Upon execution, the proc will insert a pointer for all the active exports at that time into the PTG_ExportQueue table, where the records will await for the next time the job runs, exports the rows and marks the rows as exported in the PTG_ExportOut table.
User Guide
Here’s the following steps on using this tool. (Missing reference images)
Ensure the Export Job has been deployed and scheduled.
Ensure the Export Queues are set up and enabled.
Populate and configure your Benchmark tables
Run ‘Exec dbo.QueueExport’
Wait for the job to pick up the queues and create the files.
How to set up
You’ll need to create an export queue for the exporter job to pick up the tables you want to export. You may use the Insert script on TFS as a starting point to write all the table’s export queues.
\TFS\Conversion\Integration_Pipeline\Export Job\Stored Proc\Insert Script.sql
Solution
The solution has 3 main parts:
The queue proc, QueueExport
The configuration tables: PTG_ExportDeploy, PTG_ExportConfig, PTG_ExportQueue, PTG_ExportOut, PTG_ExportInstance
The SSIS Parametrized package: The package looks for the Active exports and generates the file. ExportPackage.dtsx
Tables
PTG_ExportDeploy
ExportID: The unique pointer for the export job.
ExportName: A descriptive name for the purpose and data of the export.
Active: The Active flag that controls whether a file gets generated.
PTG_ExportConfig
ExportConfigID: The ID for the configuration row.
ExportID: The Export ID that relates back to PTG_ExportDeploy.
TableName: The name of the table with the data to export.
FilePath: The target location for the generated file.
FileName: The intended name for the generated file.
PTG_ExportInstance
ExportInstanceID: The ID for the Instance.
bywho: The DB User that queued the export.
Timestamp: The time when the export was queued.
PTG_ExportQueue
ExportQueueID: The ID for the queue row.
ExportInstanceID: The Instance ID. (PTG_ExportInstance)
ExportID: The ID for the export. (PTG_ExportDeploy)
dbo.QueueExport
The proc will start an InstanceID on PTG_ExportInstance which logs who queued the export.
It will then look through all the Active exports on PTG_ExportDeploy and insert a pointer for each active row into PTG_ExportQueue to be picked up by the export package upon job execution.
Package
This package is to be deployed as an Agent Job.
\TFS\Conversion\Integration_Pipeline\Export Job\ExportPackage.dtsx
Set Export ID: Gets the queued export rows from PTG_ExportQueue
Set Export File Variables: Sets table name for source data, file name, file path, sheet name for each export
Create Excel File: Is the C# script task that creates the file in the Folder system
Mark Exported rows: Removes the exported rows from PTG_ExportQueue and adds a row to PTG_ExportOut to timestamp the export as done.
Deployment
In order to enable the exports the following steps need to be taken:
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.
