Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 4 Next »

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.

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. [PACKAGE NAME GOES HERE]

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)

Package

PLACEHOLDER FOR PACKAGE LOCATION

Deployment

In order to enable the exports the following steps need to be taken:

  1. Deploy the solution tables to the DB.

  2. Populate the PTG_ExportDeploy and PTG_ExportConfig tables with the table names, file names, file paths and active flag.

  3. Deploy the queuing procedure (dbo.QueueExport).

  4. Deploy the package as a scheduled SQL Agent Job, running every 15 or 30 mins.

  • No labels