Search code examples
sql-servercsvssisetlflat-file

SSIS - Is there a way to filter data from a flat table?

I have a general ledger transactions down to the individual journal bookings. I am working on a process to rebuild these booking from the bottoms up using tables and other information that is located in other places. To start, I have an SSIS package that pulls in 3-4 different "Divisions" worth of data.

In one case, there are over 600k lines, and I'll need at most 50k. The 600k being loaded into a table takes a while. I was looking for a way to head that off. If I were doing it in SQL Server, I'd do something like:

SELECT * FROM C601
WHERE (COST_CENTER = 5U AND ACCOUNT = 1100001)
OR (COST_CENTER = 5U AND ACCOUNT = 1300001)

I'd have about 12-13 total WHERE items, but would reduce it to maybe 10% of the original items. Is there a way to filter the flat file loading in SSIS with far fewer items before I load the SQL Server table as I would with SQL above?

Solution

  • Use a Conditional Split Transformation

    1st approach

    Add a similar expression:

    [COST_CENTER] = "5U" && ([ACCOUNT] = 1100001 || [ACCOUNT] = 1300001)

    2nd approach

    Or you can add two split expression as following:

    COST_CENTER]!= "5U" || [ACCOUNT]!= 1100001

    And

    [COST_CENTER] != "5U" || [ACCOUNT] != 1300001

    Then you can use the Conditional Split default output to get the desired result.