CODEWITHSUNDEEP
ssis
AaronGDL
AaronGDL

Reputation: 69

SSIS - How can I use the value of a variable to determine the ForEachLoop Container?

I'm working on an SSIS package, the goal of the package is to take a spreadsheet that has several columns (we need PartNum, PartType, and Qty)

and for each row in the spreadsheet, run a query to calculate consumption and dump that into a separate sheet.

I've got a few problems, but my initial problem, is that I have two part types, Manufactured and Purchased. I only want to run the query against Manufactured pieces. How Can I do that in SSIS? I'm trying to set it up in the expression builder for the variable to equal "M", but this always evaluates to false.

Ideally, I want to filter on both Part Type = M and Qty > 0.

Here is the variable:

Here is a picture of the SSIS package, basically I'm using a data flow to bring a spreadsheet into a Recordset, and then in a Foreach loops, an OLEDB Source to pass query parameters (the part and qty variables) to export into a .csv

SSIS Package

Upvotes: 1

Views: 42

Answers (1)

userfl89
userfl89

Reputation: 4810

In the initial Data Flow Task from the Excel Source into the Recordset Destination, instead of loading the entire Excel file just select records that satisfy the given criteria. Unless you need these records for another purpose in the package, this will also prevent adding unused rows in the Recordset Destination and processing them in subsequent components. You can do this in the Excel Source by changing the Data Access Mode to SQL Command and adding the necessary filters. Excel can be queried similar to SQL. The query you want should be somewhat similar to the following, with the table and column names substituted appropriately. If the columns contain spaces in their names, these will need to be enclosed in square brackets. For example, PartType would be [Part Type].

SELECT 
PartNum, 
PartType, 
Qty 
FROM Excel_Sheet 
WHERE PartType = 'M' AND Qty > 0

Upvotes: 1

Related Questions