[dba-SQLServer]Execute DTS from VBA

Mark L. Breen subs at solution-providers.ie
Thu Jun 5 10:09:12 CDT 2003


Hello Susan,

Firstly, I am sorry that I did not know that you were in Ireland, I would
have invited you and the previous Mr Gellerman to Co. Kildare.

Donegal is beautiful, I love it, they say up there, if you do not like the
weather, wait five minutes, 'cause it changes that fast.  My brother is
married to a girl from Culdaff, about 3 miles south of the most northly
point in Ireland.  I hope that the people were nice to you and that you
could handle the roads and the tiny cars we all drive over here.

The only reference in relation to DTS is MicrosoftDTSPackageLibrary, as you
have.

If you do not set the UseTrustedConnection bit, you have to mess around with
the username and password of the package, not what I wanted to have to
bother with.  I gave no consideration to permissions after that, I just
relied on the OpSys security, I do not think that the user needs to be an
SA, but perhaps you have to set some security rights for the user or group.
I have not done it yet, but I do not think that it is too big a deal.

You can get away with one execution without uninitialize, but once I put it
in I could do multiple, I still get the failures, and I am still considering
doing it manually, but as I said, I would prefer not to.

Keep in touch with it, you are the only person I have ever communicated with
that uses DTS since SQL 7 was born!  Maybe I just lead a sheltered life.

The Shannon to Donegal is great, did you go to Carrick-On-Shannon or even a
village named Boyle ?  In fact, did you go to Culdaff?

Mark



----- Original Message ----- 
From: "Susan Geller" <sgeller at cce.umn.edu>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Thursday, June 05, 2003 2:32 PM
Subject: RE: [dba-SQLServer]Execute DTS from VBA


> Mark,
>
> Your code worked!  I just made a really simple DTS to test it out with.
> It seems to work about 75% of the time.  I'm not at all sure what is
> different the other 25% of the time.  It may be the quick succession
> thing that you mentioned.  I'll need to play with this a bit more.
>
> A couple of questions:
>
> 1.  What references do you have set for DTS?  I just set
> MicrosoftDTSPackageLibrary.
>
> 2.  What does "DTSSQLStgFlag_UseTrustedConnection" do? And, related to
> that, what do I need to consider about permissions?  I want users to be
> able to execute the DTS, but they don't have SA access to the Server the
> way I do.
>
> On a OT note, I just got back from 9 days of honeymooning in Ireland.
> What a beautiful country.  We spent most of our time in County Donegal,
> but saw a lot of the west coast on our drive up from Shannon to Donegal.
> Where do you live?
>
> --Susan
>
>
> -----Original Message-----
> From: Mark L. Breen [mailto:subs at solution-providers.ie]
> Sent: Thursday, June 05, 2003 3:25 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer]Execute DTS from VBA
>
>
> Hello Susan,
>
> Have a look at the following lines of code
>
>             Dim objPackage2 As New DTS.Package
>             objPackage2.LoadFromSQLServer ".", , ,
> DTSSQLStgFlag_UseTrustedConnection, , , , "NEWSPPSPackage"
>             objPackage2.FailOnError = True
>             objPackage2.Execute
>             objPackage2.UnInitialize
>             Set objPackage2 = Nothing
>
>
> They are taken from a system that I have just finished, they are
> working. It is important to use the uninitialize, otherwise, you cannot
> reuse the package.
>
> One word of warning, I am sometimes finding that it does not like being
> used in quick sucession, but only sometimes, I cannot figure out why if
> fails when it does.  Other times, it will import five and ten files
> rapidly in quick sucession.
>
> I was considering rewriting the code to manually handle the file that I
> am importing on a line by line basis, but I feel that DTS should be the
> way to go, otherwise why would MS have created it in the first place.
> But then again, you could say that about the fax facility in Windows 95.
>
> Let me know what you decide to do, I would be interested.
>
> Thanks
>
> Mark Breen
> Ireland
>
>
>
>
> ----- Original Message ----- 
> From: "Susan Geller" <sgeller at cce.umn.edu>
> To: <dba-SQLServer at databaseadvisors.com>
> Sent: Wednesday, June 04, 2003 2:22 PM
> Subject: [dba-SQLServer]Execute DTS from VBA
>
>
> > Can I execute a DTS from inside my ADP file in code?  How?
> >
> > Access XP, Windows XP, SQL Server 2000.
> >
> > Thanks.
> >
> > --Susan
> >
> >
> > Susan B. Geller
> > Office of Information Systems
> > College of Continuing Education
> > University of Minnesota
> > 306 Wesbrook Hall
> > 77 Pleasant Street SE
> > Minneapolis, MN 55455
> > Phone:  612-626-4785
> > Fax:  612-625-2568
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>




More information about the dba-SQLServer mailing list