Warm welcome to #StepbyStepMSBI with Gurunatha.S.Dogi. Every week learn new topic with me step by step
Little About Us : . We are an e-learning firm since 2004 covery all .NET topics like .NET, ASP.NET, ADO.NET, OOPS and SQL Server , MVC, MSBI, AngularJS, Sharepoint, MVC5, LINQ, Design Pattern and many more. You are either a fresher / senior architect / project manager we have solution for every level at lower cost and self learning materials for more info feel free to contact us.
In Today's session we will understand use of paramters and environment variables in deploying SSIS package / project. In the first part of an article we will see use of parameters in SSIS project deployment and next part we will see use of environment variables in SSIS project deployment.
Parameters : Parameters allow you to assign values to properties within packages at the time of package execution.
To show you with an example here for this session i will be using previous article example that is For example check this article. To read this following article carefully so that you will understand an example and how to deploy SSIS package to a production server.
Here in this example we will again deploy our SSIS project to production with proper parameterization. Our example was moving a txt file from source to destination using a SSIS File System Task component. Here now we will create a parameter for destination path . So we will go back to project and add parameter as shown in below image.
Click on parameter tab and add new parameter for destination path as shown in below image.
If you see we have created a string type parameter called "DestinationPath" to set destination value which we have already set in the value.
In the next step we will configure this parameter to destination file connection in the connection manager as shown in below image.
Right click on that connection manager and go to property -> choose expression.
A form modal will open where we need to specify a property as ConnectionString because we want to parameterize our connection and then choose expression.
Now when you click on expression again a new modal will open where you neeed to drag a parameter "DestionationPath" to expression and save it.
Great we have created parameter let's rebuild the solution and deploy to SQL Server.
Now on .ispac file just double click and start deploying the procedure is same as will did in our previous article link already provided above.
Same way provide server name and path where your SSISDB is located.
A notification message will display which will say that project is already exist -> Review selection and Deploy it.
As you see from our below image we have a deployed our project successfully.
Now go to SQL Server SSISDB -> project level -> right click and -> configure.
As you from our above image we have successfully configured parameter to production server. Now just click on execute for project execution.
SSIS Environment variable : SSIS Environment variable is a kind of global which can be used in a package for different projects. Above we saw parameters but the main difference between parameters and environment variable is that parameters can be set only to a single project where as environment variable can be set to a one or more projects if it is located in a same source. So you can consider environment variable is a kind of global variable this provide the mechanism to set values at the time a package is executed. This functionality is useful if you are using same value of different parameters in different projects or want to set some common variable.
So here we will show you how to create and use an environment variable for SSIS project.
Now to create an environment variable go to -> SQL Server SSISDB - Select Environment Folder right click -> Create Environment as shown in below image.
Once you have created an environment, now its time to create variable so to do that just right click on created environment -> properties -> select variables.
Give a nice name "EnvDestionationPath" and value with "String" data-type as shown in above image and save it.
Now to to package and -> right click -> configure -> Reference. Here we need to reference MyPackage (Environment) to our SSISDB Package before using its variable.
Just click on Add button to add environment.
On the same modal go to -> Parameters and select already created parameter value.
A modal will open there you need to specify the environment variable as shown in below image.
Finally go to -> Package -> click on execute -> choose environment -> run.
Celebration Time : We have successfully executed the project. So friends till now we have seen how to deploy a project to production server with a parameters and scheduling ssis project to run automatically and finally use of environment variable in SSIS deployment. Hope you have enjoyed this session.
Order only MSBI self-study learning video materials which are available with customized package costing 999 INR/15$. Click this link here to see details and order it.
