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);