SSIS Parameters and Environments

In this video, I describe how parameters and environments may be used in the SQL Server Integration Services catalog to externalize changing values such as connection strings and file paths.
Thanks to BenSound (www.bensound.com/) for the bumper music.

[@aloisraich9326]

That was the best explanation for making my dev and prod connection strings dynamic, I needed to go through many other youtube tutorials, which did not explain things so well, thank you

[@RajeshJaley]

This is the best tutorial on ssis parameters and environment on KZbin. Thanks much!

[@TimMitchellNet]

Thanks Jaley!

[@leroykian3298]

you all prolly dont give a shit but does any of you know a tool to get back into an instagram account..? I stupidly lost the password. I would love any tips you can give me

[@hughace4612]

@Leroy Kian instablaster =)

[@leroykian3298]

@Hugh Ace thanks so much for your reply. I found the site through google and im in the hacking process now. Takes a while so I will get back to you later with my results.

[@leroykian3298]

@Hugh Ace it worked and I actually got access to my account again. I am so happy:D Thank you so much you really help me out :D

[@dancrowell2933]

I used this to deploy SSIS projects, packages, and environments using SQL Server 2017. It is still relevant in 2018. Thanks, Tim!

[@VivekSingh06]

Very well designed video. Easy to understand using parameters and variables in SSIS now.

[@nedbakelman1284]

This is FANTASTIC. Thank you Tim for such a CLEAR and CONCISE tutorial on project parameters and deployment.

[@TimMitchellNet]

Thanks Ned! Glad it was useful to you.

[@dondada3190]

So far this is the best tutorial I have seen on ssis parameters and environment! Thank you, sir!

[@TimMitchellNet]

Thanks Don! Glad you found it useful.

[@ltbatis]

Thank you a lot, It is still relevant in 2020.

[@connectingthedots5987]

I’ve been trying to under parameters in SSIS for a while now. Thank you for a great content that makes sense.

[@johnesraelo4614]

Hello Bernard, I was wondering if you encounter the same issue as I. You see I have configured step by step, but, when I execute from SSIS catalogs (right-click on package and exec) keeps losing the environment reference/link). And, I have to check the environment checkbox over and over. and still does not stick. any ideas? thx JohnE

[@SciFiChick7]

Very helpful video. I've seen good explanations of SSIS parameters, but this also had a great explanation of environments, which had been harder to find.

[@TimMitchellNet]

Thanks for watching and for commenting!

[@Chris-by8eh]

Your a great presenter. I would love to see more videos

[@nedbakelman1284]

This is fantastic. Thank you Tim for your CLEAR and CONCISE explanations. We're about to implement parameters and configurations in our production environment.

[@anthonymcnicol8430]

Great video, thank you for taking the time to make this. Way more fun to follow along then reading this in a training kit or text book.

[@TimMitchellNet]

Great! Thanks for the feedback.

[@deniseyu9015]

This video is worth to spend your time; it takes me for working on ssis in different level. Thanks much:)

[@luisazario]

Same here, excelent !!!

[@YellowMiniCooper2]

Finally a good and easy guide !!! It is really hard for a beginner like me

[@yvonneandtwins]

This was great and clear. I wish you had more tutorials. I have seen many and they are tough to follow. Thank you!

[@TimMitchellNet]

Thank you! Stay tuned here for more videos soon.

[@tonguita21]

Tim, excelent tutorial, it helped me a lot. Thank you very much

[@sashadongre]

Amazing tutorial. So precise to the point yet very detailed.

[@wfharty3]

Excellent video. Fairly hard to find good material on params and envs in SSIS. Thanks!!!

[@achouab]

Well streamlined and excellent explanation. Great thanks Tim!

[@prateek2577you]

Nice video tutorial Tim. Looking forward to more of these.

[@feridboussoffara2377]

Thank you Tim, Excellent explanation !

[@AlexBunardzic]

Tim, this was just awesome. Thank you.

[@TimMitchellNet]

Thanks, Alex! Glad you enjoyed it.

