Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 :
Can this be done without Bulk Insert
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);
Assuming you're working with the project deployment model, a package run is a few stored procedure calls strung together
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;
