SSIS: Flat File default length

Question

I have to import about 50 different types of files every day. Some of them with a few columns, some inculde up to 250 columns.

The Flat File connection always defaults all columns to 50 chars. Some columns can be way longer than 50 chars, and will of course end up in errors.

Currently i am doing a stupid search&replace with notepad++ - Opening all SISS packages, replacing:

DTS:MaximumWidth="50"

by

DTS:MaximumWidth="500"

This is an annoying workaround. Is there any possibility to set a default length for flatfile string columns to a certain value?

I am developing in Microsoft Visual Studio Professional 2015 and SQL Server Data Tools 14.0.61021.0

Thanks!

Answer 1

I don't think that there is a way to achieve this from SQL Server Data Tools.

But you can do some workaround to achieve this:

1. Easiest solution, In the Flat file connection manager - Advanced Tab, select all columns (using Ctrl key) and change the data length property for them all in one edit. (detailed in @MikeHoney answer)

2. You can use BIML (Business Intelligence Markup Language) to create ssis package, if you're new to BIML you can access to BIML Script website for detailed tutorials.

3. You can create a Small application that loop over .dtsx files in a folder and replace DTS:MaximumWidth="50" with DTS:MaximumWidth="500" using normal String.Replace function or using Regular expressions. (you can read my answer @ Automate Version number Retrieval from .Dtsx files to see an exmaple on reading .dtsx file using Regular expressions)

Function To Read and Replace content of dtsx file (Vb.Net)

Public Sub FixDTSX(byval strFile as string)

    dim strContent as string = string.empty

    Using sr as new Io.StreamReader(strFile)

        strContent = sr.ReadToEnd()

        sr.Close()

    End Using

    strContent = strContent.Replace("DTS:MaximumWidth=""50""","DTS:MaximumWidth=""500""")

    Using sw as new Io.StreamWriter(strFile,False)

        sw.Write(strContent)

        sw.Close()

    End Using

End Sub

Answer 2

There is a way to achieve what you want using the standard Visual Studio SSDT UI, although it is quite obscure. AFAIK it works in every version of this editor since SQL Server 2005.

With the package open, from the Connection Managers pane, right-click your Flat File Connection and choose Edit. Then navigate to the Advanced page. Then multi-select the columns you want to change (e.g. shift-click a range or ctrl-click a specific set). Now the Properties appearing at the right will be applied to all the selected columns.

In the example shown below, I have set all the selected columns to a width of 255.