SSIS Project Level Connection Manager Property configurations

0
votes

I have just started looking at using the SSIS 2012 Project Deployment. One thing I have noticed is that once the project is deployed all the properties relating to the project level connection manager get deployed too and hence need to be configured (Linked to SSIS Environment Variables).

Was just wondering whether the SSIS environment is smart enough to infer the ConnectionString property from the InitialCatalog and ServerName properties. That way I would only need InitialCatalog and ServerName Environment Variables and not a third variable containing the actual connection string which would result in a duplicate configuration.

Please see screenshot below showing an example of this.

enter image description here

2
configurationssisssis-2012
Or you can use 1 Environment variable which is the whole ConnectionString and forget messing with the parts. - billinkc

2 Answers

0
votes

If I got your question right, you want to set the initial catalog and server name property paramatically.

Here are the steps to do it: [Applicable for SSIS 2005 and above]

Step-1: Create two variable in SSIS package (Package scope) and name them DbName, ServerName

enter image description here

Step-2: In connection manager, create a OLEDB connection and test connection.

Step-3: Now, right click on the connection manager and click properties.

Step-4: In properties, click expression. You will see below window. In this window select initial catalog

enter image description here

Step-5: Now click on expression and drag drop DbName (i.e. your catalog name) variable from upper left window to expression text box and evaluate it.

enter image description here

Step-6: Do the same for Server name. and you are done

Remember: If your initial catalog and database parameter name are not correct then you may face connection issue.

Other way, Specifically for SSIS 2012 and above

Now, as you have mentioned SSIS2012. Let;s utilize its feature of Project parameter

  • Create project level variables, You can either create two variable names [DBName, ServerName] or complete connection string.

enter image description here

  • Create OLEDB connection in connection manager and right click on the connection. Then select, "Parameterize"

  • Based on your parameter, you can set parameter to properties like Initial catalog, Server Name, Connection string etc. Or you can create project level parameter from here by selecting appropriate values, as shown below

enter image description here

Read this link for more details

0
votes

I tested several combinations of ConnectionString, InitialCatalog and ServerName. It looks like servername (DataSource) and initialcatalog have to be in the connection string and the initialcatalog has to have a value. If I left servername blank it worked as long as DataSource was in the connection string.