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

ASP.NET application to use DTS

I've got an application that needs to:
1. Look on the user's machine and locate a file
2. Pull that file over to DTS
3. Allow DTS to insert this file into a table

I'm sure this can be done...anyone got any article on how to do it?

Thanks!

You could adopt to the following two approaches;
1. You can created a DTS package (using VB Script) to search for a file on the machine and then pull the file over to DTS and finally date into database.
2. You can register this as scheduled task
3. You can call this package programatically from ASP.NET
or there is another approach as described in the following article;
http://www.dotnetbips.com/7F78A65B-456D-45D1-BD10-2F5E2C218696.aspx?articleid=228