SSIS - Can I get the column schema for a flat file source from a database?

Question

We receive a nightly data export from a vendor in the form of about 10 tab-delimited flat file without column headers. In addition, the vendor provides us with the SQL scripts for the database tables so that we can import the files into our system.

Unfortunately, the vendor recently changed the schema for the flat files. Each file has upwards 150 columns, and having to go through the DB schema and adjust column types on a Flat File Data Source in SSIS is extremely time consuming, not to mention a royal pain.

Since I know the file data layout in the database schema, is there any way I can dynamically pull that into a Flat File source to set the columns correctly? Or am I just stuck with manually setting everything?

Answer 1

You cannot do this through BIDS (boy that would be nice), but you certainly can do it through the object model. I built a program which read Focus master files (describe a fixed-width flat text layout) and used a template package and then update the connection manager to reflect the column layout (which I read into my own data structure). Parsing the SQL might be a little harder, but this is certainly semi-automatable.

Note that if the SQL has native types like int, someone will have to tell you or you will have to figure out how wide the int is in the text file if you are using fixed width columns. In this case, your life could be a lot easier in CSV.

My full program also makes a derived column transform to trim spaces and does some other things - the package generated is then cleaned up and tested by hand.

Here's some example code (mf is my objectmodel for the masterfile, this code loads a template package, adds a connection manager, puts all the fields in):

        Application App = new Microsoft.SqlServer.Dts.Runtime.Application();
        p = App.LoadPackage(TemplatePackage, null);

        ConnectionManager cm = p.Connections.Add("FLATFILE");
        cm.Properties["Name"].SetValue(cm, mf.SSISConnectionManagerName);
        cm.Properties["ConnectionString"].SetValue(cm, FlatFilePath);
        cm.Properties["Format"].SetValue(cm, "FixedWidth");
        cm.Properties["RowDelimiter"].SetValue(cm, "\r\n");
        cm.Properties["HeaderRowDelimiter"].SetValue(cm, "\r\n");
        cm.Properties["CodePage"].SetValue(cm, 1252);
        cm.Properties["ConnectionString"].SetExpression(cm, "@[User::FilePath] + \"\\\\\" + @[User::FileName]");

        RWrap.IDTSConnectionManagerFlatFile90 con = cm.InnerObject as RWrap.IDTSConnectionManagerFlatFile90;

        List<FocusField> flds = mf.Fields();
        foreach (FocusField fld in flds)
        {
            RWrap.IDTSConnectionManagerFlatFileColumn90 Col = con.Columns.Add();
            (Col as RWrap.IDTSName90).Name = fld.FieldName;
            Col.ColumnType = "FixedWidth";
            Col.ColumnDelimiter = "";

            Col.DataType = RWrap.DataType.DT_STR;
            Col.ColumnWidth = fld.SSISColumnWidth;
            Col.MaximumWidth = fld.SSISColumnWidth;
        }

        RWrap.IDTSConnectionManagerFlatFileColumn90 EolCol = con.Columns.Add();
        (EolCol as RWrap.IDTSName90).Name = "CRLF";
        EolCol.ColumnType = "FixedWidth";
        EolCol.ColumnDelimiter = "";
        EolCol.DataType = RWrap.DataType.DT_STR;
        EolCol.ColumnWidth = 2;
        EolCol.MaximumWidth = 2;

Answer 2

Also see http://sqlsrvintegrationsrv.codeplex.com/releases/view/21238