DTS does not find the table...

Question

DTS does not find the table...

Scotty-196944

SSC Enthusiast

Points: 158
More actions
March 10, 2006 at 11:03 am

#116429

I'm stuck... Got a DTS pkg that have deployed to our prod environment. It is very complex with many steps between several servers. Many of these steps are executing against a central Accounting db. They all execute fine, except one. That one step for some reason throws an error saying that my Recevables table does not exist. There are other steps in the pkg using the same table and connection, and execute fine. Also, if I open the step and preview, it works fine. However, when it executes it fails. However, when i delete and recreate the step on the production server, it begins working. I've checked all connection and login info, but have found no answers.
Any ideas on the problem or suggestions on finding the problem would be very appreciated.

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

It may happen if your step is trying to connect as a different user. Or all other steps use a qualified name like dbo.mytable and this step says "mytable". When you pre-view it may execute as your current login, but being a part of tha package it would execute as someone else. When re-created it would have an owner who was a current connection in Enterprise Manager when re-creating a package and will work.

SQL Server searches for the current user's table then for dbo's table then if it does not find any says that the object does not exist.

Also it may be if the database name is not defined in the connection string it would connect to the default user's database. If this step is trying to connect as another user, it can not find a table in the different database

Regards,Yelena Varsha

Scotty-196944 SSC Enthusiast Points: 158 More actions · Answer 2

I had the same basic thoughts... Looked into all that, but with no success.

Thanks for the reply though.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Look, you may re-create a step when connecting as a user #3 and then use profiler to see who is trying to connect when you are excuting a step. Could be surprises. I did have one case when my DTS package that I created when connected in EM as SA and that did NOT contain any SA authentication or SA ownership did not execute when I changed SA password.

I was able to reproduce the issue but was not able to find out what was the reason. Sounds similar to your case.

Regards,Yelena Varsha

Mubeen Mohammed-203067 SSC Eights! Points: 877 More actions · Answer 4

My response is in reference to Yelena Varshal, I am not an expert in DTS Packages but I faced the same problem when I changed sa password. All of my DTS packages got failed after changing the 'sa' password. When you create a DTS Package, it uses the current login thru which you logged. So it uses 'SA' login.

If you edit a STEP, you see the command as DTSRun .../.../ and everything is in encrypted format. So internally it encrypts sa login and it's password. To overcome of this issue, I used DTSRunUI tool and generated encrypted code for DTSRun and copied in the STEP command. Then it got executed. So it is some kind of mess. But indirectly we can solve this problem. My recommendation is not to use 'sa' login for any DTS Packages. Better you use any seperate login which has SysAdmin Role.

Thanks

Mubeen

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Mubeen,

Thanks!

I don't run ANYTHING on SA password, except for ocasional work in EM as myself. I looked up my notes related to the described problem that happened 2 years ago, and notes actually contained the fix: to add /E (integrated) to the DTSrun:

My Notes of 2004:

"My DTS (myJob) job does not run due to Login Failed for user sa since March 10 when we changed sa password. There is NO sa authentication anywhere in the DTS package, job or startups for SQL Server and Agent. Proxy account should not work because "allow only member of sysadmins to run CmdExec jobs" is checked

I fixed my problem, but I still don't know if it is a bug

I ran Profiler, did not help.

I ran a package by itself without a job, it ran OK

The job step is just

DTSRun <package number>

I tried to execute this statement in the Command Prompt and got the same "Login Failed for user sa" error.

I then added /E (integrated authentication) at the end of the line and it worked. So I modified the job steps of those 2 jobs adding /E. They look like working now.

So: I know where it was wrong, I know how to fix it, I just don't know what caused the error.

There is a very good article that explains all aspects and details in regards what identity is used when package runs in every possible case. It does not explain my case, I suppose.

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

INF: How to Run a DTS Package as a Scheduled Job"

Regards,Yelena Varsha

Scotty-196944 SSC Enthusiast Points: 158 More actions · Answer 6

We've already got a new pkg to deploy, and am going to see if the problem arises again. Also, I'm going to run profiler, if the error ocurrs, and see if I can get more information.

Thanks for your help, I'll let you know, if I do figure out anything.