Scenario:
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 go Create Table dbo.FilesToLoad ( FolderPath VARCHAR(100), FileName VARCHAR(100)) go --Insert file names and folder path those need to be loaded by SSIS Package Insert into dbo.FilesToLoad values('C:\Users\Aamir\Desktop\InputFolder\','CustomerFile_AS_20150417.txt') Insert into dbo.FilesToLoad values('C:\Users\Aamir\Desktop\InputFolder\','CustomerFile_NA_20150416.txt') --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
- Execute SQL Task Demo for Max Size of SQL Statement in Execute SQL Task Query Editor and Solution
- Execute SQL Task Demo- How to Build Dynamic SQL Query to Execute Multiple Stored Procedures those names are saved in a SQL Server Table
- Execute SQL Task Demo - How to Execute SQL Queries from a SQL Server Table in SSIS Package ( SQLStatementType= Variable)
- Execute SQL Task Demo - How to Execute SQL Queries from Excel file in SSIS Package ( Record Set Destination, Object Type Variable and Foreach Loop Container Demo as well)
- Execute SQL Task Demo - How to use Single Row Result Set in Execute SQL Task and Control the Flow Tasks by using that value ( Precedence Constraint Demo as well).
- Execute SQL Task Demo - How to use Insert Query in Execute SQL Task and Map the variables to Parameters ( Insert File Name and Record Count after Loading the File).
- Execute SQL Task Demo - How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package( Load File Name, Package Name , Record Count and File Load Time in SQL Server Table).
- Execute SQL Task Demo - How to build Query by using Variables in Execute SQL Task ( File Name validation and move files to Archive folder and Bad File Folder if does not validation correctly)
- Execute SQL Task - How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package
- Execute SQL Task ( use Variable value in Expressions Demo) - How to Reject Already Loaded Files and Load Only New Files in SSIS Package
- Execute SQL Task ( Handle Single Row Result Set Blank Error) -Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package
- Execute SQL Task ( Save Result Set to Variable Demo) - How to Return Deleted and Update
- Record Count from Execute SQL Task and Write to Flat File in SSIS Package
- Execute SQL Task ( Build Dynamic Query in Variable and use as Source ) - How to Save Query in Variable and Use in Execute SQL Task
- Execute SQL Task Expression's Demo - Using Variable in IF Clause in Execute SQL Task in SSIS Package
- Execute SQL Task ( Parameter Mapping Demo for Stored Procedures) - How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters
- Read Single Cell Value from an Excel File in SSIS Package
No comments:
Post a Comment