07 Dynamically Execute Packages in SSIS | SSIS real time scenarios

[@h1permonth723]

thank you so much, this is best channel to learn ssis. i have added one more feature for this project that already executed packages will not get run by after 1st execution and new packages will run :) 🥰

[@learnssis]

Thank you. Okay great.

[@sql3051]

Thank you. A great demonstration of a complicated process in SSIS.

[@learnssis]

Thanks for your support.

[@CRVgarage]

We can also run multiple packages concurrently using execute package tasks in one master ssis pkg. Thanks for the video.

[@gfenno]

I love your videos. Please keep them coming. I'm in the process of learning how to use ssis and this helps allot

[@learnssis]

Good to hear that it helped you. All the best :)

[@MVinodKumar-tt1tu]

Hello Sir, In this video the all child package details are inserted manually before calling them using select. Cant we store it dynamically all child packages info using Execute Sql Task as we have child package names already. I tried like this but i got to insert only one child package sir. Remaining i'm unable to insert all childpackage details. After creating variables Packagefolderpath and packagename. And if i keep them empty initally and use it to insert dynamically using this insert query 'insert into pkg_details values (?,?,1) and mapped both variables. If i keep empty variables initially then output is not coming and i want to store all child package values dynamically sir? And Foreach Loop Container and Execute package task is also failing

[@daniellespeight1916]

Hello! Is there a way to run the packages in parallel (not looping) the same way you have developed it (using ssms table)? Or is the only way to do this is by using ssis execute package for individual packages and not linking them? Thank you.

[@learnssis]

Hello, one way that I can think of is that create a sql agent job for each package and then from SSIS package, use a for each loop container ado enumerator and then get the list of all sql jobs from a sql table and loop through all sql agent jobs and call them inside for each loop container from an execute sql task, there is a stored procedure that you can use to call a sql agent job. Below is the command to call a sql agent job EXEC dbo.sp_start_job N'SQL Agent job name' ; 2. Another basic way can be that you can create a Sequence container and put an execute package task for each SSIS package and call an SSIS package using each execute package task and don't connect them to each other.

[@SpoorthiTejasvi]

@@learnssis Thanks for such valuable information. I have a question here : when we have multiple copies of same child package with paramter bindings done inside for each loop of parent package, there is overlapping of values assigned to child package.(ie value childpkg1 is assigned to each copy of child package paramter instead of childpkg1 to copy 1 of child package , childpkg2 to 2nd copy of child package) how to overcome that?

[@skipa9906]

Great video, its kinda what I am looking for. My question ism, what if I want to run a specific package and not all of them the same time? If I want to run child1 at a certain time I should be able to specificy that run only child1

[@learnssis]

Thanks. If you want to only execute only a specific package then just set the active flag to 0 for all packages and then only enable that specific package like below update pkg_details set active=0 go update pkg_details set active = 1 where packagename = 'child2.dtsx' So in above queries we are not actually changing anything inside ssis package but running the queries directly on the sql database to run a specific package.

[@rajkumarrajan8059]

Amazing!! Genius work

[@learnssis]

Thank you Raj Kumar Ranjan.

[@crixus7354]

Thanks for answering. I am new to SSIS package development. I am looking for the easiest way to move from a development environment to a production environment. Especially when configuring the path of the input files and the names of the development and production servers. What would be the correct way to configure input file paths, database connection strings that is the most optimal.

[@learnssis]

You can use project parameters to provide the values to file path, configure the oledb connection managers using project parameters. You can even provide a value to ssis variable from project parameter. Then you can deploy the SSIS packages to SSIS catalog in sql server. From there you can create the environment and create variables to hold a new value for project parameter and map it with project parameter. You can create multiple environment as well. For example you can have dev environment as well as prod environment. Now you can execute the ssis packages from ssis catalog and map a environment to it. If you do not want to use the environment and just want to change the value of ssis packages then you can do that using sql agent job. Below are the 2 videos about deploying the package to ssis catalog and scheduling and providing a value to sql agent job. kzbin.info/www/bejne/rpKleoyumpqkjKM kzbin.info/www/bejne/p6O5Y3mYhtWIaas kzbin.info/www/bejne/rpq7qHafmbN5r5I

