Project Description

SSISConnectionBuilder is a simple command line tool to ease the burden of
building flat file connectors for SSIS.

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 delimeter, 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.


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

Alpha 2 ConnectionBuilder.exe no longer has any external dll dependencies.

To generate the schema spread sheet you can run one of these queries against your table and just cut and paste into a new spread sheet.

--SQL Server 2008/R2/2012 
SELECT 'Column' = sc.name,
       'Type' = CASE st.name
                  WHEN 'time' THEN 'time(p)'
                  WHEN 'datetimeoffset' THEN 'datetimeoffset(p)'
                  ELSE st.name
                END,
       'Precision' = CASE st.name
                       WHEN 'decimal' THEN sc.PRECISION
                       WHEN 'numeric' THEN sc.PRECISION
                       WHEN 'float' THEN sc.PRECISION
                       WHEN 'time' THEN sc.scale
                       WHEN 'datetimeoffset' THEN sc.scale
                       WHEN 'varbinary' THEN sc.max_length
                       WHEN 'varchar' THEN sc.max_length
                       WHEN 'binary' THEN sc.max_length
                       WHEN 'char' THEN sc.max_length
                       WHEN 'nvarchar' THEN sc.max_length
                       WHEN 'nchar' THEN sc.max_length
                       ELSE 0
                     END,
       'Scale' = CASE st.name
                   WHEN 'decimal' THEN sc.scale
                   WHEN 'numeric' THEN sc.scale
                   ELSE 0
                 END
FROM   sys.objects o
       INNER JOIN sys.columns sc
               ON o.object_id = sc.object_id
       INNER JOIN sys.types st
               ON sc.user_type_id = st.user_type_id
WHERE  o.name = 'datatypes'

--SQL Server 2005 
SELECT 'Column' = sc.name,
       'Type' = st.name,
       'Precision' = CASE st.name
                       WHEN 'decimal' THEN sc.PRECISION
                       WHEN 'numeric' THEN sc.PRECISION
                       WHEN 'float' THEN sc.PRECISION
                       WHEN 'varbinary' THEN sc.max_length
                       WHEN 'varchar' THEN sc.max_length
                       WHEN 'binary' THEN sc.max_length
                       WHEN 'char' THEN sc.max_length
                       WHEN 'nvarchar' THEN sc.max_length
                       WHEN 'nchar' THEN sc.max_length
                       ELSE 0
                     END,
       'Scale' = CASE st.name
                   WHEN 'decimal' THEN sc.scale
                   WHEN 'numeric' THEN sc.scale
                   ELSE 0
                 END
FROM   sys.objects o
       INNER JOIN sys.columns sc
               ON o.object_id = sc.object_id
       INNER JOIN sys.types st
               ON sc.user_type_id = st.user_type_id
WHERE  o.name = 'datatypes'

--SQL Server 2000 
SELECT 'Column' = sc.name,
       'Type' = st.name,
       'Precision' = CASE st.name
                       WHEN 'decimal' THEN sc.xprec
                       WHEN 'numeric' THEN sc.xprec
                       WHEN 'float' THEN sc.xprec
                       WHEN 'varchar' THEN sc.length
                       WHEN 'binary' THEN sc.length
                       WHEN 'char' THEN sc.length
                       WHEN 'nvarchar' THEN sc.length
                       WHEN 'nchar' THEN sc.length
                       ELSE 0
                     END,
       'Scale' = CASE st.name
                   WHEN 'decimal' THEN sc.xscale
                   WHEN 'numeric' THEN sc.xscale
                   ELSE 0
                 END
FROM   syscolumns sc
       INNER JOIN systypes st
               ON sc.xusertype = st.xusertype
       INNER JOIN sysobjects o
               ON sc.id = o.id
WHERE  o.name = 'datatypes' 

Last edited Jul 24, 2013 at 4:44 AM by SQLServerIO, version 7