SSIS 2008: I need to pass a RowDelimiter dynamically to my SSIS packge. My Source Flat files can have either CR-LF or LF as a Row Delimiter. I created a variable (Package level) named strRowDelimiter. I am passing strRowDelimiter to Flat File connection to RowDemiliter dynamically (using the "Expressions" options) i.e. RowDemiliter = @[User::strRowDelimiter ] But it does not work. Has someone done like this before? Is there any other way I can pass RowDelimiter dynamically to my SSIS packge?

I ran test few minutes and didn't succeed. So I think that configuring Row delimiter and Column Delimiter in flat file connection manager with expression is not developed fair enough and you can not do that. I think this problem is because of flat file connection manager makes columns statically when you configure flat file connection manager, if you double click on flat file connection manager in the advanced tab, you will find that all columns created there with a property named Column Delimiter. So I think this part of flat file is bad developed, even with expression property named RowDelimiter I couldn't to set it dynamically with a variable. http://www.rad.pasfu.com

You can user BULK INSERT TASK instead of dataflow and flat file source in the BULK INSERT TASK, you can set ROW TERMINATOR and FIELD TERMINATOR expression properties, and this will work correctly.http://www.rad.pasfu.com

We do some pretty intense transformation of the data, so BULK INSERT was not an option. I can share an alternate suggestion. I agree that SSIS 2008 does not seem to allow you to set the HeaderRowDelimiter through Expressions. I suspect it has to with the fact that the delimiter appears twice in a FlatFile connection manager, once as HeaderRowDelimiter and second as the ColumnDelimiter in the last column. You can't set that one dynamically from the UI (maybe another way?), so it doesn't work. A CR-LF also shows up as "_x000D__x000A_", hmm. We have flat files where 90% of them are CR-LF, but 10% of them are LF. I hope that matches up with your problem. 1)So, I used multiple Flat File Connections, one for CR-LF and the other for LF. 2) Create one data flow task for each file connection. I was able to COPY exactly. 3) Right before your Data Flow Tasks, add a script task that does some test for the kind of file and sets a flag in a variable. Then from that script task connect to each data Flow task. 4) Double click each connection and change it to Success and Constraint. One constraint would be like @[User::DelimFlag] != "CRLF" and the other @[User::DelimFlag] == "CRLF". I used the approach becase I had to use Data Flow task components and wanted to use one package for all my files. You could use the same approach if you had multiple file types in the same directory, but you would have different data flow tasks for each. Good luck, Grant

Your alternate suggestion is interesting. Could you publish the script task that does some test for the kind of file ? Thank you !

Finally, I wrote the script myself... In french (sorry) using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; namespace ApplicationCS { class Program { static void Main(string[] args) { string cheminFichier = "c:\\temp\\test.txt"; int nbrCarbufferFichierAnalyse = 0; char[] bufferFichierAnalyse = null; string delimiteurLigne = ""; using (StreamReader fichierAnalyse = new StreamReader(cheminFichier)) { bufferFichierAnalyse = new char[8000]; nbrCarbufferFichierAnalyse = fichierAnalyse.Read(bufferFichierAnalyse, 0, bufferFichierAnalyse.Length); if (nbrCarbufferFichierAnalyse > 0) { for (int positionDansBufferFichierAnalyse = 0; positionDansBufferFichierAnalyse <= (bufferFichierAnalyse.Length); positionDansBufferFichierAnalyse++) { // Le caractère 0D est recherché. // S'il est suivi de 0A, on en conclut que le RowDelimiter est CRLF int a = (int)bufferFichierAnalyse[positionDansBufferFichierAnalyse]; if ((int)bufferFichierAnalyse[positionDansBufferFichierAnalyse] == 13) { if ((int)bufferFichierAnalyse[positionDansBufferFichierAnalyse + 1] == 10) { delimiteurLigne = "{CR}{LF}"; } else { delimiteurLigne = "{LF}"; } break; } // 'Si seul un 0D est trouvé, on en conclut que le RowDelimiter est LF if ((int)bufferFichierAnalyse[positionDansBufferFichierAnalyse] == 10) // delimiteurLigne = "LF" { delimiteurLigne = "{LF}"; break; } } } } Console.WriteLine(delimiteurLigne); Console.WriteLine("Appuyez sur une touche..."); Console.ReadLine(); } } }