home
Donate to support site


SSIS Generic File Exporter - ConfigurableComponents
Author Nigel Rivett

Generic SSIS File Exporter - ConfigurableComponents


Objective

To find the items in an export package that define the items to be exported and destination.


Method


Create an export package to export a single column table to a csv file with headers and quote delimited data items.
Import the package xml and identify the items that will be configured at run time.
Similarly for a two column table to compare.


Package name: Export_c001.dtsx (Export_c001c002.dtsx)
Source - OleDB, table

Destination: Flat file

Delimited
Text qualifier "
Column names in first data row

create table ExportTest_c001


(
c001 varchar(20)
)

go


Configurable items identified

Server, Database

DTS:ConnectionString="Data Source=DESKTOP-D6M6JCB;Initial Catalog=SSIS_Test;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />


Table

property

dataType="System.String"
description="Specifies the name of the database object used to open a rowset."
name="OpenRowset">[dbo].[ExportTest_c001]


Table Column

Block: inputColumn

refId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].Columns[c001]"
cachedCodepage="1252"
cachedDataType="str"
cachedLength="20"
cachedName="c001"
externalMetadataColumnId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].ExternalColumns[c001]"
lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[c001]"

Block: externalMetadataColumn

refId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].ExternalColumns[c001]"
codePage="1252"
dataType="str"
length="20"
name="c001"


File

DTS:ConnectionString="C:\Users\nigel\OneDrive\Nigel\Articles\SSIS_FileExport\FileExport_c001.txt">


File Column

Block: DTS:FlatFileColumn

DTS:ColumnType="Delimited"
DTS:ColumnDelimiter="_x000D__x000A_"
DTS:MaximumWidth="20"
DTS:DataType="129"
DTS:TextQualified="True"
DTS:ObjectName="c001"
DTS:DTSID="{008EBA94-7184-465B-A6FC-A74341888919}"
DTS:CreationName=""

Block: outputColumn

refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[c001]"
codePage="1252"
dataType="str"
errorOrTruncationOperation="Conversion"
errorRowDisposition="FailComponent"
externalMetadataColumnId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[c001]"
length="20"
lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[c001]"
name="c001"
truncationRowDisposition="FailComponent"

Block: externalMetadataColumn

refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[c001]"
codePage="1252"
dataType="str"
length="20"
name="c001"

Block: outputColumn

refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[c001]"
codePage="1252"
dataType="str"
length="20"
lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[c001]"
name="c001"

Also notice that the column delimiters are specified with a crlf for the final column 

Downloads
	Package: single column export
	Package: two column export
	SQL to import package and prepare templates
home