INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Running SSIS package from a batch file

Running SSIS package from a batch file

(OP)
Hi

I have a SSIS package which I can run from a batch file with no issue. If another user runs it the package fails to run. Despite many google attempts I cannot find a way to run the batch file for any user. I know it is a permission problem but I have added other users into the file paths involved and still no luck. Any ideas anyone please, the code is below which runs for me.


"\\Server123\c$\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\SSIS Projects\Export Instructions\ExportbookinginstructionsLIVE\ExportBookingInsturctionslive\exportlive.dtsx"

RE: Running SSIS package from a batch file

you can not do that.
dtexec is a licensed tool from SQL Server and required a paid for license to be executed on each pc where it is executed, and also means that the clients pc´s require the instalation of SQL Server componentes for the executable and its libraries to be available through the command line.

What you can do is setup sql server agent jobs where one step is the execution of the ssis package, and grant permissions to the users to execute the job. The job execution can be done through a sqlcmd script and that you can do with the basic sql client installs / .net installs.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Running SSIS package from a batch file

(OP)
Hi

Sorry should have explained. This is run on the SQL server so it is run where all the license and software is required to run such things.
We remote desktop onto it and of course certain users only have certain permissions. Therefore I can log in and run the batch file, but another user with less permissions cannot run it. I need the ability to run it with permissions already granted in some way.

Thanks

RE: Running SSIS package from a batch file

Then do not use the share to execute dtexec.

If everyone is executing on the server then just use c:\....\dtexec

And what errors are you getting when the users try to execute? give us this after you change the command to reference the local drive, not the share

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Running SSIS package from a batch file

(OP)
Hi

I have changed the code as below, again it runs fine for myself, as soon as I log in as the other user it does not work.
The batch files runs and vanishes off screen, but the file creation is not updated, the time (date modified) does not change. It does when I run it as myself. There are no other messages. Thanks


"c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\SSIS Projects\Export Instructions\ExportbookinginstructionsLIVE\ExportBookingInsturctionslive\exportlive.dtsx"

RE: Running SSIS package from a batch file

redirect the output to a text file and you will have some logging - add /rep ewidpv to the command line

you most likely have permissions on server or share level and without logging you will never find it.

and as batch files go when testing always add a pause command to it to see the output - and execute it on the command line, not through windows explorer

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Running SSIS package from a batch file

(OP)
Hi

Managed to get some report back when ran as the user. Where to put it right is something else. The SSIS is looking at the Server1.Live Database

Started: 10:15:37
Progress: 2017-08-04 10:15:37.12
Source: Data Flow Task
Validating: 0% complete
End Progress
Error: 2017-08-04 10:15:37.17
Code: 0xC0202009
Source: Exportlive Connection manager "Server1.Live database"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0
" Hresult: 0x80040E4D Description: "Login failed for user 'IT_Domain\Bbevan'.".
End Error

RE: Running SSIS package from a batch file

(OP)
Hi

Ok got it sorted. I needed to give the user permission to the live database on the live server. After this the batch file ran no problems.

Thanks for the advise, ruining it form the DOS prompt told me what I needed to know

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close