Is there an easy way to provide parameters to an SSIS package?

What's the best way to configure parameters for a SSIS package? (I.e. what's common/easy/simple)

I am developing an SSIS package and I want to specify the location of a folder to watch for source files. I want to make it as easy as possible for those who deploy the SSIS package.

For example, I see that it's easy to specify connection strings for connection managers using dtexecui, but I don't see a place for parameters. i.e. none of the variables show up in the "Set Values" tab.

I looked at package configu开发者_StackOverflowrations but it seems they have to be created by a SSIS developer and not the SSIS package runner/deployer (I may be missing something)

---

Package configurations are created by the developer but they populate either an SQL table, an XML file, the registry or environment variables. So the selection of which values are populated is up to the developer but the actual values can be changed at deployment.

I've found that the best combination is to have the package configurations in an SQL table with the connection string for that table in an environment variable. That way you can maintain different environments for testing and production.

---

you can store them in a table and then dynamically populate the variables at runtime from the table with an Execute SQL Task, this way you don't have to modify the package

To add a variable View-->Other Windows-->Variables, then click on the Add variable icon (the top left one)

---

I don't think there is a simple and easy way to make configurable parameter passing simple for users/deployers to configure. The following is an outline of a semi-complex one I made a few years back.

A SQL Agent job regularly calls a stored procedure that does some checks and, when necessary, prepares data, launches an SSIS package, and passes the prepared data into the package via parameters; the values are either derived from the work-at-hand or from database "configuration tables" that IT configures on a per-enviornment basis. Given the nature of our scaled-out environment it was simpler to use parameters passed into the package than it was to try and have the package figure out where to go for its data.

Calling the package was done by building a complex script and passing it as a parameter to xp_cmdshell. DTEXEC is the executable to call, and it has mandatory and optional switches, such as /File to specify where the package is. To work parameters, I defined variables sich as "FilesFolder" and "EMail_SMTPServer" within the package, and then passed in values to them with the /Set switch, like so:

    SET @Command = 'DTEXEC'
     + ' /File "' + @PackageLocation + '\' + @PackageName + '"'
     + ' /Set \package.variables[FilesFolder].Value;"' + @FilesFolder + '"'
     + ' /Set \package.variables[EMail_SMTPServer].Value;' + @SMTPServer

/Set replaces their their Value property with the specified value. Note the extra " quotes for FilesFolder, to account for embedded spaces in the name.

This was pretty fussy to implement--I recall having loads of fun getting semicolon-delimited email addressees working properly. Thing is, it's been running smoothly for about 2 years now on several environments, and setting up a new one is a documented and thus relatively pain-free process.

There are many ways this kind of functionality can be done, this is only one of them.

---

There should be a variables tab in SSMS which allows you to specify the variable to set the values for.