Hi, all!
I need to load base from ODBC-source into MSSql base using MsSql DTS at my customer's side with
automatic as much as possible('Pushing 1 buton').
I have found such an example:
DECLARE @object int
DECLARE @hr int
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0 BEGIN
print 'error create DTS.Package'
RETURN -1
END
EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile',
NULL, 'C:\Test.dts', ''
IF @hr <> 0 BEGIN
print 'error LoadFromStorageFile'
RETURN -2
END
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0 BEGIN
print 'Execute failed'
RETURN -3
END
It works for my DTS-file. But I need change programmatically connection parameters, login and password of connection,
not use connection parameters that I set manually in my DTS-Package.
In this packete as a source of datas "Connection Properties" with ODBC DataSource as source of my datas,
it's login and password.
Then destination is object "Connection Properties" with DataSource Microsoft Ole DB tuned to base I need,
login and password. there are about 20 objects "Transform Data Task". In doc I found function
sp_OAMethod with method "Connect":
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server', 'my_login', 'my_password'
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
But how can I do with it as I have two bases: source and destination?
Thanks in advance,
Nilov Serge.