Column Required is used to force a value in the parameter before executing. Place the name of the parameter in the name column. There will be a toolbar button available to create new parameters. To create Project Parameters, just double-click the Project.param file in the solution explorer. The values used when developing and testing this package will be different than when deployed to production. The ServerName and DatabaseName project parameters will enable us to deploy this package to a different SSIS instance (QA or Production) and change the parameters when executing. There will be 2 new parameters created: ServerName and DatabaseName. So, if you use the parameter in more than one package in a project, and the value is changed before executing, then all packages will see and use the new value. Project Parameters are global to all Packages in a single SSIS Project. The first new area is Project Parameters in the Solution Explorer of the project. Figure 3 shows right-clicking the (project) ADVDW14 database connection and selecting the Parameterize… submenu, which is a shortcut.īut before we use shortcuts, it is better to learn the areas used by SSIS to complete this parameterization. The entire string can be changed or just parts like Server Name and Initial Catalog (database name). The Expression property is going to be used to change the connection string. The properties available when selecting the connection in the Package Connection Manager can be changed to parameterize the connection string. A connection (DW) created just in the package will not have this prefix. Figure 2 shows the prefix (project) on the connection name in the package DimProduct. The connection properties are changed from the Package Connection Manager which can be confusing. Once a connection is created in the Project Connection Manager, it is available to the packages in the project. If the connection to the database needs to be used in multiple packages, use the Project Connection Manager. These Connection Managers are available to either just the package or any package in a project. The Connection String for a database can be created for an individual package or for a project in Visual Studio. This article is going to dive into an example that parameterizes the Server Name and Initial Catalog (database name) in a connection string for packages in a project. Project parameters have become the go to solution for changing variable values in packages at run time. Variables were a step in that direction, but with Project deployments to the SSIS Catalog in SQL Server 2012 Microsoft has stepped it up, even more. SQL Server Integration Services (SSIS) has been growing into an enterprise solution over the last three releases.
0 kommentar(er)
