May 13, 2009 at 9:30 am
When creating a flat file connection manager, the default row delimiter is {CR}{LF} and there are seven othe choices. What if all these choices are not suitable for my usage and I want to create my own, is there a way to do it? For example, I want to use ##&&** as a row delimiter.
w
May 13, 2009 at 9:59 am
you can type any value you want into the row delimiter in the flat file connection manager, it is under the columns tab in the connection manager editor
May 13, 2009 at 10:35 am
You know, after I sent my post out, I thought I should try typing over it in that drop down field and it works. I do have a follow up question,
I have 50 flat file connection manager created in it, is there a way to treate this customized row delimiter as a variable or parameter so I can change them all at once? My thinking is that after I build the solution as package, I want to pass this variable to the package for execution. Can this be done?
OD
May 13, 2009 at 10:47 am
If you go into the properties window of the file connection and click on expressions, click on (...) for a new expression.
Here you can select 'Row Delimeter' from the drop-down box under property, then click (...) to create an expression. You can add a user variable that will hold the delimeter to your expression and this should resolve to a valid delimeter.
Though I have not done this before, i have created a lot of expressions for the file name property and they work fine. So i am assuming it should work also for the row delimter.
May 13, 2009 at 10:54 am
Yep, it does work.
Have you had any experience of passing a value to that variable when executing a package? Do you know what need to be done?
Here is my command of executing the package.
dtexec /F "C:\DTSPackage\ExportUserData.dtsx"
May 13, 2009 at 11:00 am
No i don't sorry, i run all my packages on SQL server as jobs and load the variables from tables.
I can ask one of the guys here at work tommorow if you don't find a solution before then..
May 13, 2009 at 11:11 am
I understand that there are many ways to solve a problem. Maybe I should find another way. Let me ask you this. I have a SSIS project(with one solution) that is for say 5 customers and variables for them are customerID and row delimiter. How would you implement it if you just want to create and maintain one solution for all customers?
May 13, 2009 at 10:54 pm
I suggest that you read up on Package Configurations - I think that will work well for you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 15, 2009 at 3:04 pm
I need to clarify my 'it is working' statement in my reply. If I directly typed in the custom row delimiter, it would work. But if I define a user variable for the custom row delimiter and use the Property Expression Editor to subsitute the HeaderRowDelimiter and RowDelimiter for the flat file connection with the user variable, the subsitution does work but when I run it, the row delimiter on the text file is still {LF}{CR} not the custom row delimiter I set to.
The problem seems to be in the flat file connection where I define the custom row delmiter. If I go into the Flat file Connection Manger editor, In the 'Columns' property, the delimiter does set to the correct custom row delimiter. But in the 'Advanced' property and the very last field's property has a column delimiter stlll set to {LF}{CR}. The only way to make this work is I need to type in the row delimiter here which is not what I want. I am stuck.....
od
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply