I've been working with SSIS for years, but I'm new to the whole project deployment paradigm - although I think it has been sorely needed!

I'm in the midst of converting our SQL 2008 SSIS packages to SQL 2012. The piece I could use some guidance on is dynamic file names and connection strings to certain environments (DEV/TEST, PROD, etc.)

In all the packages in question, there are files that are on a corporate share that need to be created/processed. What I need help with is how to set up an SSIS project with the deployment model with different environments, and in those enviornments pass different server names, paths, etc.

So for example, I have two environments: DEV (DEV_SERVER1) and PROD (PROD_SERVER1).

On each of these servers I have the same directory structures: \\<server_name>\Data\Images\DailyFile.

There are two files I'm working with: Daily_SAE and Daily_Image. Right now I have the file paths and name hardcoded in the connection manager(s).

So what I need is to for each environment be able to set up project parameters unique to each environment for the UNC server name, the SQL database, and the file name(s) unique to each environment.

Been looking for a good resource on dynamic connections, files, etc. but still a tad lost.

If anyone could provide some food examples of working with file names and connection strings that would be great!!

Thanks!!

Seems that you want to manipulate on the SSIS catalog programmatically. So, 

The easiest way to change the connection string value is by using SQL Proc

EXECUTE [SSISDB].[catalog].[set_environment_variable_value]

This method is covered there.

Actually what I want to do is have project parameters for two different environments: TEST and PROD. I'd then deploy the projects to SSISDB. For each project, there would be a set of variables: varTestDB, varProdDB, varTestFileName, varProdFileName - each with a different value.

I need to create connection strings and expressions to be used in the packages,connection strings using project parameters so depending on what environment I'm running in, the packages will point to a particular database on a particular server. And the packages that use file connections will point to a particular file share on a particular server.

Trying to create dynamic connection strings using project parameters, variables, and expressions.