SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Building Flat File Connectors Dynamically For SSIS

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!

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.


Leave a comment on the original post [sqlserverio.com, opens in a new window]

Loading comments...