I want to extract string data from text files which will likely contain all of the 'available column delimiters:' CRLF, semicolon, colon, comma, tab, and bar. My problem is that my connection manager reads every occurance of the given character as a delimiter, which it frequently is not. I can request that these text files be delivered to me with any Ascii characters I choose to be used as the column and record delimiters. There are many characters which would not be found in the files' text--some of the non-printing characters and a lot of the extended characters--but my question is how could I 'teach' the connection manager to recognize any of the characters which are not on its ColumnDelimiter list? I can think of a two step RegEx process by which I have the file delivered to me using some of the obscure Ascii characters and then make a couple of passes through the body searching and replacing until there were no remaining occurances of the column delimiters but is there a better way?HomeCookN

could you put some lines of text files data here?http://www.rad.pasfu.com

A short example could be something like the following five fields. If a comma is used as a column delimiter, then the connection manager will mistake the commas within the Field4ProcessExtract column as delimiters without 'realizing' that they are simply part of that column's string data. Does this clarify my question? I need to use a column delimiter which can be guaranteed not to ever appear within the body of the file's text string data. Field1CreatedDate,Field2CreatedBy,Field3SigningDate,Field4ProcessExtract,Field5DollarCost 12/12/2010,Bob Smith, 12/23/2010,This field could contain several paragraphs of formated text which would include: colons; tabs, line feeds, etc.,$12,000HomeCookN

If you had text qualifier around your values on each column then you could handle it simply with configure it as Text qualifier, but in your case I think you have no way instead of scripting in Script Component and use Regular Expression or string functions. this is because flat file connection manager can not find actual column delimiter when you have this character inside values.http://www.rad.pasfu.com

Thanks for the analysis, Reza. One other possible solution: instead of using the Flat File Connection Manager Editor to select a column delimiter from the listbox, I see from the Help files that there a way to programatically configure a Connection Manager. Perhaps in code I might be able to access a property like ConnectionManager.ColumnDelimiter and specify any Ascii character I choose as my column delimiter instead of being limited to the 8 choices in the column delimiter listbox? (Haven't been able to test this idea yet because I'm having trouble using the object browers to find Microsoft.SqlServer.Dts.Runtime where I suppose the ConnectionManager class must live.)HomeCookN

what about using the "Knot" symbol ¬ (sort of horizontal L symbol on keyboard next the number 1 key on a UK keyboard) never seen it used in any sort of input file text so a pretty safe bet as a column delimiter, its what i've always used in the past HTH

If you can put another column delimiter instead predefined values in the Flat file connection manager, so you can use Fandango suggested way. But note that you need to scripting for automating this changing the delimiters process. let me know if you mean anything else.http://www.rad.pasfu.com

Yes, FandangoAmeruso, ¬ or many other of the Ascii extended characters--between 128 and 256--would work fine as far as creating the text file. The problem so far is how to 'teach' SSIS to recognize ¬ or whatever I use, as the column delimiter. It seems that the SSIS Connection Manager has a list of only 8 characters--just pretty standard ones like comma, colon, etc.-- which it can recognize as column delimiters and ¬ is not one of them. HomeCookN

Yes, FandangoAmeruso, ¬ or many other of the Ascii extended characters--between 128 and 256--would work fine as far as creating the text file. The problem so far is how to 'teach' SSIS to recognize ¬ or whatever I use, as the column delimiter. It seems that the SSIS Connection Manager has a list of only 8 characters--just pretty standard ones like comma, colon, etc.-- which it can recognize as column delimiters and ¬ is not one of them. HomeCookN

Yes, FandangoAmeruso, ¬ or many other of the Ascii extended characters--between 128 and 256--would work fine as far as creating the text file. The problem so far is how to 'teach' SSIS to recognize ¬ or whatever I use, as the column delimiter. It seems that the SSIS Connection Manager has a list of only 8 characters--just pretty standard ones like comma, colon, etc.-- which it can recognize as column delimiters and ¬ is not one of them. HomeCookN

Thanks, Reza. I guess I will have to write a RegEx script to handle this. I'll go bother the guys in that forum for a while.HomeCookN

Thanks, Reza. I guess I will have to write a RegEx script to handle this. I'll go bother the guys in that forum for a while.HomeCookN

Thanks, Reza. I guess I will have to write a RegEx script to handle this. I'll go bother the guys in that forum for a while.HomeCookN

Using the flat file connection manager, all you have to do is go to the columns screen of the flat file connection manager editor by clicking columns on the left hand side, and then just type in the character you want to use as a delimiter in the appropriate column or row box rather than having using one of the preset ones available as drop down items. The boxes will allow you to overtype the 8 selections. HTH I should add that this is the case in VS2008 which is what i'm using, i've not got VS2005 installed unfortunately to be able to check if its the same though

Using the flat file connection manager, all you have to do is go to the columns screen of the flat file connection manager editor by clicking columns on the left hand side, and then just type in the character you want to use as a delimiter in the appropriate column or row box rather than having using one of the preset ones available as drop down items. The boxes will allow you to overtype the 8 selections. HTH I should add that this is the case in VS2008 which is what i'm using, i've not got VS2005 installed unfortunately to be able to check if its the same though

This is just what I want to know. We don't have ¬ on the keyboard layout in my country, but I can copy and paste it using Character Map. (Using Alt+0172 doesn't work in SSIS for some reason.) Thanks for your help. Happy New Year to you.HomeCookN