Susan Geller
sgeller at cce.umn.edu
Thu Jun 5 11:21:39 CDT 2003
Mark, We were in Culdaff!!!! It is soooo beautiful there. The day we were there it was in the low 20's and sunny and the beach had only a very few people. We did go to McGrory's but there wasn't music that night. Culdaff was one of my favorite places. Other favorites were Horn Head and Slieve League. We didn't go to Carrick-On-Shannon or Boyle, but I remember them both from the map. I'm sorry to have missed you in Co. Kildare. We intend to go back b/c we didn't have nearly enough time and we'll visit then. People were exceptionally nice and the roads were a nightmare. We drove a LOT and did get used to it a bit, but we never relaxed and we were happy to get back to our roads where the lanes are wider, there are more of them, and people drive at reasonable speeds and pass with a bit more caution. But, the roads in Ireland are far more scenic!!! --Susan -----Original Message----- From: Mark L. Breen [mailto:subs at solution-providers.ie] Sent: Thursday, June 05, 2003 10:09 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer]Execute DTS from VBA 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 > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com