[@onkaringawale4930]

How are you using Getdate() within Insert statement? it throws an error on SSMS.

[@learnssis]

GetDate() should not throw an error. Can you run this query on SSMS select Getdate()

[@crixus7354]

In the connection manager of the project I created the global connection and proceeded to change all the connections in the child packages and in the parent package. If I run the individual child packages, they run successfully, but when I run them through the parent package, dynamically using the method in the video, they no longer work for me. What I can do?

[@learnssis]

while configuring the execute package task, inside project reference I have selected the option External reference, if you want to use a global connection manager (project connection manager) then here you should choose "Project reference" instead of "External reference" and then pass the package name dynamically without folder name. This will work for you as I have tested it.

[@crixus7354]

Now that I converted my project to a deployment model, the configuration manager is gone. Where could I set up a global connection string for the project to make it easier to go live?

[@learnssis]

You can use project parameters to provide the values to file path, configure the oledb connection managers using project parameters. You can even provide a value to ssis variable from project parameter. Then you can deploy the SSIS packages to SSIS catalog in sql server. From there you can create the environment and create variables to hold a new value for project parameter and map it with project parameter. You can create multiple environment as well. For example you can have dev environment as well as prod environment. Now you can execute the ssis packages from ssis catalog and map a environment to it. If you do not want to use the environment and just want to change the value of ssis packages then you can do that using sql agent job. Below are the 2 videos about deploying the package to ssis catalog and scheduling and providing a value to sql agent job. kzbin.info/www/bejne/rpKleoyumpqkjKM

[@vitto_af]

Hi, do You have some video with streaming data in ssis ?

[@learnssis]

No, I don't have any such video, neither I worked on this kind of SSIS package.

[@heavyraindrops4750]

Thanks for this video, that's very helpful. I have a question, what if this parent package needs to be deployed to SQL server and then dynamically determine which child packages to execute? How would you approach that? Because then using the package folder path in this way won't work.

[@learnssis]

You can convert the project type to project deployment by right clicking on project if it is package deployment. What happens with project deployment that you don't need to provide the package path, instead you just need to provide the package name and select the reference as Project reference, this way you can read the package name from sql table and can pass the package name through expression to execute package task and it should work without folder path.

[@heavyraindrops4750]

​@@learnssis Thank you so much for your response, I really appreciate it! I tried the approach you suggested before but it didn't work for me. This is what I tried: I created a SQL table with the package names that need to run. I then used a execute SQL task to get these package names and stored them in a result set. I then used a for loop to loop through the result set and map the package name to a variable. Within the for loop I use an execute package task and then for the expression I am trying to use the variable + ".dtsx". When I try to save the task it will say that it can't find the package name, because it only sees ".dtsx". Any idea what I might be doing wrong here?

[@learnssis]

@@heavyraindrops4750 If you can use the foreach loop then it will only loop for all packages, you don't need to use for loop here. Second thing is inside the foreach loop container store the Value of SSIS package in a string Variable like PackageName and now in the execute package task expression, select the project reference and then select the PackageName property and in the package name property try to write it like below ""+ drag and drop the ssis variable here +"+.dtsx" Send a test email to me at [e-mail], I can show you how to do this if you still face issue.

[@glhuanto]

I tried your method. I got an error -" Error: 0xC001000E at ChildPackage_1: The connection "{43011289-55D2-4C7E-9F16-A119DEBD524D}" is not found." The only different than yours is the connection (database) of ChildPackage_1 is the project db connection ("{43011289-55D2-4C7E-9F16-A119DEBD524D}" ). Any hints to make this work? Thanks!

[@learnssis]

Hi Linda, you missed to map one of the connection manager to a component. Please verify the connections for all components. Thanks.

[@pedrojmanuel]

