121 How to execute SSIS package with parameters from stored procedure

[@blofeld2430]

Pretty well explained, thanks ! Is there a way to make the query throw an error message if the package fails ?

[@learnssis]

yes, we can do that. Below is the updated sp. CREATE PROCEDURE ExecuteSSISPackage @ServerName NVARCHAR(100), @Database NVARCHAR(100) AS BEGIN BEGIN TRY -- Declare variables DECLARE @execution_id BIGINT; DECLARE @status INT; -- Create execution EXEC [SSISDB].[catalog].[create_execution] @package_name = N'Package.dtsx', @execution_id = @execution_id OUTPUT, @folder_name = N'Test Project', @project_name = N'121 How to execute SSIS package from Stored Procedure', @use32bitruntime = 0, @reference_id = NULL, @runinscaleout = 0; -- Set parameters DECLARE @varDatabase SQL_VARIANT = @Database; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 20, @parameter_name = N'Database', @parameter_value = @varDatabase; DECLARE @varServerName SQL_VARIANT = @ServerName; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 20, @parameter_name = N'ServerName', @parameter_value = @varServerName; -- Set logging level DECLARE @logging_level SMALLINT = 1; EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', @parameter_value = @logging_level; -- Start execution EXEC [SSISDB].[catalog].[start_execution] @execution_id; -- Wait for execution to complete (Optional - Adjust timeout as needed) WAITFOR DELAY '00:00:05'; -- Check execution status SELECT @status = status FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id; -- If status indicates failure, raise an error IF @status NOT IN (7, 8) -- 7 = Success, 8 = Canceled BEGIN DECLARE @error_message NVARCHAR(MAX) = ''; SELECT @error_message = STRING_AGG(message, CHAR(10)) FROM [SSISDB].[catalog].[operation_messages] WHERE operation_id = @execution_id AND message_type = 120; -- 120 = Error IF @error_message IS NULL SET @error_message = 'Unknown error occurred during package execution.'; RAISERROR ('SSIS Package failed: %s', 16, 1, @error_message); END ELSE BEGIN PRINT 'SSIS Package executed successfully.'; END END TRY BEGIN CATCH -- Handle and re-throw errors DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; GO

[@BengaloruMachi]

Thq soo much aqil

[@learnssis]

Thank you Harish.

[@itsupportbyharshit6285]

this is very helpful я

[@learnssis]

Glad to know that you liked the video.

[@learner_SSIS]

Great Aqil sir 😀

[@learnssis]

Thank you Naveen.

[@thiagoperezsantos]

Execelente!!! Muito obrigado!

[@learnssis]

Thank you.

[@MethodOverRide]

Great video! Each time you update the package, do you have to redeploy to the ssis catalog?

[@learnssis]

Yes we need to redeploy it.

[@bhupathi7636]

Hi sir thanks for sharing your knowledge. Can you please make a video on ssis package if user pass db name and table name and the package export all the data from table to excel

[@learnssis]

Hi Bhupathi, Sorry I was out of station thus missed to reply to you on this, actually we already have a video very similar to your question, where I shown how to export data from sql server table to excel file. kzbin.info/www/bejne/kHaam5-NqtamrLc The only change here will be that we just need to declare 2 variables, one Database name and second table name and just pass the value of database to connection string and just pass the sql table name to the sqlquery used in this code.

[@thulasi1122]

Hi, can you make a video using power automate how to automate the ssis package execution whenever a new file added in SharePoint.

[@learnssis]

Sorry I never worked on SharePoint nor power automate.

[@giria7809]

Hi Sir, I need to load a specific monthly file(All files) using Ssis. How to do it? Please Explain. Example:- If I need an August month-Load all August month files into the sql server table. If I need a February month - Load all February month files into the sql server table.

[@learnssis]

For this one we need to use the foreach loop container with file enumerator, thus it will loop through all files, now inside foreach loop container we can put an script task and inside script task we can get the either create date or modified date for a file, and from the date we can get the month using c#. Now we need to declare an ssis variable month_name of type string and can pass the value to the script task and now compare this value with the value from month got from date and if they are equal then assign the value to an ssis variable like "Value matched" and then after the script task between the data flow task, we can use precedence constraint like If ssis variable=="Value matched" I will try to make a video on this one.

[@giria7809]

@@learnssis Thank you so much Sir. . I will waiting for your Video. . Thanks a lot.

[@jayanthjay1050]

Bro, did you have any idea how to get the current date folder from SharePoint to the local folder by using a PowerShell script?

[@learnssis]

Sorry I haven't worked on SharePoint.

[@chellurisatish8514]

Very Nice Aqil Can you make a video on have to execute the package by changing the parameters in a table . For example usually we will take load sysdate -3 through ssis . if we want to take load for 20days if we updated in table and run package it should take the for 20days .

[@learnssis]

Yeah Satish it will good to make such video, will try to create one.

[@chellurisatish8514]

@@learnssis Thanks in advance

[@thulasi1122]

If you have any idea let me about this because now i am working on same kind of task

[@learnssis]

Sorry I don't have any idea about power automate or SharePoint.

[@agentsmith2189]

TY I LOVE U

[@learnssis]

Love you too :)

[@s.211]

Hello aqil sir . I have been learning SQL since a week and now I am pretty much familiar with SQL queries.. will I get a job on SQL only ? If yes then please let me know how ?

[@learnssis]

I think you should try to learn either ssis or SSRS or power bi along with SQL then it will be easy for you to get the job.

[@s.211]

@@learnssis ok thank you