We need to extract the records from a reference table for Lookup Transformation and we are using a Stored Procedure. As Reference Table has records for multiple regions and we get Source Files for each region, we don’t want to put all the records in memory if we can just get the records related to Source File. The Stored Procedure accepts the RegionCode as Parameter and get the related records.
How can we pass the parameter value to stored procedure in Lookup Transformation that we will extract from file name in SSIS Package?
What you will learn in this video
How to create SSIS Package from scratch
How to Loop through files by using For-each Loop Container in SSIS Package
How to Read the part of File Name and Save into Variable by using Expressions
How to make use of Variable to make Lookup Query dynamic for Stored Procedure in SSIS Package
How to filter the rows for Lookup Reference Data Set by using Variable Value in Stored Procedure in SSIS Package
Link to the blog post for this video with script if used
Check out our Full Step by Step SQL Server Integration Services(SSIS) Tutorial
Take the opportunity to connect and share this video with your friends and family if you find it useful.
Keywords: Lookup Transformation in SSIS Package, SSIS Package, SQL Server Integration Services Interview Questions and Answers, SQL Server 2014, SSIS, Database, MSBI, Business Intelligence, Business Intelligence Tools, ETL Tools, SQL Server development, SSIS 2014, SSIS Tutorial for beginners, Tech Brothers, Real Time SSIS Scenarios and Solution, Stored Procedure, Lookup Transformation in SSIS Stored Procedure Parameter Demo, SSIS realtime Training, TechBrothersIT