MS SQL Server DTS - Using a constant value in data transform task

Author

Using a constant value in data transform task

Terry via SQLMonster.com

2005-07-25, 1:40 pm

Hi. I have a dts package that loads data from a fixed length text file into
a table. I would like to insert the filename into a column in the table so
that I can track the records inserted from which source file. I have the
script to get the filename, but I don't know how to pass this information to
the data transform task into the "sourcefile" column. Can I assign it to a
global variable and use the variable as constant in the task?

Any help would be appreciated. Thanks.

-Terry

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...er-dts/200507/1

Allan Mitchell

2005-07-25, 8:29 pm

Ok So I presume you have a column in the destination to hold the variable
value.

I do not know how your transforms are set up but here is one way of doing
this and I do it all the time.

In the destination columns list in the transformations tab select the
destination column you want to be populated.

Create a new Active Script transform
In the properties of the transform make sure no source columns are chosen.

Now in the active Script itself you simply do this

DTSDestination("Column Name") = DTSGlobalVariables("Retrieved
Filename").Value

When you come back to the trasnformations tab you should see a transform
starting over the top of the source columns i.e. not attached and the
destination is your destination column.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Terry via webservertalk.com" <forum@webservertalk.com> wrote in message
news:51DC69AE35F40@w
ebservertalk.com...
>
> Hi. I have a dts package that loads data from a fixed length text file
> into
> a table. I would like to insert the filename into a column in the table
> so
> that I can track the records inserted from which source file. I have the
> script to get the filename, but I don't know how to pass this information
> to
> the data transform task into the "sourcefile" column. Can I assign it to
> a
> global variable and use the variable as constant in the task?
>
> Any help would be appreciated. Thanks.
>
> -Terry
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...er-dts/200507/1

Terry via SQLMonster.com

2005-07-26, 9:24 am

Allan,
Thanks for the help on this! This worked perfectly.

-Terry

Allan Mitchell wrote:[color=darkred
]
>Ok So I presume you have a column in the destination to hold the variable
>value.
>
>I do not know how your transforms are set up but here is one way of doing
>this and I do it all the time.
>
>In the destination columns list in the transformations tab select the
>destination column you want to be populated.
>
>Create a new Active Script transform
>In the properties of the transform make sure no source columns are chosen.
>
>Now in the active Script itself you simply do this
>
>DTSDestination("Column Name") = DTSGlobalVariables("Retrieved
>Filename").Value
>
>When you come back to the trasnformations tab you should see a transform
>starting over the top of the source columns i.e. not attached and the
>destination is your destination column.
>
>[quoted text clipped - 10 lines]

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...er-dts/200507/1

Sponsored Links