MS SQL Server DTS - Dynamic DTS Task Challenge

Author

Dynamic DTS Task Challenge

dm4714

2005-05-17, 1:23 pm

I have a DTS Task that has the following:

* Dynamic Property
* SQL Task (Truncate a table in SQL)
* Connection1 (Input CSV file)
* Connection2 (Output SQL Server)

This tasks previously did not contain a Dynamic Property and, as you know,
the connection details and filename was hardcoded.

To remove the hardcoded values, I created a DTS.INI file that has
"Servername", "Username", "Password', and "Database" names, etc. under a
[DTS] section heading.

The Dynamic Property reads the DTS.INI file to obtain its values.

I have a SETUP program that will dynamically create the DTS.INI file at
installation and will install the DTS package within SQL Server.

My only opportunity now with the package is that the SQL Tasks has the
hardcoded name and SQL statement that includes the database name. Does
anyone know of an easy way to change this information?

Can I also use an INI file for this?

Any help would be appreciated.

Helge C. Rutz

2005-05-18, 3:23 am

Hi,

dm4714 wrote:
> My only opportunity now with the package is that the SQL Tasks has the
> hardcoded name and SQL statement that includes the database name.
> Does anyone know of an easy way to change this information?
>
> Can I also use an INI file for this?
e.g. you can read the name from ini file and assign it to a global variable.
Then use an activeX task to replace placeholders in the sql statements with this database name.

Helge

Darren Green

2005-05-18, 3:23 am

In message < 3ev5rkF54db3U1@indiv
idual.net>, Helge C. Rutz
< news050424@nurfuersp
am.de> writes
>Hi,
>
>dm4714 wrote:
>e.g. you can read the name from ini file and assign it to a global variable.
>Then use an activeX task to replace placeholders in the sql statements
>with this database name.
>
>Helge

This may help-

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Rodney Mullins

2005-05-19, 8:24 pm

I would also take a look at the best practice document put out by
Trey Johnson and Mark Chaffin.
http://msdn.microsoft.com/library/d...ntbpwithdts.asp
This document was written for SQL Server 2K. I'm sure there will be a new
doc when Yukon is released with Integration Services.

"dm4714" wrote:

> I have a DTS Task that has the following:
>
> * Dynamic Property
> * SQL Task (Truncate a table in SQL)
> * Connection1 (Input CSV file)
> * Connection2 (Output SQL Server)
>
> This tasks previously did not contain a Dynamic Property and, as you know,
> the connection details and filename was hardcoded.
>
> To remove the hardcoded values, I created a DTS.INI file that has
> "Servername", "Username", "Password', and "Database" names, etc. under a
> [DTS] section heading.
>
> The Dynamic Property reads the DTS.INI file to obtain its values.
>
> I have a SETUP program that will dynamically create the DTS.INI file at
> installation and will install the DTS package within SQL Server.
>
> My only opportunity now with the package is that the SQL Tasks has the
> hardcoded name and SQL statement that includes the database name. Does
> anyone know of an easy way to change this information?
>
> Can I also use an INI file for this?
>
> Any help would be appreciated.
>
>
>
>
>
>
>

Sponsored Links