MS SQL Server DTS - Pass connection parameter to DTS

Author

Pass connection parameter to DTS

blue_nirvana

2006-01-24, 11:23 am

Using SQL Server 2000, I have a DTS job that imports data from a text file
that uses 2 connections and a transform data task. Obiviously the first
connection is the text file and the other is the SQL Server database. The
problem is that the text file will not always be in the same directory and so
I want to pass in the directory location to the DTS job so that it knows
where to grab the text file. Does anybody have any good examples or links or
how to do this?

Thanks

Darren Green

2006-01-24, 8:24 pm

blue_nirvana wrote:
> Using SQL Server 2000, I have a DTS job that imports data from a text file
> that uses 2 connections and a transform data task. Obiviously the first
> connection is the text file and the other is the SQL Server database. The
> problem is that the text file will not always be in the same directory and so
> I want to pass in the directory location to the DTS job so that it knows
> where to grab the text file. Does anybody have any good examples or links or
> how to do this?
>
> Thanks

Look up the Dynamic Properties Task, Global Variables in Books Online.

Also look up the /A parameter for DTSRUN.

Some articles on connections, including managing them-

Connections
(http://www.sqldts.com/default.aspx?101)

--
Darren
http://www.sqldts.com
http://www.sqlis.com

blue_nirvana

2006-01-24, 8:24 pm

Thanks Darren, but I just decided to create and execute the DTS job in
VB.NET. That way it is easy for me to control the datasource for that
connection. Also, I don't have to worry about passing a parameter. It
surprising works really well and ran very quickely.

If there are any concerns that I should know about doing it that way, please
let me know.

Thanks again

"Darren Green" wrote:

> blue_nirvana wrote:
>
>
> Look up the Dynamic Properties Task, Global Variables in Books Online.
>
> Also look up the /A parameter for DTSRUN.
>
>
> Some articles on connections, including managing them-
>
> Connections
> (http://www.sqldts.com/default.aspx?101)
>
> --
> Darren
> http://www.sqldts.com
> http://www.sqlis.com
>

Darren Green

2006-01-26, 5:16 pm

blue_nirvana wrote:
> Thanks Darren, but I just decided to create and execute the DTS job in
> VB.NET. That way it is easy for me to control the datasource for that
> connection. Also, I don't have to worry about passing a parameter. It
> surprising works really well and ran very quickely.
>
> If there are any concerns that I should know about doing it that way, please
> let me know.
>

Just be aware that the package runs on the same machine as the VB.Net
app, and under tech security context of the user running the
application. Jobs are often used to help abstract as they use the SQL
Server Agent service account, or a proxy account, and execute on the server.

--
Darren
http://www.sqldts.com
http://www.sqlis.com

Sponsored Links