April 10, 2011 at 6:50 pm
I have created a SSIS package, i have an sql agent job running the package everynight.
Users want to update this package on demand. I do not want to give them access to the SQL Server, how would you update the siss package, ie via vb script if so where can I learn how to do this.
I am new to the SSIS world in SQL 2008
April 11, 2011 at 1:24 pm
Hi,
SSIS related questions should be posted on this forum (SQL Server 2005 Integration Services, http://www.sqlservercentral.com/Forums/Forum148-1.aspx).
1) If you are running SSIS package from SQL Agent you could configure JobStep to run package from the file system and create a file share to the package directory with limmited access for required users.
2) Or you could let them upload the package via let's say a webpage where you could utilize Integration Services Programming model. Example of saving DTS file to the SQL Server can be found on MSDN (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetosqlserver.aspx).
April 12, 2011 at 12:21 pm
My preferred method for this is to allow the users to insert a request record in a table.
Then I run a job continuously, say every five minutes. The job looks for outstanding requests and if one exists then the package is run.
This assumes your users can wait for the scheduling interval that your job is running at. If they need absolute immediate gratification then this wouldn't be a solution.
I find this method robust, easy to code, and straightforward from a security perspective.
April 15, 2011 at 9:41 am
You must make the package entirely Dynamic, meaning that every possible changeable configuration must be stored either in variables or in an SSIS XML or SQL Configuration.
Then you can either create a batch file, stored procedure or .net code to allow them to modify those values and run the package after that.
use TDEXEC on their computers to run the package.
Example:
DTEXEC /SQL "\PackageMSDBPath\PackageName" /SERVER "SSISHostSQLServer" /X86 /CHECKPOINTING OFF /REPORTING E
http://msdn.microsoft.com/en-us/library/ms162810.aspx
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply