How to Execute SQL Queries From Excel File ( Execute SQL Task Demo) in SSIS Package - SQL Server Integration Services Tutorial

Scenario:

Let's consider we have some business users and we have trained them to prepare the SQL queries for updates,deletes and inserts for some specific business tasks. They prepare an Excel File with their queries on daily basis and we need to run those queries by using an SSIS Package.


What you will learn from this video
  1. How to read an Excel File in SSIS Package
  2. How to save the Data from Excel File to Object Type variable in SSIS Package
  3. How to Loop through Object type variable in For-each Loop Container
  4. How to Save record from Object Type variable into a variable in For-each Loop Container
  5. How to use the variable in Execute SQL Task when it contains SQL Statement.

Script used in the Demo Video: Extract SQL Queries from an Excel File and Run in SSIS Package

--Create Sample Tables
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 Sample Tables
CREATE TABLE [dbo].[Customer_NA](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL
) 

--Queries Used in the Excel file 
insert into dbo.customer values(1,'Test1')
Insert into dbo.Customer values (1000,'Test100')
update [dbo].[Customer_NA] set CreatedOn=getdate()
Delete from [dbo].[Customer_AS] where FirstName='Aamir'


--Queries to Verify Data after Executing SSIS Package
select * from dbo.customer
--Truncate table dbo.customer
Select * from [dbo].[Customer_NA]
Select * from [dbo].[Customer_AS] where FirstName='Aamir'



Execute Multiple Queries from Excel File in SSIS Package




 Related Posts / Videos on Execute SQL Task