CODEWITHSUNDEEP
sqlsql-serverexcelssisssis-2012
xorpower
xorpower

Reputation: 18963

Need to filter excel rows based on some condition in SSIS

I have a source excel files that contains few records. As of now, I am applying the filters in excel file, copying the new result set (after applying filters) and then paste in new Sheet in same excel and then load the new data set in SQL Server.

I want to automate the task so that I can avoid the copy-paste as there are larger number of files. I did Google Search about that and found out that Conditional Split can be helpful but when I applied the Conditional Split on the same, I didn't found out any difference in the final result set.

Hence requesting guidance for the same.

Upvotes: 0

Views: 3865

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

Let's say you want to apply three filters to some Excel data to three different columns to get your final data set.

Let's say your Excel sheet has the following columns:

  • ProductId (integer id field for a product)
  • AmountOfSale (values in dollars)
  • StoreId (integer id field for the store location of the sale)
  • QuantitySold (integer value)

Now let's say you want the following filters applied:

  • AmountOfSale > $100.00
  • StoreId = 13 (you only want to see data for StoreId 13)
  • QuantitySold between 1 and 10

I'm going to assume you have your Excel Source connection correctly configured inside your Data Flow Task.

Add a Conditional Split task and connect the output of the Excel Source to it. Go into the Conditional Split configuration editor. Click in the box under Output Name and give your filtered output a name, maybe something like PassedFilters. Next, we'll build the filter condition. Under Condition you would then add the following expression:

[AmountOfSale] > 100.00 && [StoreId] == 13 && ([QuantitySold] >= 1 && [QuantitySold] <= 10)

Hit OK and save the Conditional Split task. Now add your destination connection to your Data Flow. Connect the Conditional Split task to the Destination connector and SSIS/SSDT should pop up a dialog box for Input Output Selection. Choose your PassedFilters output from the Output dropdown and the Destination Input should be prepoulated. If it isn't, then choose the correct input. and hit OK.

Now only the rows that pass your filters will be sent to the Destination.

Upvotes: 4

Related Questions