0

I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps. My idea :

  1. Pass the SSIS package to Job (Looping the path in procedure and call the job)
  2. Once the data is imported , on success / failure : different steps will be processed .
  3. Once success , the next Package will be processed (Step 1)

Can this be done without Bulk Insert

1 Answer 1

0

You can't pass parameters to a SQL Agent job, the first item has to be reworked.

An approach you could do, is create a custom table that holds the paths to the ssis packages and then your agent job enumerates through that and calls the various commands to start the SSIS Package.

CREATE TABLE ETL.PackageControl
(
    PackageControlSK int identity(1,1) NOT NULL PRIMARY KEY
,   ExecutionOrder int NOT NULL
,   PackagePath varchar(255) NOT NULL
);
-- Sample data
INSERT INTO ETL.PackageControl
SELECT * 
FROM
(
    VALUES
        (10, '\ssisdb\folder\project\package.dtsx')
    ,   (20, '\ssisdb\folder\project\package2.dtsx') 
    ,   (30, '\ssisdb\folder\project\package3.dtsx') 
)D(ExecutionOrder, PackagePath)
WHERE NOT EXISTS (SELECT * FROM ETL.PackageControl AS PC WHERE PC.PackagePath = D.PackagePath);

SSIS

Assuming you're working with the project deployment model, a package run is a few stored procedure calls strung together

  • create_execution creates an instance of an execution
  • set_execution_parameter_value allows you to configure values for an instance of that execution
  • start_execution begins the actual running of the package

By default, ssis packages will run in asynchronous mode, so you will want to set the SYNCHRONIZED bit.

The following TSQL shows how you can enumerate through the PackageControl table running SSIS packages.

Do note your TODOs and while the syntax looks right to my mental parser actual code might be slightly off

DECLARE @PackagePath varchar(255)
,   @ExecutionOrder int
,   @execution_id bigint
-- Your TODO will be parsing this data out of PackagePath
-- Or change the definition of PackageControl to hold the individual parts
-- whatever makes sense for your environment
,   @folder_name nvarchar(128) = 'folder'
,   @project_name nvarchar(128) = 'project'
,   @package_name nvarchar(128)

DECLARE CSR CURSOR 
FAST_FORWARD FOR
SELECT
    PC.PackagePath, PC.ExecutionOrder
FROM
    ETL.PackageControl AS PC
ORDER BY
    PC.ExecutionOrder ASC;

OPEN CSR;
FETCH NEXT FROM CSR INTO @PackagePath, @ExecutionOrder;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Lazy assumption here on how we can identify the package that is needed to be run. The trailing slash might be esacaping the single quote, handle appropriately
    SET @package_name = REPLACE(@packagePath, '\ssisdb\folder\project\', '');

    EXECUTE SSISDB.catalog.create_execution
        @package_name=@package_name
    ,   @folder_name=@folder_name
    ,   @project_name=@project_name
    -- TODO: Which runtime do you need to use?
    ,   @use32bitruntime=False
    ,   @execution_id=@execution_id OUTPUT;

    DECLARE @var0 smallint = 1;    
    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
        @execution_id
    ,   @object_type=50
    ,   @parameter_name=N'LOGGING_LEVEL'
    ,   @parameter_value=@var0;

    EXEC [SSISDB].[catalog].[start_execution] @execution_id;

FETCH NEXT FROM CSR INTO @PackagePath, @ExecutionOrder;
END
CLOSE CSR;
DEALLOCATE CSR;

Docs/Learn

1
  • Thanks a lot for the detailed explanation , I ll read through the steps and work this out
    – Lakshmi R
    Feb 7, 2023 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.