1

I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. Each row represents a doctor. Below image shows how I am able to do this successfully. No problems upto this point. enter image description here

Here's what I need help with:

If the doctor is no longer active we are going to get the same .CSV file without the record of him/her. How do I check to see if the record is not in .CSV file but it exists in SQL database? I need to update that doctor row in SQL database and update the IsActive field for that row to false.

3
  • 2
    Is there a reason you can't just import the .CSV into a staging table and then do an outer join with the destination table?
    – Queue Mann
    Commented Jul 22, 2015 at 15:22
  • hey @QueueMann I am new to SSIS and I am no dba either. So pardon me if I am asking the obvious question. How will I create a staging table in my ssis package ? Commented Jul 22, 2015 at 15:36
  • Your OLE DB destination will specify a table in your database. A staging table is just a term used to refer to simply another table in your database that typically exists simply to hold imported data.
    – Queue Mann
    Commented Jul 22, 2015 at 16:14

3 Answers 3

1

How do I check to see if the record is not in .CSV file but it exists in SQL database?

Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a particuliar record is in the staging table or not.

This way you get better control of the process and only update the main table or add new records to the main table. You can do it using TSQL (Merge) or SSIS.

This is what we do and it incurs less overhead on the main table, since you will update or add only records that have changed.

Also, I really like the idea of doing a soft delete IsActive = TRUE or FALSE. This way you can preserve the history as well.

0

Not sure if this is a proper answer, but I'm too low rep at the moment to just comment. I have similar tasks that I achieve by comparing the load table to the target table by stored proc ( execute sql step in your ssis package ). Something like:

With Inactive as
(
-- returns doctor id's that appear in the Load table but NOT in the target table
    select doctorId
    from LoadTable
    except
    select doctorId
    from TargetTable
)
Update TargetTable set
    active = 'no'
from TargetTable tt
inner join Inactive cte on cte.doctorId = tt.doctorId ;

Hope this helps.

0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file?

The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat file as a reference set. I blogged about a similar approach for using Excel as a lookup source

Warm Cache

Add a Data Flow to your Package, call it something like "Warm Cache". Here you'll have a Flat File Source (for your Active Doctor File) and it will route into a Cache Transform. You'll need to identify the key(s) that uniquely define the row - most likely a provider ID. You might also want to add a Derived Column component in there to add a column called IsActive to true.

After that task runs, you'll have an in-memory representation of that file in a Cache Connection Manager. You'll then need to have a second Data Flow Task, this one is where the inactive doctor detection will be performed.

Inactive detection

Within this data flow, you'll have an OLE DB Source which pulls back the keys for your doctor table SELECT D.ProviderID, D.IsActive FROM dbo.Doctors AS D; Add a Lookup Transformation and change the source from an OLE DB Source to Cache Connection Manager and the behaviour for no match to Route to NoMatch. Connect the Source to the Lookup by matching on the business key(s) and pull back the IsActive flag from the lookup - call it lkp_IsActive.

From the Lookup, you'll have two output streams - things that matched and things that didn't match. The Didn't Match are things that should have IsActive set to False. The Match output will contain all the active doctors.

NoMatch output

In the NoMatch output, you'll need to update everyone in the Doctor table by setting their IsActive to false. The possibility exists that the unmatched row already has their IsActive set to false. You could have filtered that in your source query for this data flow (WHERE D.IsActive = CAST(1 as bit);) but I did not as that would preclude a Doctor that went inactive from every coming back active.

Since our nomatch stream contains both IsActive values of true and false, I'll add a Conditional Split to the data flow and route all the rows that don't need updated (IsActive == false) into an unused path. The default will be only the Active doctors that we just learned are no longer active by virtue of their absence from the source CSV which we materialized into our Cache Connection Manager.

If the number of rows to be updated is small, then an OLE DB Command can be used. Otherwise, route all the rows to a staging table and then perform a set based UPDATE via Execute SQL Task after the data flow completes.

Match Output

As noted in the NoMatch output, a doctor could have been active. The previous run they were inactive and now they are active again. Let's set those people as Active.

Add a Conditional Split here as well. We only want to update the rows that aren't active so I'd create an output that matches (IsActive == True) and route the default path into an OLE DB Command (or staging table) much the same as we did with the Inactive Detection. The IsActive is a special case but a more general pattern would be (IsActive != lkp_IsActive).

End result would look something like

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.