Allowing users to run SSIS packages remotely

Question

Allowing users to run SSIS packages remotely

Maddave

SSCertifiable

Points: 6828
More actions
March 12, 2015 at 10:51 am

#301958

I am quite new to SSIS but managed to build a package which imports text files in to SQL. The text files are generated after users complete a manufacturing process on a machine.
The SSIS package is stored in the SSIS catalog and currently a SQL Agent tasks runs every evening to import new files that have been created during the day. Users have now requested the ability to run the import process as soon as they have finished their manufacturing runs as they may want to query the data to looks up stats etc.
What is the best way to do this considering all of the users are not SQL guys and wont have direct logins into the SQL Server or access to SQL Server Management studio. They will have access to the PC where the files are generated, so I ideally I need a batch file which they can just execute to import their new files.
I have seen lots of things on the web about running dtsexec but as the package is stored in the SSIS Catalog, how can I execute this remotely?
Thanks.

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

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

One simple solution is to schedule the package to run frequently and process any files it finds. If it finds no files, it completes without doing anything.

Might that work?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

lebraix SSC Enthusiast Points: 192 More actions · Answer 2

Phil's suggestion is simpler, but we do this at my workplace.

At the end of a MS Access process, we call a stored procedure that starts the job that calls the SSIS package. The only tricky thing is users have to be able to start jobs they aren't owners of. To do this, I had to add them to SQLAgentOperator role in msdb.

It sounds like you're halfway there since you already have the package and the job set up.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

lebraix (3/12/2015)
Phil's suggestion is simpler, but we do this at my workplace.
At the end of a MS Access process, we call a stored procedure that starts the job that calls the SSIS package. The only tricky thing is users have to be able to start jobs they aren't owners of. To do this, I had to add them to SQLAgentOperator role in msdb.
It sounds like you're halfway there since you already have the package and the job set up.

You could use this solution but using the EXECUTE AS in the stored proc. You can specify the name of a user who has permission to start jobs. Doing this means you won't need to add your users to the SQLAgentOperator role - they will only need permission to execute the sproc in a user database

Maddave SSCertifiable Points: 6828 More actions · Answer 4

Thanks all for your help. I didn't want to run the job all the time as it can be quite intensive and users could be searching historical data whilst the import process could potentially be importing a few million rows worth of data. Having a manual option is really for one off tasks and the users have been warned on the impact it could have on queries already running on the database at the same time.

In the end, I managed to use this guide https://msdn.microsoft.com/en-us/library/ms403355.aspx to create a small executable which simply calls the SSIS job. A sql user has been created which only has access to run the job and so no user access to the database is required.

Thanks again.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

Maddave (3/13/2015)
Thanks all for your help. I didn't want to run the job all the time as it can be quite intensive and users could be searching historical data whilst the import process could potentially be importing a few million rows worth of data. Having a manual option is really for one off tasks and the users have been warned on the impact it could have on queries already running on the database at the same time.
In the end, I managed to use this guide https://msdn.microsoft.com/en-us/library/ms403355.aspx to create a small executable which simply calls the SSIS job. A sql user has been created which only has access to run the job and so no user access to the database is required.
Thanks again.

Good solution, well done.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

lebraix SSC Enthusiast Points: 192 More actions · Answer 6

Maddave (3/13/2015)
Thanks all for your help. I didn't want to run the job all the time as it can be quite intensive and users could be searching historical data whilst the import process could potentially be importing a few million rows worth of data. Having a manual option is really for one off tasks and the users have been warned on the impact it could have on queries already running on the database at the same time.
In the end, I managed to use this guide https://msdn.microsoft.com/en-us/library/ms403355.aspx to create a small executable which simply calls the SSIS job. A sql user has been created which only has access to run the job and so no user access to the database is required.
Thanks again.

Thanks for sharing your solution. I may have to give this a try too.