Execute SQL Statements From a SQL Server Table in SSIS Package - SQL Server Integration Services Tutorial

Scenario:

In this video we will learn how to run multiple SQL Statements which are saved in a SQL Server Table. The new sql statement can be added or old can be deleted as per requirement. In any case we don't have to make any changes in SSIS Package to run the queries from SQL Server Table. SSIS Package will be dynamic to handle this.

After watching this video, you will be able to learn


  • How to Read the List of SQL Queries from SQL Server Table by using Execute SQL Task and save them into Object Type Variable
  • How to Loop through List of SQL Queries By using Foreach Loop Container
  • Save values from Foreach Loop Container to variable in SSIS Package
  • Execute SQL Queries ( From Variable) by using Execute SQL Task

Scripts used in the Demo Video to Execute SQL Statements from a SQL Server Table in SSIS Package.

--Create Sample Tables for Testing, insert some records 
CREATE TABLE [dbo].[Customer_AS](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL
) 


CREATE TABLE [dbo].[Customer_NA](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL
) 



--Create the Table to save SQL Queries
Create table dbo.QueryTable(
Query NVARCHAR(MAX))

-- Alter Column as scenario changes or to handle rerun of Same Query
Alter table dbo.QueryTable
Add QueryRanStatus tinyint

Alter table dbo.QueryTable
Add Id int Identity(1,1)


--Insert the Queries in the table
Insert into dbo.QueryTable values ('insert into dbo.customer values(1,''Test1'')')
Insert into dbo.QueryTable values ('Insert into dbo.Customer values (1000,''Test100'')')
Insert into dbo.QueryTable values ('update [dbo].[Customer_NA] set CreatedOn=Null')
Insert into dbo.QueryTable values ('Delete from [dbo].[Customer_AS] where FirstName=''Sukhjeet''')

--Insert incorrect Query to Generate Error in SSIS Package
Insert into dbo.QueryTable (Query,QueryRanStatus)
values ('update [dbo].[Customer_NA] set CreatedOn=getdate(86'')',0)

--Delete the Record from Table
Delete from dbo.QueryTable
where Query='Delete from [dbo].[Customer_AS] where FirstName=''Sukhjeet'')'


--Test if all queries completed successfully.
select * from dbo.customer
--Truncate table dbo.customer
Select * from [dbo].[Customer_NA]
Select * from [dbo].[Customer_AS] where FirstName='Sukhjeet'

--update the queires to rerun by using SSIS Package
update dbo.QueryTable
set queryranStatus=0
where id=3

--Check if queries ran successfully
Select Query,ID ,QueryRanStatus  from dbo.QueryTable where QueryRanStatus=0




Execute SQL Statements in SSIS Package from a SQL Server Table




 Related Posts / Videos on Execute SQL Task