[@AlexBunardzic]

You are a flawless presenter, Tim. Do you have any other of your presentations you'd recommend me watching? Keep it up, you're doing a stellar job :)

[@CoopmanGreg]

Great Video. You are a master instructor, for sure.

[@TimMitchellNet]

Thanks! Glad you enjoyed it.

[@hajnel4597]

This was great ! Thanks !

[@KevinMullarkey]

Excellent tutorial

[@karthikvalluri8125]

Excellent Video Tim..Very helpful!! Thank you.

[@kingingway3308]

Wow! The best explanation so far Thank you

[@ram1879]

Excellent video! Thank you so much for doing this.

[@lemmiTry]

Precise and concise. Thanks!

[@ashvinb001]

Nice demo on parameters !!

[@2maskstudio]

thanks for this concise and clear explanation. Plz keep making more vids

[@mesfinewunie9100]

Excellent Explanation

[@TimMitchellNet]

Thank you!

[@ubaidimran7102]

very nicely explained! thank you!

[@sinergy100]

So very helpful. Thanks so much!

[@MinhLa]

Great lesson!

[@mook4394]

Nice clear demo

[@stevespringer1373]

Very helpful. Thank you!

[@TimMitchellNet]

Thanks for watching!

[@luchoalejo1000]

Hey Tim, very good video, it was very clear. Thank you

[@abhisekmondal4076]

Excellent!

[@aldoramirezbautista]

Thanks for sharing your knowledge and your time. :)

[@MrPretoriaS]

Hi Tim, thank you for the video. As I understood, it is necessary to associate the package with an environment in SSMS and it can't be done in Visual Studio. However, this kind of sucks if you want to change something in the package itself and redeploy it. It would be great if an associated environment could be defined at design time (especially with BIML!)

[@shubhampranjale]

Fantastic

[@hojo70]

Excellent tutorial! Thx

[@dergimorka6126]

this is great tutorial brother thank you

[@PrakashPraaku]

Really helped me sir! Thanks

[@KhushbooKashiKi]

Nice demo

[@andyjackson6677]

Excellent tutorial, thanks Tim. I'd done everything but link the proj params to the env params. Thanks.

[@MehrdadAlemi]

How are the Param and Env works when we configure a Job to run the SSIS project?

[@zamdrist]

Why does it seem you must provide the parameter value in both the project, and SSISDB Project configuration? Perhaps I am confused. Shouldn't the value reside only in the solution as a project parameter, having a value there and under source code control?

[@hajnel4597]

Is there a way of automating the environment execution with sql stored procedures?

[@katvess1401]

Looking to use the Parameter Mapping to assign values dynamically. Do you have a video for that

[@robertodilisio7535]

Great video! It helped me a lot :)

[@bartomiejpietrzyk9040]

Great video, thanks. What if I want to change servers using parameters? I have a production and testing servers. Testing server is just a copy of production. Changing the parameter in VS, I want to execute the package either on testing or on production. Any ideas? Thanks!

[@luisazario]

Hi Tim, excelent tutorial, thank you. I was able to follow it and use almost everything the same way you showed, I needed two params too. I created the environment but when I tried to asocciate the parameter to the Proyect and press enter I got an error related to the existence of the parameters . Altough I was able to use the parameters from a job and execute the package too, changing parameters values manualy but not like you showed in minute 16:10 that the proyect get the values from the Env. Here is the error, notice that I am running sql-Server 2017. This are the errors I was able to see running from SSMS Msg 27106, Level 16, State 1, Procedure set_object_parameter_value, Line 163 [Batch Start Line 0] Cannot find the parameter 'pCadenaDeConexion' because it does not exist. Msg 27106, Level 16, State 1, Procedure set_object_parameter_value, Line 163 [Batch Start Line 5] Cannot find the parameter 'pUbicacionArchivoExcel' because it does not exist. I checked and parameters name are correct.

[@antoniodiala9696]

