Populate tables with data that can be used to create XML to export the data from a table Simple package to carry out an export. This will be used to replace blocks depending on the table to be exported. We can use the simple single column export package as a an initial template. The items to be altered will be replaced by identifiers to be used by the SP
1. Take the single column import package and import into a table. The table has a single column and an identity column. I use bulk insert but be cautious to check the data is imported in order 2. From this identify the items to be altered 3. Create a table containing the static package data and another with data that needs to be altered for each table export 1. Import package data create table ExportPackageTest ( s varchar(1000) , id int identity (1,1) ) go create view ExportPackageTest_vw as select s from ExportPackageTest go bulk insert ExportPackageTest_vw from 'C:\Users\nigel\OneDrive\Nigel\Articles\GenericFileImporter\Test\GenericImport\GenericImport\Export_c001.dtsx' 2. Identify items to be altered These were investigated in the Find configurable components step Here we identify the blocks by their start and end line IDs Note: this is programatic so that it can be performed on data from an altered package. The code is in a download at the bottom 3. Create and populate two tables Export_Package_Template: Static package XML Export_Package_Template_Replace: Configurable package XML that changes for each table export At run time the output package data will be created by replacing the configurable items The column data will be used to create a column block for each column in the table The configurable data blocks are DatabaseConnection: Identifies the server and database FileConnection: Names the file to create FileCol1: Column to export TableCol1: Column to export FileCol2: Column to export TableName: Table to export ColMap1: Column to export ColMap2: Column to export
Code (sql) to identify items to replace
home