Running SSIS package from a batch file 1

[Cpreston]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

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"

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

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

 

[Cpreston]

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