DTSRun error

Question

DTSRun error

@DJ

SSCommitted

Points: 1989
More actions
February 26, 2007 at 7:31 am

#116457

Hi
I have e DTS package set to ectract some dta from Lotus Notes to MS SQL 2000. If I run the dts package from enterprise manager or DTSRUNUI it works fine, but if I try to schedule the dts package it fails. I have tried to set up a job on my own with one singel job step refering to:
DTSRun /S "(local)" /N "[BPW_12_T][Valutakurser][LotusNotes]" /E
Doing it this way I get an error saying: Incorrect syntax near '/' ???? By the use of DTSRUNUI it generates a job that refer to the same DTSRun statement as above, though, it add`s a /G to the statement. Anyway runing it directly from DTSTUNUI works just fine, but schedule does noe work. That gives an error like:
Step 'DataTransferStep' failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
I can not figure out what it wants me to do, can anybody help with this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

The dts package from enterprise manager or DTSRUNUI it works fine, but if I try to schedule the dts package it fails.

This is quite common symptom when you put the dts into job. When your dts is running in the enterprise manager, it runs in your PC using your login credential but when it runs in the job it runs in the server using SQL-Agent login credential. I am not too certain what sort of process you have in your dts but if it tries to access files in the shared directory, then you need to give proper access permission to the SQL-Agent login for those files.

vadba SSChampion Points: 11133 More actions · Answer 2

Expanding on what Terry wrote, if you run the DTS package from a workstation, then that's where the drive mappings the package refers to come from. If you then schedule the DTS package, the drive mappings are those of the server. So if you wrote a DTS package from your workstation that refers to the C: drive, and then scheduled it, it would look at the servers C: drive instead.

Also, did you manually create the job? If so, make sure the job step type is "Operating System Command (cmdexec)", and not "Transact-SQL script (T-SQL)".

@DJ SSCommitted Points: 1989 More actions · Answer 3

Terry: "(..)then you need to give proper access permission to the SQL-Agent login for those files."

The notes database is on another server yes. I have set up an odbc and using this works just finE, I have now created a user that has the exact same name as the sql agent uses. sqlserv. Login on to win sql using notes odbc connection and this user works.

I can not figure out what I have to change to make things work. I realize that it is the SQL AGENT that is my showstopper, but I just do not see how.

mkeast: I created the job by scheduling it in Enterprise Manager. It has the correct job step type, "Operating System Command (cmdexec)"

I really hope somebidy can provide me with some hints.

vadba SSChampion Points: 11133 More actions · Answer 4

Let's verify the environment. You've got SQL Server running on a server, and you've created a DTS package the runs successfully from Enterprise MAnager on your workstation. Then, you scheduled to the DTS package to run at a particular time, and you get the error message. Inside the DTS package, you are importing data from Lotus Notes. Is the the Lotus Notes data in a file on your workstation (a local replica, a .nsf file)? If not, where is the Lotus Notes data located? What driver are you using to connect to the Lotus Notes datasource, and where did you get it from?

@DJ SSCommitted Points: 1989 More actions · Answer 5

argggghh, I was sitting here minding my own business and writing a reply to your post and pushed post reply, when it all went down the drain... Damn...

I will try again...

The Notes data is on a nsf file. The nsf file is located on a different server then the ms sql server. I use a driver donwloaded from notes homepage, Notes SQL 3.02j. As mentioned this part works just fine.

I read that a user that wants to connect and get data from other servers have to be apart of the sysadmin role.

http://support.microsoft.com/?id=269074 I used EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin' to mange this.

Still, that did not solve my problem.

I belive it has to got the sql agent user who has some sort of a problem. Wwhen I start DTSRUNUI and try to access the DTS package it fails, claiming that the login fails. I tested both username and password by loging on to the same database with WIN SQL. No. problem What kind of login problem can stop the same user from using DTSRUNUI?

I have been fighting with this issue for quite some time now, and now I am getting abit frustrated......

vadba SSChampion Points: 11133 More actions · Answer 6

Did you add 'Corporate\HelenS' to the sysadmin role because that login id is used to run SQL Agent? To schedule the job, you'll need to make sure that the account use to run SQL Agent has all the necessary permissions and drive/server access.

Unfortunately, we also use Domino/Lotus Notes here at work. I downloaded the driver and briefly played around with it. From what I can tell, in order to schedule the DTS package, the SQLNotes driver needs to be installed on the server so that the SQL Agent account has access to it. I tried that, but it appears to require that Lotus Notes be installed first. So, I can't test that.

I'll look into it further when I get a chance.