0

I have to do an incremental extraction using SSIS. My scenario is something like this: I need to find the maximum of the datetime from my table and store it in a variable that I am using in the where condition of my OLE DB Source. I am using the Script Component transformation to update my variable. The problem is that the maximum value gets assigned to the variable while executing, but when I try to execute it again the variable gets set to itself default value that I have provided. How do I make the variable value persistent?

Yousuf Sultan
  • 3,055
  • 8
  • 35
  • 63

1 Answers1

1

Instead of using the Script Component, couldn't you use a Execute SQL Task first?

In the SQL task, in the General tab you define your connection and then your SQLStatement that gets the max date. And you set ResultSet to "Single row".

Then in the tab Result Set map the result to the variable you already have.

How to assign value to variable with SQL task

Rupal
  • 482
  • 4
  • 8
  • 14
  • I tried using that as well but if I execute the package again without any new insertion in my source then still the last record gets inserted into my target which has already been inserted. – Yousuf Sultan Oct 11 '13 at 12:23
  • I'm not sure I follow. From what I understand, you want to extract from table A to B, where you only get rows in A greater than the max date in B. So in my case I would, like mentioned above, find the max date in B, then store in in variable, and then extract from A where date > @maxDate. Each time you run, the code should fetch the max date. Maybe I'm not understanding the whole issue.. – Rupal Oct 11 '13 at 13:49
  • Actually the whole issue is about Incremental Extraction. I need to get only the new records from the source table based on the date column that I have in my source. – Yousuf Sultan Oct 11 '13 at 15:36
  • Why not use the last date from **destination** instead in order to incremental extraction from source? – Rupal Oct 14 '13 at 07:55
  • I can do that. That is not a problem, but I want to use a variable and keep it updating on every execution. I want to get that last date and assign it to a variable. – Yousuf Sultan Oct 15 '13 at 09:47
  • From what I understand, that is exactly what I tried to describe in the original answer... (Have added a link to step by step how-to) – Rupal Oct 15 '13 at 13:32
  • Hi Rupal... Thanks for the link... I have implemented exactly the same procedure... but I don't where I am going wrong.... It's not working out for me... – Yousuf Sultan Oct 17 '13 at 09:24
  • Can you add a screenshot of your config? – Rupal Oct 17 '13 at 12:35