Great insight. I try to apply this solution to run a more then one child package dynamically. Can you do a good video how to do a precedence for 4 child packages run two first and other two in next cycle of the loop

[@learnssis]

Okay, will try to make a video on this one. But I think the package number should be even otherwise it will fail.

[@pedrojmanuel]

@@learnssis thanks , I'll appreciate that. I'm a bit struggle to run dynamically in parallel the packages.

[@llciii]

Hello I'm getting this error could you help please and thanks Error: Error 0x80070003. Unable to access the package file, "C:\MSBI\SSIS\Training\DynamicallyExecutePackagesinSSIS\DynamicallyExecutePackagesinSSIS\ChildPackage1.dtsx". Make sure that the file exists and that you have permission to access it.

[@learnssis]

Yeah looks like package does not exists at that location. Can you try to paste this path in windows explorer and see what happens ?

[@sarfarazmakandar4684]

Can I execute SSIS Package for Fuzzy Grouping Dynamically? I need to pass table and column names which are dynamic.

[@learnssis]

I don't think they can be passed from a config file, they need to be set at the design time. Thanks.

[@VinodkumarKotagi]

What if we want to execute all the child packages with 'active=1' in parallel?

[@learnssis]

If you can create the sql job for them, then in the foreach loop container you can just execute the sql agent job and it can kick off the package. So they will be kicked off one by one and then they will run in parallel.

[@VinodkumarKotagi]

@@learnssis sorry I'm new to this so didn't get your point, Is there any example or video related to that?

[@learnssis]

@@VinodkumarKotagi You can watch this video to understand how to schedule the ssis package from sql agent job. kzbin.info/www/bejne/p6O5Y3mYhtWIaas In your case you can create the sql agent job but do not create the schedule for it. Now once the sql agent job is created you can execute the sql agent job from execute sql task using below code exec msdb.dbo.sp_start_job N'Weekly Sales Data Backup' This way it can execute the sql job and whatever package will be called from the sql agent job it will execute the ssis package. You can have one sql agent job for one ssis package. And in the execute sql task you can pass the value of sql agent job dynamically from expression or from parameters.

[@raulgutierrez5862]

Awesome bro. Thank you!

[@learnssis]

Thank you Raul for your comment.

[@Navi-i1m]

How we can deal with the dependency packages

[@learnssis]

What do you mean by dependency packages ?

[@catulopsae]

Very good. I have a question. Can we execute other packages that are not within the same solution?

[@learnssis]

Yes we can. You just need to provide the path to the SSIS package on the machine and it will execute that package as well.

[@catulopsae]

@@learnssis thank you so much for taking your time to answer my question

[@learnssis]

@@catulopsae No problem.

[@Jigyasuh]

Hi aqil, [Execute SQL Task] Error: Executing the query "select PackageFolderPath, PackageName from Pkg_Det..." failed with the following error: "Invalid object name 'Pkg_Details'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Please suggest what to do?

[@learnssis]

It seems like Pkg_Details table does not exists. Please create the table in the database.

[@Jigyasuh]

@@learnssis [Execute SQL Task] Error: Executing the query "INSERT INTO LOGS VALUES ('Package Started','ChildP..." failed with the following error: "Invalid object name 'LOGS'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

[@learnssis]

@@Jigyasuh It seems like Logs table is missing, please create the logs table.

[@crixus7354]

Hello...Excellent video...I had a series of independent packages, each one with its connections to the database, independent of each other. When implementing your solution it worked great for me, but when I want to create a unique and global connection for all the packages, it does not work for me, it sends me an error that the connection does not exist in the child packages.

[@learnssis]

Okay, in this video, while configuring the execute package task, inside project reference I have selected the option External reference, if you want to use a global connection manager (project connection manager) then here you should choose "Project reference" instead of "External reference" and then pass the package name dynamically without folder name. This will work for you as I have tested it.

[@dilipkumar-ck1nc]

Expression builder code and sql complete code is missing.kindly paste the notepad and update it

[@learnssis]

Okay, I have uploaded the code to the google drive. Thanks.