Hi Tim, I followed this video and set the parameters and environment in my SSIS package, but when I run the package I deployed in SQL server, an error of "Data Flow Task: Sample Files failed the pre-execute phase and returned error code 0xC020200E". It seems the file cannot be found? Could you please help me to figure out what is wrong so I can correct the error and can able to process the file. Thank you so much and have a great day! Thanks, Antonio Diala

[@ahmedfarouk8197]

Excuse me I have two questions about this great tutorial 1- what the benefit of using (Environments with parameters) rather than using (only the parameters) ? 2- is there is ability of passing the parameters value from (flat file or table) ? another question , what is the name of the recording program you used for this video :)? Thanks in advance

[@TimMitchellNet]

Briefly, the advantage to using environments is that you can bundle all of the parameters you want to pass in as a single unit, rather than having to set numerous parameters individually at runtime. Since the logic to start a catalog-deployed package is just a T-SQL statement, you could creatively build your T-SQL statement to add in parameters stored in a table. Doing so from a flat file would take a little more work, but it could be done.

[@RC-nn1ld]

Superb, thanks

[@peterrhoades2743]

Using a for each file enumerator with a wildcard in the file name. I’ve tried directory + file spec. Getting “” file not found. Everything is done on same server; package runs fine if don’t use environment. Can this sort of construct even work with an environment

[@peterrhoades2743]

Never mind. Works. Just have to make the Directory and Filespecs as package Params then follow Tim’s set up for the environment

[@benyams8014]

Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

[@orxanbabashov]

thanks lot. very useful

[@annlaosun6260]

The question is how to automate the job using SQL job agent as you have to manually click using environment each time. ?? I could not figure out how to do this.

[@TimMitchellNet]

Ann, when you create a SQL Agent job step, you can specify the environment to use, and that setting will be used whenever that job step runs.

[@annlaosun6260]

@@TimMitchellNet Thanks. Figured it out.

[@SciFiChick7]

What tool were you using for your zooms and drawing on the screen?

[@timmitchell1752]

+SciFiChick7 I use ZoomIt, which is a free tool to allow zooming and screen annotation. technet.microsoft.com/en-us/sysinternals/zoomit.aspx?f=255&MSPPError=-2147217396

[@vishnukant6287]

Thanks tim for this wonderful vedio , I have a question if we use sql authentication mode instead windows authentication mode and also use microsoft ole db provider (mean my db is not reside in my system).how to we run the same package in the visual studio if the do not want to store the password in the parameter/variable because of its sensitivity.

[@ashokku4191]

What about scheduling? If we made a parameter "requied", will it ask before starting of each schedule or is there a way we can set default "environment" for packages scheduled?

[@timmitchell1752]

+Ashok k u If you are scheduling a job through SQL Server Agent, the UI allows you to specify the environment to use. If executing a scheduled job via T-SQL, you can do the same thing through T-SQL.

[@MrBtuladhar]

Hi Tim, Thank you for a very helpful video. I tried running it through T-SQL, but I keep getting message "In order to execute this package, you need to specify values for the required parameters.". Is this the correct way of passing the info? EXEC [SSISDB].[catalog].[set_environment_variable_value] @folder_name=N'folder', @environment_name =N'Env-Dev', @variable_name=N'vConnection', @value=N'.....value....;' Environment has multiple variable already set. Do I still need to pass all variable names and their values?

[@ShadowOfBoss]

hi, i did all that but when i got to deploy it says I dont have the Integration services in that server (even tho I see it in my sql server) do you know what that might be?

[@healthyworld4840]

Hi Mitchel.. thanks for your knowledge sharing...... i have one question . Suppose you created the above story in Dev Environment, I want send this package to Production server. i don't have permission to open SSDT and SSMS, on that scenario how can we send it for deployment.

[@johnesraelo4614]

Hi Tim, great video. it has been almost 6 years since you have posted this video and I am not sure if you are still seeing the comments and questions here. I was wondering after we associated the references from the environment do we have to execute them at that point? The project that I have is rather complex and I just want to associate the references and execute the entry point from a SQL Agent Job. Is this possible? Also, every time I right-click on the project dtsx in SSIS catalogs keeps wanting me to check the environment checkbox. I just want that to take place once and do not go thru that cycle over and over. is this possible!?

