There are times that you want to make Row Delimiter and Column Delimiter dynamic in Flat File source, and you want to change these values dynamically with package variables.
usually you can use EXPRESSION properties for making things dynamic in SSIS, such as ConnectionString and bind it to a package variable.
As you may know there is no Column Delimiter in Flat File Connection manager, I think this is because limitation of SSIS Data flow task which is STATIC METADATA. actually SSIS can not solve columns on the fly in Data flow task, so It needs to define columns exactly at design time.
So there is no way to make Dynamic Column Delimiter in Flat File Connection manager .
On the other hand you can find RowDelimiter Expression property in Flat File Connection Manager, But after a simple test you will find that this will not work correctly, maybe this is a hidden bug in SSIS.
So what is work around?
If you want to read data from flat file into SQL Server table, use BULK INSERT TASK instead of Data flow.
in the BULK INSERT TASK you can set these EXPRESSION properties:(you can find them in the expression tab of Bulk Insert Task Editor)
and this will work with binding to variables,this is I think because of physical layer behind of bulk insert task which probably use Bulk Insert ( T-SQL command ).
If you want to read data into other Data Source, You need Scripting for this. you can use Split(‘delimiter character’) .NET Method for creating dynamic delimiters in Script task .