DTS Package Help

**DFS** · Dec 8 '06, 03:35 AM

Re: DTS Package Help

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.Metho d database, userID, password, , , , , DTS package name

'example
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" , "password", ,
, , , "Calc New Domestic CityPairs"

yieldMgmtDTS.Ex ecute
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** · Dec 8 '06, 01:15 PM

Re: DTS Package Help

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** · Dec 8 '06, 02:45 PM

Re: DTS Package Help

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" <rickbrandt2@ho tmail.comwrote in message
news:2Qdeh.1167 6$Py2.7505@news svr27.news.prod igy.net...

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** · Dec 14 '06, 10:29 PM

Re: DTS Package Help

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

yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" ,
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServ er 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.Metho d database, userID, password, , , , , DTS package name
>
'example
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" , "password", ,
, , , "Calc New Domestic CityPairs"
>
yieldMgmtDTS.Ex ecute
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** · Dec 14 '06, 10:29 PM

Re: DTS Package Help

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_wadhwa@berke ley.eduwrote in message
news:1166045330 .834408.111000@ j72g2000cwa.goo glegroups.com.. .

>I tried using this code in my Access application but I got stuck at
>
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" ,
"password", ,
, , , "Calc New Domestic CityPairs"
>
LoadFromSQLServ er 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.Meth od database, userID, password, , , , , DTS package name
>>
>'example
>yieldMgmtDTS.L oadFromSQLServe r "yieldMgmtD B", "yieldMgmtUser" , "password", ,
>, , , "Calc New Domestic CityPairs"
>>
>yieldMgmtDTS.E xecute
>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** · Dec 14 '06, 10:29 PM

Re: DTS Package Help

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 connectionstrin g which
uses ODBC connection.

Can I use ODBC DataSource name in parameters for LoadFromSQLServ er
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_wadhwa@berke ley.eduwrote in message
news:1166045330 .834408.111000@ j72g2000cwa.goo glegroups.com.. .

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

yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" ,
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServ er 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.Metho d database, userID, password, , , , , DTS package name
>
'example
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" , "password", ,
, , , "Calc New Domestic CityPairs"
>
yieldMgmtDTS.Ex ecute
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** · Dec 14 '06, 10:29 PM

Re: DTS Package Help

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 LoadFromSQLServ er( ). 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 LoadFromSQLServ er(ServerName As String, [SQLServerUserNa me As String],
[SQLServerPasswo rd As String], [Flags As DTSSQLServerSto rageFlags =
DTSSQLStgFlag_D efault], [PackagePassword As String], [PackageGuid As String],
[PackageVersionG uid As String], [PackageName As String], [pVarPersistStgO fHost])

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_wadhwa@berke ley.eduwrote in message
news:1166052194 .450546.135970@ 73g2000cwn.goog legroups.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 connectionstrin g which
uses ODBC connection.
>
Can I use ODBC DataSource name in parameters for LoadFromSQLServ er
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_wadhwa@berke ley.eduwrote in message
>news:116604533 0.834408.111000 @j72g2000cwa.go oglegroups.com. ..

>I tried using this code in my Access application but I got stuck at
>
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" ,
"password", ,
, , , "Calc New Domestic CityPairs"
>
LoadFromSQLServ er 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.Meth od database, userID, password, , , , , DTS package name
>>
>'example
>yieldMgmtDTS.L oadFromSQLServe r "yieldMgmtD B", "yieldMgmtUser" , "password",
>,
>, , , "Calc New Domestic CityPairs"
>>
>yieldMgmtDTS.E xecute
>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** · Dec 14 '06, 10:29 PM

Re: DTS Package Help

Thanks for suggestions,

i got it working by just giving the DTSSQLServerSto rageFlags 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 LoadFromSQLServ er( ). 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 LoadFromSQLServ er(ServerName As String, [SQLServerUserNa me As String],
[SQLServerPasswo rd As String], [Flags As DTSSQLServerSto rageFlags =
DTSSQLStgFlag_D efault], [PackagePassword As String], [PackageGuid As String],
[PackageVersionG uid As String], [PackageName As String], [pVarPersistStgO fHost])
>
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_wadhwa@berke ley.eduwrote in message
news:1166052194 .450546.135970@ 73g2000cwn.goog legroups.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 connectionstrin g which
uses ODBC connection.

Can I use ODBC DataSource name in parameters for LoadFromSQLServ er
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_wadhwa@berke ley.eduwrote in message
news:1166045330 .834408.111000@ j72g2000cwa.goo glegroups.com.. .
I tried using this code in my Access application but I got stuck at

yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" ,
"password", ,
, , , "Calc New Domestic CityPairs"

LoadFromSQLServ er 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.Metho d database, userID, password, , , , , DTS package name
>
'example
yieldMgmtDTS.Lo adFromSQLServer "yieldMgmtD B", "yieldMgmtUser" , "password",
,
, , , "Calc New Domestic CityPairs"
>
yieldMgmtDTS.Ex ecute
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.

DTS Package Help

DTS Package Help

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment