Building Connectors Is Crap in SSIS – Updated

What else can I say. I finally broke when I had to build a flat file connector with 258 columns that needed to be imported into a staging database. 258 columns… I almost had a stroke. Not only is it mind numbing it’s also error prone. I do dabble in c# so I build a little tool to automate this for me.

Introducing SSISConnectionBuilder

SSISConnectionBuilder is a simple command line tool to ease the burden of building flat file connectors for SSIS. It is WAY WAY alpha but I am working on cleaning up the code. You can pick it up here on codeplex.

I have only tested this on Windows 8/7 64 bit. If you get an error about cannot find DLL you need to install the client development stuff from the SQL Server installer.

You need to generate an excel spreadsheet with four columns. Column,Type,Precision,Scale.

The program loops through the sheet and kicks out an SSIS dtsx file with a single flat file connector defined.

You can choose a delimiter, package name and set the csv file name for the connector. The csv file name doesn’t have to be valid. If you know the csv file will be unicode you need to pass the -u or you will have errors with your connector with the error column being ntext instead of text.

Command line options:

-s, –schemafile =VALUE Your excel schema definition file.

-d, –delimiter=VALUE The column separator you wish to use, usually a comma or pipe.

-p, –packagename=VALUE Name of the dtsx file that will have your connection in.

-c, –csvfilename=VALUE Name of the csv file that your connection will use.

-u, –unicode csv file is Unicode.

-?, -h, –help show this message and exit

Update 7/23/2013

Removed the dependencies on the SSIS SDK the current release doesn’t require any external dll’s to run!