November 1, 2013 at 9:47 am
Just want to get some ideas on how to set up and deploy SSIS packages (examples will be great) in the following environment.
Svr1 (development db and ssis)
Svr2 (testing db and ssis)
Svr3 (prod db and ssis)
Question 1: Develop and Deployment
When we develop, we develop everything on Svr1 (both DB and SSIS). So naturally, all the connection strings will be svr1.dbname. When everything works, we need to change the connection strings to svr2.dbname and re-deploy to Svr2. It's a tedious task when you have 50-60 packages in question. And the process repeats when it goes to prod.
Is there a better way of doing this?
Question 2: Running the package.
I double-clicked the package, and the Execute Package Utility. I clicked Execute, saw positive activity, and was curious if I could run it on the "undeployed" packages … which I can.
So, my question is, what is the function of the deployment process?
Can Sql Server Agent run "undeployed" packages?
Thank you.
November 1, 2013 at 11:47 am
Question 1.
Yes, research package configuration.
Article: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66500/
BOL: http://technet.microsoft.com/en-us/library/ms141682(v=sql.105).aspx
Question 2
The deployment process does not alter packages, it moves them to a location where SQL Server agent can access and execute the packages.
It should not matter how you deploy the packages, it is easier to use the deployment utility but a simple copy, or import with SSMS does the trick too.
November 1, 2013 at 12:29 pm
It should not matter how you deploy the packages, it is easier to use the deployment utility but a simple copy, or import with SSMS does the trick too.
Frankly, I always thought deployment utility compiles everything and moves a compiled file to the server.
So if a user (non-IT personnel) needs access and run dts packages for a file system deployment (on the server), does he / she need to install BIDS on his / her workstation? Or will I need to install BIDS on the server and she'll have to remote into it to run?
Thanks for your suggestions on package configurations. I'll definitely look into it.
Thanks again.
November 3, 2013 at 2:15 pm
SSIS needs to be installed on the machine where the packages are run, it is an option for the general SQL Server install.
How to run a package: http://technet.microsoft.com/en-us/library/ms138023(v=sql.105).aspx
If the packages can be on a schedule SQL Server agent is the way to go, if the business user decides when the package needs to run and want to start the job, dtexecui is likely you best choice.
BIDS is the development environment, and allows the user to change the packages.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply