HoW to design incremtal load in ssis based on DateTimeStamp

Hi

I need to design incremental load packages.we have field in the source named IssueDate.so can i do incremental loads based on this.

Plz suggest me the ways to do incrementalload packages in ssis.

Thanks

You will need to elaborate on what you need, for me to give you a better answer. But here is a short description.

Create a variable in the SSIS package and set the value to the date you want to go back to. Then use this as a parameter in the SQL statment you are using the pull the data. Example

Select * from Table where issuedate >= ?

The question mark is the parameter.

Hi

Thanks.

Hi There -

I think in an ideal case scenario, you should create two variables in SSIS as StartDate & EndDate and while pulling the data from source, your WHERE clause should look something like this:

SELECT Col1, Col2, ... , Coln FROM TableX

WHERE ModifiedDate >= StartDate and ModifiedDate <= EndDate

This is especially essential where the data in source is continuously updated. You should stop pulling the data which has come after your ETL's have started, to make sure that the data is consistent.

Example: At 10:00 AM data load starts and you have two tables TableA (Primary Key Table) and TableB (Foreign Key Table). Now assume your ETL's take20 Minutes to load, 6 Minutes for TableA and 14 Minutes for TableB, so your ETL's would complete by 10:20 AM. Now if you dont use the above condition that I suggested, what might happen is that a key value xyz got loaded into your TableA and corresponding record in TableB at around 10:05 AM 55 Seconds when the data is being commited into TableA. Now if you pull all the data with only condition as ModifiedDate >= StartDate then this xyz will try to load into TableB and it will throw foreign key constraint violation.

Also, please note that when the date time values are stored in a SSIS date time variable then the milli seconds portion gets truncated.

Hope that helps!

Thanks,

Dattatrey Sindol (Datta)

Blog: http://mytechnobook.blogspot.com/