Deploying to SQL Server 2005

[katbear]

Hello,

I would like to deploy my packages to SQL Server, but I am a little nervous as I have never done it before. I am afraid of breaking something and not knowing how or where to fix it. It's pretty easy to see what's going on in the file system with my packages - not so sure about saving to SQL Server...

Are there any caveats I need to be aware of?

For example, what if I need to change something? Do I need to "re-deploy" if any of the packages change? Or are the changes saved to SQL Server automatically when changes to the packages are saved?

Also, what is this "magic folder" (the folder where dependent files go) Is there a "standard" place for this folder? My book on SSIS mentions it, but does not indicate where to put it.

One more thing, I am clueless about configuration files. Are these important? I realize this is a vague question. But the packages I am working on now are tested and working... they are not going to be migrated anywhere. So should I bother with a configuration file? That is, when would I need a configuration file?

Thanks much!

 

[mrdenny]

If you change an SSIS package then yes you need to redeploy it to the SQL Server.

Configuration files are useful when moving a package from Dev to QA to Production so that the package never needs to be changed. Just the config file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

When I make a change to an SSIS package, I click on Rebuild and then I redeploy it. Haven't had a problem yet.

-SQLBill

Posting advice: FAQ481-4875

 

[katbear]

SQLBill, what kind of security are you using for your packages, and do you have problems running packages from jobs? That is the issue I'm grappling with.

Thanks

 

[mrdenny]

Depending on the user that owns the job and what permissions that account has at the OS level you can have issues running the job. How are you setting up the job and what access level does the account have to the OS?

What error are you getting when the job runs?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

I forget the exact error message now, but it had to do with cryptographic errors from the package because I had the package security set to "Encrypt Sensitive Data with User Key" - however, when I changed this to other options, I could not figure out how to pass the password properly to the job so that it would run. So I just changed to "Server Storage" security and none of the permissions issues cropped up again.

 

[SQLBill]

When I deploy the package, I chose the option for SQL Server vice File Server and then on the next window there is a check box for allowing the security to take place on the storage (in other words, let SQL Server handle the security).

To deploy a package, it has to be part of a solution. Adjust the properties for DeploymentUtility to TRUE. Then select build. That creates <packagename>SSISDeploymentManifest double click on that to start the deployment utility.


-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

I have also seen, but not tested yet, that you can choose to SAVE COPY AS and select the SQL Server storage option which is supposed to allow you to save it MSDB.

-SQLBill

Posting advice: FAQ481-4875

 

[katbear]

Yes, that is how I did it "SAVE AS COPY", but you can only deploy one pkg at a time that way. I tried using the manifest file, which calls the wizard (if I recall correctly), but I did not see an option to use SQL Server storage when I did it this way.

Maybe I was missing something? As you say, you do it this way and select SQL Server storage.

 

[SQLBill]

When I double click on the manifest file, I get the deployment wizard. The first window "Deploy SSIS Packages" has radio buttons (two) that allow me to choose either File System Deployment or SQL Server Deployment. I check SQL Server. Then click next. The next window "Specify Target SQL Server" has a check box at the bottom that says "Rely on server storage for encryption". Check that.

-SQLBill

Posting advice: FAQ481-4875