Hello!

How can i change the source and the target of a "transform data task"
in a named DTS-Package via StoredProcs "sp_OA*" within T-SQL?

Thanks a lot...


chris

Depends on how you have implemented them.

By far the easiest method is to make the package do the work. Have it read
from an INI file or a DB table but make the package do that. All the sp_OA*
does is run the package.

If you want you can assign the connections to be the values of Global
Variables in the package. In sp_OA* you will need to grab the
GlobalVariables Collection.

You can also manipulate the object model and fire it that way. You would
accesss the Connections collection.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Christian Weber" <abc (AT) def (DOT) de> wrote

Thank you for your answer.

I have a SQLServer connection and a MSAccess conenctionin the DTS-Package.
How can i access the properties (for example to query the Initial catalog)
via sp_OAGetProperty?

greets

chris

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> schrieb im Newsbeitrag
news:%23UY%23SwnjDHA.2216 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Here is how you would populate a GV through the object model in sp_OA*

EXEC @hr = sp_OASetProperty @DTSPkg,
'GlobalVariables("Source_ID").Value',@GV_IN_Source _ID


All you need to do is GET the right connection. Get it's Catalog Property



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Christian Weber" <abc (AT) def (DOT) de> wrote

Hello,
i have a construct like this:

exec sp_OAGetProperty @pkg, 'Connections("server1")',@conn OUT
exec sp_OAGetProperty @conn,'Catalog',@datasource OUT

where @pkg,@conn and @datasource ar integer.

The first statement works, but on execting the second i get a "type
conflict" error.

greets

chris

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> schrieb im Newsbeitrag
news:unZ74CzjDHA.1676 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

The value of the Catalog property - try changing to be a character datatype

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Christian Weber" <abc (AT) def (DOT) de> wrote