SSIS Packages in Visual Studio CSV file to SQL using Column Delimiter of Flat File Connection

Question

I have SSIS Packages in Visual Studio 2019 I want to import from a CSV file. The major issue is here that I am unable to pass a value to to define the column delimiter using variable. Is there any solution to pass a variable to column delimiter of flat file connection (passing value using expression or something like that)?

One way of doing that is using BULK insert, but we cannot do mapping in BULK Insert.

Answer 1

Before executing ssis package , I changed the column delemeters programmatically and save package ,

like

string packagePath = @"pathtomyssispackage.dtsx";
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            Microsoft.SqlServer.Dts.Runtime.Package pkg = app.LoadPackage(packagePath, null);

            foreach (ConnectionManager connMgr in pkg.Connections)
            {
                if (connMgr.CreationName == "FLATFILE")
                {
                    IDTSConnectionManagerFlatFile100 flatFile100 = connMgr.InnerObject as IDTSConnectionManagerFlatFile100;

                    if (flatFile100 != null)
                    {
                        var length = flatFile100.Columns.Count;
                        for (int i = 0; i < length-1; i++)
                        {
                            IDTSConnectionManagerFlatFileColumn100 column = flatFile100.Columns[i];
                            column.ColumnDelimiter = "|";//newdelemeter here
                        }
                        
                    }
                }
            }
            app.SaveToXml(packagePath, pkg, null);

Answer 2

No, as Thom A calls out, each Column <DTS:FlatFileColumn has an attribute DTS:ColumnDelimiter="_x002C_" that specifies the delimiter used 2C here is a comma.

Fun fact, the final column deviates from the column delimiter as it will list the Row Delimiter value DTS:ColumnDelimiter="_x000D__x000A_" 0D 0A is Carriage Return, LineFeed

SSIS exposes nothing that allows you to modify all the column delimiter values for a Flat File Connection Manager at run-time.

How do I solve my problem of changing delimiter values?

It depends.

The best answer is to tell your data providers "this is the format I accept" and make them standardize on something. Comma, pipe, thorn, don't care, just some consistent delimiter.

Next option, preprocessing. If you know provider A sends you a Pipe | delimited file, call the PipeToCSV.dtsx package (or C# or python or whatever tech) to format the file into your expected format. Write N converter pre-processes and now your "actual" SSIS package is trivial because you don't have to worry about making it do the things it cannot do.

But I also have variances in the file itself... If that's your problem, then you're trying to write a generic/slurp-and-burp/from anything to anything type of process and that is not the bailiwick of SSIS. It is tightly bound to the design-time metadata of the inputs and outputs. Which means it cannot change at runtime. If that's your problem domain, then you're looking for Azure Data Factory or find a copy of SQL Server 2000 and use DTS. etc etc