DTS Package Help

[Shals]

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

[DFS]

Shals wrote:

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software on
the PC, and in your Access file set a reference to the DTS Runtime (this is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password", ,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.

[Rick Brandt]

Shals wrote:

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

There is likely a command for running the package directly but what we do is
create an Agent job to execute the DTS package and then use a passthrough
query to send the command to the server to run the Agent job. That provides
for an asynchronous operation where Access does not need to wait for the
task to complete.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Terry Kreft]

You can do this directly from the SP as well using DTSRun.

If you use DTSRunUI and fill in the details correctly, then click on the
Advanced button and then the Generate button on the new screen it will give
you the command line to run the package using DTSRun, which you can then
just copy and paste.

--

Terry Kreft
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:2Q******************@newssvr27.news.prodigy.n et...

Shals wrote:

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

There is likely a command for running the package directly but what we do

is

create an Agent job to execute the DTS package and then use a passthrough
query to send the command to the server to run the Agent job. That

provides

for an asynchronous operation where Access does not need to wait for the
task to complete.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Shals]

I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser",
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServer command as it requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.
DFS wrote:

Shals wrote:

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software on
the PC, and in your Access file set a reference to the DTS Runtime (this is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password", ,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.

['69 Camaro]

Hi.

requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.

Sorry. If you don't have the user name and password, we have to assume it's
because you aren't authorized to have them. We can't help you hack into someone
else's SQL Server database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

>I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser",
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServer command as it requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.
DFS wrote:

>Shals wrote:

Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software on
the PC, and in your Access file set a reference to the DTS Runtime (this is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password", ,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.

[Shals]

It's not like that, but it's the company policy that networking people
maintain the servers so they have given us access thru windows login to
the servers but we need SQL Server password right?

Besides I have created the DTS on SQL Server only and my Access
Application is connecting to SQL Server thru connectionstring which
uses ODBC connection.

Can I use ODBC DataSource name in parameters for LoadFromSQLServer
Command?

'69 Camaro wrote:

Hi.

requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.

Sorry. If you don't have the user name and password, we have to assume it's
because you aren't authorized to have them. We can't help you hack into someone
else's SQL Server database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser",
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServer command as it requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.
DFS wrote:

Shals wrote:
Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software on
the PC, and in your Access file set a reference to the DTS Runtime (this is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password", ,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.

['69 Camaro]

Hi.

it's the company policy that networking people
maintain the servers so they have given us access thru windows login to
the servers but we need SQL Server password right?

Probably not. There are two ways to authenticate, either through Windows NT
authentication (the secure way) or through SQL Server authentication. The SQL
Server DBA most likely set it up for you to only authenticate with Windows NT
authentication. For Windows NT authentication, use empty strings for user name
and password parameters in LoadFromSQLServer( ). Otherwise, you should use your
SQL Server login credentials. If your DTS package has a password, you must use
that as one of the parameters.

Syntax:

Sub LoadFromSQLServer(ServerName As String, [SQLServerUserName As String],
[SQLServerPassword As String], [Flags As DTSSQLServerStorageFlags =
DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String],
[PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

It's not like that, but it's the company policy that networking people
maintain the servers so they have given us access thru windows login to
the servers but we need SQL Server password right?

Besides I have created the DTS on SQL Server only and my Access
Application is connecting to SQL Server thru connectionstring which
uses ODBC connection.

Can I use ODBC DataSource name in parameters for LoadFromSQLServer
Command?

'69 Camaro wrote:

>Hi.

requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.

Sorry. If you don't have the user name and password, we have to assume it's
because you aren't authorized to have them. We can't help you hack into
someone
else's SQL Server database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@j72g2000cwa.googleg roups.com...

>I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser",
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServer command as it requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.
DFS wrote:
Shals wrote:
Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software
on
the PC, and in your Access file set a reference to the DTS Runtime (this
is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password",
,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.

[Shals]

Thanks for suggestions,

i got it working by just giving the DTSSQLServerStorageFlags value =
256 for windows authentication from trusted connection.

the value is 0 for the flag if you have server username and password
value with you.

So now the DTS is working
Thanks a lot.
'69 Camaro wrote:

Hi.

it's the company policy that networking people
maintain the servers so they have given us access thru windows login to
the servers but we need SQL Server password right?

Probably not. There are two ways to authenticate, either through Windows NT
authentication (the secure way) or through SQL Server authentication. The SQL
Server DBA most likely set it up for you to only authenticate with Windows NT
authentication. For Windows NT authentication, use empty strings for user name
and password parameters in LoadFromSQLServer( ). Otherwise, you should use your
SQL Server login credentials. If your DTS package has a password, you must use
that as one of the parameters.

Syntax:

Sub LoadFromSQLServer(ServerName As String, [SQLServerUserName As String],
[SQLServerPassword As String], [Flags As DTSSQLServerStorageFlags =
DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String],
[PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

It's not like that, but it's the company policy that networking people
maintain the servers so they have given us access thru windows login to
the servers but we need SQL Server password right?

Besides I have created the DTS on SQL Server only and my Access
Application is connecting to SQL Server thru connectionstring which
uses ODBC connection.

Can I use ODBC DataSource name in parameters for LoadFromSQLServer
Command?

'69 Camaro wrote:

Hi.

requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.

Sorry. If you don't have the user name and password, we have to assume it's
because you aren't authorized to have them. We can't help you hack into
someone
else's SQL Server database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"Shals" <s_******@berkeley.eduwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser",
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServer command as it requires Username and Password. I don't
know what username and password to enter.

can't we override these parameters.
DFS wrote:
Shals wrote:
Hi,

I have a DTS Package created in SQL Server but the client wants to
execute the DTS package from within MS Access by clicking a button.
If any one can tell me how to execute that DTS from with in MS
Access(VBA Code)

any suggestions would be helpful.
Thanks,
Shal

Shal,

I did it a while back. It was very easy, too. I don't have any DTS
packages to test, but as I recall you need to install DTS client software
on
the PC, and in your Access file set a reference to the DTS Runtime (this
is
done in the VBA module editor screen). Here's the code I used:
=============================================
Dim yieldMgmtDTS As New DTS.Package

'pseudo-code
DTSObject.Method database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.LoadFromSQLServer "yieldMgmtDB", "yieldMgmtUser", "password",
,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Execute
Set yieldMgmtDTS = Nothing
MsgBox "Finished"
=============================================
You'll need to add error trapping, and figure out a way to validate that
your DTS package executed properly

Also ask on the comp.databases.ms-sqlserver group if this doesn't get you
going.