How can I use variables and SQL code within an SSIS package?

Question

I have an SSIS package I am building to take data from a .CSV file and load it into a table in a SQL Server database. The .CSV file has more columns than my table and I'm looking to filter out the data based on some of these columns that are not being inserted into the table.

I have year, kind, type, dollars as my column names in the .CSV file but I'm only pulling type and dollars into the DB. However, I can only pull those rows where the kind= "L" and year is the current year (with one major caveat). If the process is running in the first quarter of a given year (so month <= 3) it needs to use the previous year as my qualifier for what rows it pulls in from the .CSV file. For instance, say it is February 2015 when this package is running, I need it to pull only rows with a year of 2014 and kind="L" from my .CSV file. If it is September 2015 then it needs to pull in rows with a year of 2015 and kind="L".

Any idea what the best way of doing this is? Right now I have a conditional split in my package but I can only get it to say year==YEAR(GETDATE()) and this will not work for the first quarter. I'd need some sort of variable logic to say something like IF(currentmonth<=3 THAN @year = currentyear-1) ELSE (@year = currentyear) and then use the @year variable in the conditional split. Is this possible?

Any help is much appreciated!

Answer 1

If you want the logic to be in the SSIS package you can use a derived column component to declare a new boolean field for example IncludeRowInOutput and set it to be something like

((currentmonth <= 3 and year = year(getdate() - 1)) or (year = year(getdate))) and kind = 'L'

Then you can do the conditional split based on the IncludeRowInOutput field.

I'd normally be wary of using too much script components, I find that they are harder to debug and make the dataflow harder to understand.

Answer 2

Normally for this kind of workflow, I will import the entire CSV into a temporary table, and then have a separate SQL script or view which reads from the temporary table and applies whatever business logic is needed for the final view.