How to Save Multiple Records Returned by SQL Query in Execute SQL Task ( Full Result Set) in SSIS Package -SSIS Tutorial


We have created an SSIS Package to Load the Flat files to SQL Server Table and Archive them after load. But we got this new requirement. The vendors can place multiple files in the Folders but we should not be loading all of them at all. Instead of that the vendor will have access to a table in which they will log the FolderName and FileName those need to be loaded. We need to modify our SSIS Package to load files according to above requirement.

In this video you will learn

  • How to Create SQL Server Table to save Folder path and file name information
  • How to use Data Flow Task to Load Flat File to SQL Server Table
  • How  to create variables in SSIS Package to hold values returned by Execute SQL Task
  • How to return multiple values with Multiple rows and save that in Object Type variable by using Execute SQL Task
  • How to Loop through Object Type variable in For-each Loop Container to use one row at a time
  • How to write Expressions on Connection managers in SSIS Package

Script used for Demo: Load the files for which Folder path and name is provided in the definition table.

--Create a Table to save folder path Save names which need to be loaded
Use Test
Create Table dbo.FilesToLoad
( FolderPath VARCHAR(100),
FileName VARCHAR(100))

--Insert file names and folder path those need to be loaded by SSIS Package
Insert into dbo.FilesToLoad

Insert into dbo.FilesToLoad

--Check the results from Table
Select FolderPath,FileName from dbo.FilesToLoad

Get Folder Path with File Name from SQL Server Table and Load those Files in SSIS Package

 Related Posts / Videos on Execute SQL Task