SSIS Packages and SQL Agent
-
I have successfully created SSIS package and the packages work great as long as I am in the gui environment. When I try to call the command string through DTEXEC or thru the SQL Agent. It can not find the unc file that I want to import. I know I probably have a permission not set upcorrectly on SQL server but do not know what the setting should be.
Any help would be apprecated.
-
You have to deploy the package to the server where Integration Services is running. You can do that several ways. My favorite is to save a copy of the SSIS package to SQL Server.
Once saved, you can then schedule it using SQL Server Agent Job and specifying where you saved the package.
The other option is to just copy the .dtsx file to a local drive on the server. Then, you can schedule using either SQL Server Agent or Task Scheduler or some other option.
And finally, you can create a deployment package that you use to deploy. When you run that, it will prompt for all of the information you need to deploy the package to a server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank You for the response and I follow exactly what you said and the package will work if I run it under integration Services GUI and it the package is stored on the server. When I try to execute the command structure of the DTS package in SQL Agent I get a syntax error.
"Inncorrect syntax near '/" [sqlstate 4200][Error 102]"
The Actual command line in the job:
/DTS "\File System\SSIS_import\allsois" /SERVER A4012937 /CHECKPOINTING OFF /REPORTING V
Any help would be appreciated.
-
Okay, you are storing the package at the file system - not in SQL Server itself. The command line looks okay - not sure what the problem is there.
What do you mean Integration Services GUI - I am not familiar with that term. Are you talking about running the package in Visual Studio (Business Intelligence Development Studio)? Or, are you talking about something else...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Yes I can run the package from business intelligent studio or I can run it from integration Servives from with in SSMS. In both cases it works greate with no Errors. when I try to run it in a strored procedure with DTEXEC or from SQL Agent I get the error where it can not find my unc connected file.
-
Steven.g.karpowicz (10/27/2009)
Yes I can run the package from business intelligent studio or I can run it from integration Servives from with in SSMS. In both cases it works greate with no Errors. when I try to run it in a strored procedure with DTEXEC or from SQL Agent I get the error where it can not find my unc connected file.Okay - I think I know what the problem is now. When you use SSMS to connect to Integration Services, the package is going to run on the machine where you have opened SSMS. Additionally, the package is going to use the credentials of the user logged into that machine to access network resources.
So, if you are RDP'd into the server or running SSMS from your desktop - the credentials being utilized are the yours. You have access to the UNC location where the package is stored and it runs as expected.
When you run it from SQL Server Agent or from a stored procedure - the user context is the context of the user that is running SQL Server (or the Agent). Since that user does not have access to the UNC where the package is stored - it fails.
Grant access to the share to the service account that is running SQL Server and SQL Server Agent and it should work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
In the Scheduled Job, Look at the Login it is using under Run as Column.
And see that login has admin level rights on that Instance.
Edited --- Jeffrey has already posted the same, at the time I started to reply the post, it was not there.
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
-
I guess I am confused on permissions. I think you hit the problem on the head however I am the user for SQL SERVER AGEnt so I should be the login their. As far as SQL server I believe I set it up as NT user id to log on. but from what I can tell I granted myself all the permissions.
I do not know where to grant my user ID additional permissions.
-
Steven.g.karpowicz (10/27/2009)
I guess I am confused on permissions. I think you hit the problem on the head however I am the user for SQL SERVER AGEnt so I should be the login their. As far as SQL server I believe I set it up as NT user id to log on. but from what I can tell I granted myself all the permissions.I do not know where to grant my user ID additional permissions.
SQL Server and Agent run as services. Check the logon properties of the service to identify what account they are running under.
I really don't think they are running under your user account - if they were, you wouldn't have any issues with accessing that file share. Most likely, they are running as Local System accounts which won't have access to the network share. You would have to grant access to the computer for that to work - but it is not recommended.
You need to create a domain user to run SQL Server - and grant that domain user access to the file share. Once you have that created, use SQL Server Configuration Manager to change the logon properties - as that will make sure the account is added to all of the right groups on the server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I am actually running under Network Services and not Local Systems account. It takes my Id which has permissions to the network servers. My thinking is that I need something more since I still get the error message and like you said I need to set up a Domain account. I thought that my network account was my Domain account, but apparently not.
-
Look into setting up a proxy account for SSIS this should help fix your problem. It will allow you to run the SSIS package under a different credential. A 5 second google found this tutorial that may help you:
Proxy-Accounts-in-SQL-Server.htm
Hope this helps,
Rich[p]
[/p] -
Thank You for your help that solved the problem.
-
Steven.g.karpowicz (10/28/2009)
I am actually running under Network Services and not Local Systems account. It takes my Id which has permissions to the network servers. My thinking is that I need something more since I still get the error message and like you said I need to set up a Domain account. I thought that my network account was my Domain account, but apparently not.It is not recommended that you run under the network services account. Microsoft recommends running under a domain user account (not an admin account).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply