home


SSIS Generic File Exporter 5 - Package Template
Author Nigel Rivett

Generic SSIS File Exporter 5 - Package Template


Objective

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


Method
	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



Downloads

Code (sql) to identify items to replace

home