Sladescross's Blog

Blogging about Sharepoint related stuff

SSIS Package Deployment With Password February 7, 2012

Filed under: SSIS,SSIS Deployment Package,SSIS Package Protection,SSIS Password — sladescross @ 12:36 pm

http://msdn.microsoft.com/en-us/library/dd440760.aspx

Video about the issues.

http://connect.microsoft.com/SQLServer/feedback/details/670502/deployed-ssis-package-failed-with-dts-password-error

You can use set package property ProtectionLelel to EncryptSensitiveWithPassword and specify password. You should also provide this password for deployment and job configuration phase.

http://msdn.microsoft.com/en-us/library/ms141747.aspx

SSIS Package Protection details

 

SSIS Package Deployment February 1, 2012

Filed under: Deployment,SSIS,SSIS Deployment Package,SSIS Variable — sladescross @ 1:01 am

http://msdn.microsoft.com/en-us/library/ms137592.aspx

Package deployment for SQL Server 2008.

http://msdn.microsoft.com/en-us/library/ms137916.aspx

Configure extra folders in MSDB for package deployment.

http://www.bidn.com/blogs/DevinKnight/ssis/73/adding-a-folder-to-the-ssis-package-store

Add a custom file system folder.

http://msdn.microsoft.com/en-us/library/ms365329.aspx

Tutorial deploying packages. Building the deployment bundle.

In this lesson, you will copy the deployment bundle to the destination computer and then run the Package Installation Wizard to install the packages, package dependencies, and ancillary files on that computer. The packages will be installed in the msdb SQL Server database and the other items will be installed in the file system. After you complete the package installation, you will test the deployment by running the packages from SQL Server Management Studio using the Execute Package Utility.

In this task, you will run the Package Installation Wizard to deploy the packages from the Deployment Tutorial project to an instance of SQL Server. Only packages can be installed in the sysssispackages table in the msdb SQL Server database, the supporting files that the deployment bundle includes will be deployed to the file system.

Integration Services provides several tools that you can use to run packages in the test and production environment: the command prompt utility dtexec and the Execute Package Utility. The Execute Package Utility is a graphical tool that is built on dtexec. Both of these tools execute the package immediately. In addition, SQL Server provides a subsystem of SQL Server Agent that is especially designed for scheduling package execution as a step in a SQL Server Agent job.

http://msdn.microsoft.com/en-us/library/ms365338.aspx

Head of the package deployment tutorial.

http://arcanecode.com/2009/10/06/using-ssis-package-configuration-values-as-parameters-to-sql-tasks/

Run through of using a variable for a parameter to SQL statement,

http://arcanecode.com/2009/10/06/using-ssis-package-configuration-values-as-parameters-to-sql-tasks/

Run through of passing SSIS variables to the SQL task.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Making the package portable.

Always reuse existing XML configuration files, if one exists for your server and database combination. This is the sole reason behind configuration files, as you will have to update connectivity information in only one place. If there isn’t a configuration file already created for your server and database combination, create a new configuration file using the below templates and follow the described naming convention.
For Windows authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue>Data Source=[SRVNAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> </Configuration> </DTSConfiguration> 

For SQL Server authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue> Data Source=[SRVNAME];Initial Catalog=[DBNAME];User ID=[LOGIN];password=[PWD];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False </ConfiguredValue> </Configuration> </DTSConfiguration> 

Note: In the above templates, square brackets [] are not required except around the name of the connection. Do not surround server name, database name, user name and password with square brackets

 

 

 
Follow

Get every new post delivered to your Inbox.

Join 63 other followers