0

I want to filter a column that spans from 2014-2019 to 2017-2018 in VS with SSIS. I have tried different things but none seem to work.

The column

The data conversion

The filtering I have a question about

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
MathieuAPLep
  • 11
  • 2
  • So, what are you trying to do, and what about it isn't working? – Eric Brandt Dec 23 '19 at 13:54
  • 2
    Either filter it in the source SQL query (recommended) or use a conditional split (not recommended) – Nick.Mc Dec 23 '19 at 13:56
  • +1 to moving logic to SQL query. If staying inside SSIS with conditional split, cast your date string to date type before comparing with [week]. Otherwise use the DateTime Function YEAR([week]) and just look at year. – vhoang Dec 23 '19 at 15:00

1 Answers1

0

Derived Column date in your example is likely what you're looking for.

The Week column is of a date type DT_DBDATE. Your string "2017-01-01" should be getting promoted to a data date type so the boolean check will identify if the lower bound is being met.

You'd either need to create a second derived column to check against the upper bound or as @vhoang indicates, change the logic to just extract the year from the date column.

YEAR([Week]) >= 2017 && YEAR([Week]) < 2019

Now, you have a column that flags each row as meets criteria or not (year is 2017 or 2018)

You will then need to do something with that. The SSIS something is called a Conditional Split. I would add a new path called OutOfConsideration and the logic there would be the inverse of our above Derived Column Derived Column date which is true if the year meets our criteria. 

![Derived Column date]

Now connect your destination, or additional processing steps, to the Conditional Split's default output path. If you need to do processing on the invalid data, that'd be the OutOfConsideration path.

Finally, to get the best performance out of SSIS, only bring the rows into it that you need. If the source data is in a system that supports filtering, filter the data there. It is easy to click click click design SSIS packages but it is better long term for you to write custom queries to only bring the required columns and rows into the data flow. Less work for all around, lower maintenance cost, etc

billinkc
  • 59,250
  • 9
  • 102
  • 159