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  

3 comments:

  1. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete
  2. And how could I do it, but with a sql table?

    ReplyDelete
    Replies
    1. Apparently it already worked with a database, but now I have the problem that it finds null or empty values. And that creates an automatic error. Any way to fix it?

      Delete