[@crixus7354]

I was able to make the change using the project's connection manager using the same connection string to the destination database for all packages (parent and children).

[@learnssis]

Okay great.

[@poojitha4523]

Hi Aqil, I need to check dependency tables refreshed in that particular stored procedure if the dependencies refreshed for the day then it will run the stored procedure if the stored procedure is success/fail I need to get a mail. Like this each stored procedure is dependent on other stored procedure I need to do this for all the stored procedures. Up to here I created all like child packages. Now I need to check dependencies has refreshed then only the other stored procedure need to run. For example: without child1 I cannot run child2 package as child1 is dependency table in child2. if child1 fails it should not refresh and send us a mail, if child1 successfully refresh then it should kick off child 2, if child2 fails then the refresh should stop and immediately I need to get a mail. Can you please help me what would be the process. I really like you videos I'm learning a lot. Keep posting the videos.

[@learnssis]

What I think can be done is initially all packages like child1, child2, child3 will be inactive, means Active flag will be 0 for them. And instead of Foreach loop container we will use for loop container here as in the beginning we don't know how many time the loop can run, thus may be we can set the counter to run the loop 100 times. Now when you will try to run it, you will set the Child1's active flag to 1 and rest of the child packages like child2, child3 active flag will be 0. Now if child1 runs successfully then in the end of the package it will set the active flag of Child2 to 1 and if it will fail then of course it can send the email using OnError event, so if it ran fine then it will enable the Child2 and will disable the Child1. So in second iteration of forloop it will pick the Child2 to be run, and in the end of Child2 you can disable the Child2 and Enable Child3, this is how it can work until all packages are ran.

[@poojitha4523]

@@learnssis Thank you for the immediate response Aqil, I will try later today. Also, I want status of success or fail in my logs how can I create a variable? I tried but I can able to give only 1 value. Is there any way to create a status?

[@learnssis]

@@poojitha4523 Do you want to return the fail or success from stored procedure or from ssis package ? If from stored procedure then you can use try and catch blocks there, in the beginning you can set the status to success, and in the catch block you can set the status to fail, so if no error will occur the variable will be success and if an error occurred then the variable value will change to fail. And using output parameters in stored procedure you can return that variable value. You can do the same thing in SSIS package as well that you can declare an SSIS variable and initially set the value to Success and now in OnError event of SSIS package set the variable value to Fail.

[@artisingh9165]

Hello sir...i am in the beginning of learning SSIS..Can u make video on multiple excel file loading with removing duplicates and making the package dynamically so that every time file will come and it will load automatically...it will be great help....or if u are giving any sessions i am ready to join

[@learnssis]

Hi Arti, For this requirement, it will require multiple tasks and I have already created a video on each task separately. It will require a Foreach loop container to loop through multiple files. Thus I will share link for that. In the foreach loop container you can use a data flow task and in data flow task you can take an excel source and in the excel connection manager, you can pass the ExcelFilePath through expression from the variable of foreach loop container. Then you can move the excel file after loading to an archive folder, I will share the link of that video as well, now either you can use Sort transformation in between Excel source and OLE DB Destination as a transformation to delete the duplicate records or you can use an execute sql task and sql queries to delete the duplicate records, I will share the link of both techniques kzbin.info/www/bejne/eJvTkoaugr-ArtU kzbin.info/www/bejne/iIe1cqh8jNeAmac kzbin.info/www/bejne/bqDLpGdpnJ6La7s kzbin.info/www/bejne/eGGqaauLoZaEmdE

[@artisingh9165]

@@learnssis thanku 😊 sir I will try if I face any issue I will ping u

[@learnssis]

@@artisingh9165 Ok

[@WarDkr]

Great video

[@learnssis]

Good to know that you liked the video. Thanks.

[@nitagawade3330]

Wonderful!!!!

[@learnssis]

Thanks for your comment.

[@Navi-i1m]

How we can deal with the dependency packages

[@Navi-i1m]

How we can deal with the dependency packages