[@johnesraelo4614]

Hi Tim, I am so silly, I just figured out that there is an "advance" option when creating a sql agent job and assigning the same environment checkbox to the selected dtsx. sorry about my oversight. And, thank you again for this awesome demo. JohnE

[@luannekindred2005]

Tim, I am continuing to receive an error of : A required privilege is not held by the client. Microsoft SQL Server, Error: 6522

[@odeddror8916]

Hi Tim nice video but what about changing Dev server with Prod on the fly? how do you setup the environment for both?

[@TimMitchellNet]

You could set up different environments, one for Dev, one for Prod, and so on. An SSIS catalog environment is flexible enough to be used by multiple packages/projects, and vice versa.

[@caesaraugustusjulius]

Thanks!

[@ashokku4191]

Also please tell me how to pass a parent package variable to child package variable in Project deployment model. In package deployment we can easily use parent package configuration for this purpose. But what about project deployment model where configurations are not allowed?

[@anildangol]

+Ashok k u , it's even easier in project deployment model. You just need to create a parameter in child package and variable or parameter in parent package. Then in execute package task of parent configure the parameter binding. Now you can use the parameter in child package to use just like a another variable. Hope it helps.

[@PushpendraKumar-os9st]

Great....

[@nedbakelman1284]

This was terrific. Thank you. It would be nice if it were possible to assign values to project level parameters (as opposed to package level parameters). For instance I use the parent child design pattern. My parent package assigns a list of departments to an object variable that is then used in a loop control whereby each department value is passed into a number of child packages and then used to filter a source result. I therefore need to setup the same department parameter in each of the child packages and then use the "execute package" task in the parent for each child and assign the department variable (from the parent) to the department parameter (for each child). Is there anyway around this?

[@timmitchell1752]

Hi Ned, if you are using the Execute Package Task, any project parameters will inherit their values from the execution of the parent package. To pass in a parameter to a child package, you'd need to either create a package parameter for that purpose, or execute the child package using T-SQL rather than the execute package task.

[@nedbakelman1284]

Thanks Tim for the reply. I very much appreciate it. Currently I'm using package parameters in each of the child packages and passing the values from parent to child via the Execute Package Task(s) within the parent package. The parent package iterates through an object list of department names, assigns the department to a variable per iteration (in the parent), and then passes the value of that variable to the package parameter per child using the Execute Package Task. I thought though I could simply assign the department to a project parameter per iteration (in the parent) and then simply use that same project parameter in each of the child packages thus eliminating the need for a package parameter in each child. Doesn't sounds like I can do this. Is my understanding correct?

[@nedbakelman1284]

Also, I honestly never thought about executing packages using T-SQL. This is something I'll look into. Thanks again!!!

[@shantanuchatterjee9056]

Is there any way to copy the Environment section from one SQL Server to another ?

[@TimMitchellNet]

There is no built-in, automated way to do this. However, you could script it out, since the environments and variables are stored in the SSISDB database. For a more interactive way to do this, check into SSIS Catalog Compare - it is a commercial tool but can help with this without having to script it out.

[@shantanuchatterjee9056]

@@TimMitchellNet great thanks. Ideally one server is mostly dedicated to one environment, even tho there is a flexibility of creating more than one env in this new set-up.

[@sachindixit14]

Thanks Tim, That was really helpful for new developers like me. I have one question though.. I wanted to use folder path instead of full file path. I have 20 different files for which i wanted to create a single folder path parameter and then use that parameter across the package. e.g. pFolderPath = c:\SourceDataFiles\ Under this folder i have many files so i wanted to use like this Package 1 - pFolderPath\File1.csv Package 2 - pFolderPath\File2.csv Package 3 - pFolderPath\File3.csv Please let me know how to create it. Thanks in advance

[@Reza5th]

awesome video , how could i use parameter in sql task ?