Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assigning a particular flat file record to a string variable

Hey all!

Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?

Any thoughts?

In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?

Thanks for any suggestions!

Jim Work
You could do this in the data flow using a script component to keep track of which row you are on....|||Phil, that sounds like a great plan. I am very, very, very new at this, though, and I could use a little more detail?

Going through a flat file line-by-line seems like it would be a very straightforward thing to me...
|||The data flow on its own operates on a row by row basis. That's what it's designed to do, and very fast, by the way.|||Oh, you're kidding me!

Thanks!!