DTS runs fine from wizard, fail when scheduled

Question

DTS runs fine from wizard, fail when scheduled

mrlpgmr

SSC Enthusiast

Points: 179
More actions
January 13, 2004 at 2:07 pm

#59638

I created a simple DTS using the import/export wizard, it works pefectly except when scheduled. It take a text file created on drive C and dumps it into a one column table.
Any ideas??

Viewing 15 posts - 1 through 14 (of 14 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I have a couple of guesses, but can you post what goes wrong when you schedule the package? Do you get an error message?

My guesses:

1. The account that SQL Server Agent runs as doesn't have permission to open the text file.

2. The text file is on the c:\ drive of your workstation rather than on the server.

Greg

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Those are possiblities, but I would bet the farm that the real reason is that you do not have proper permissions.

One of the oddities of SQL Server is that it will let just about anyone create a job, but it has to run under an account with SA rights.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Per-Johan@oermen.dk Valued Member Points: 64 More actions · Answer 3

Are your version of the server and the wizard identical? - We have encountered problems scheduling packages created with newer wizards than the SQL server. The package is stored in the wizard version format, not the server version format.

Danny van Kent SSC Journeyman Points: 99 More actions · Answer 4

This is indeed likely user rights. When run trough the wizard it uses the access right of the user setting up the DTS

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

To find out if this is the case, try to open the package. If it is a version problem, the package most likely will not open. It will give you an error.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

mrlpgmr SSC Enthusiast Points: 179 More actions · Answer 6

Version in not a problem, I assume this because this is the only version of server and wizard ever installed, also the package does open

mrlpgmr SSC Enthusiast Points: 179 More actions · Answer 7

Iam assuming it is a permissions problem, however being an AS400 programmer I not certain how to correct this. I have tried to adjust permissions by setting a proxy account, following directions found in an earlier article on this site, no luck.

The file is on Drive c of the server, the only error message as follows:

[136] Job DidDraw reported: The process could not be created for step 1 of job 0xC3A75BD9AD66D0489677115DDCAE4807 (reason: The system cannot find the file specified)

amackay_1 Ten Centuries Points: 1240 More actions · Answer 8

Check to make sure that the account that the SQL Agent is set up to use has the System Administrator server role checked. When you are running the job manually it uses the permissions of the account that you are logged in under. But when it is scheduled it is using the permissions of the account that the SQL Agent is logged in under. If the SQL Agent's account already has SA rights then check to see what other differences there are between your login account and the SQL agent login account that could cause it not to be able to read the file.

fromnaija SSCrazy Points: 2876 More actions · Answer 9

Couple of things you should check:

1. When you create the package use UNC path to the text file instead of absolute path.

2. When you schedule the package change the job owner in the job properties to sa or similar login that has sysadmin rights. This is because a scheduled package requires execute rights to CmdExec.

3. If the text file is not on the SQL Server box then make sure that the account that starts SQL Server has access to the folder.

I hope this helps.

Joseph

Robert Davis One Orange Chip Points: 28027 More actions · Answer 10

This error can also be caused by creating a package with a client that has service pack 3 installed yet the server only has service pack 2 installed even though the versions are the same.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

sgordon Old Hand Points: 339 More actions · Answer 11

I had this same problem once and it ended up being we had an older version of MDAC on the server than I had on my Desktop where I created the DTS package.

Just something else to check....good luck.

nato Valued Member Points: 70 More actions · Answer 12

It is indeed a problem with different versions of MDAC. We had the same problem when we had MDAC 2.1 on the server and on the clients MDAC 2.6. There is an option in MDAC 2.6 concerning security that is not downwards compatible. Solutions are 1. make the package on the server and shedule it and 2. make the versions compatible. Good luck.

jose da fonseca

mrlpgmr SSC Enthusiast Points: 179 More actions · Answer 13

Thanks to everyone for the suggestions, I have made certian that every one has been corrected, but it is still failing

Robert Davis One Orange Chip Points: 28027 More actions · Answer 14

Just in case there's some incompatibility that we haven't thought of, I would suggest logging into the SQL Server and recreating the package from scratch and seeing how that works.

Otherwise, unless one of us can see the actual package, I don't know what else we can suggest.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]