-1

I have a 'Modified Date Column' in the source table and I need to extract the recent data

Recent data - Data in the date between the recent execution of SSIS package to till date(Current execution date).

How can this be acheived in SSIS?

Gowtham Ramamoorthy
  • 111
  • 1
  • 2
  • 13
  • 1
    I see you've also asked this question on Experts-Exchange. Please work with the comments provided. http://www.experts-exchange.com/questions/28744363/How-to-extract-only-the-recent-data-from-a-source-table-in-SSIS.html – Jim Horn Oct 15 '15 at 18:52
  • How do you define "recent execution?" Does it matter if the most recent execution ended in failure? – billinkc Oct 15 '15 at 21:07

1 Answers1

1

You can achieve that by using SQL Task to get the last execution date from Job History in msdb database and store it in a variable.

You can use this query

USE msdb
GO

DECLARE @JobName AS varchar(50)
--Put the name of the job that runs package
SET @JobName = 'UpdateFactura'

SELECT
  MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
  ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = 1
AND Sj.name = @JobName
GROUP BY SJ.name,
         JH.run_status
ORDER BY [Last Time Job Ran On] DESC
GO

In the SQL Task set single result and map the result to the variable. Now you can use the variable for retrieve the modified data from last package execution.

Let me know if this was helpful.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • @GowthamRamamoorthy Nice!. You can select my answer as **correct answer** or **up vote** it if it was useful. – alejandro zuleta Oct 15 '15 at 22:29
  • I stored the value in the variable in SSIS using an Execute sql task shape. I used another execute SQL task (witht the variable from connected SQL task)...to get the recent data with an SQL querey.. (Is this the right way of doing it ?)... Im trying to get the recent data before i perform the dataflow tasks .... – Gowtham Ramamoorthy Oct 16 '15 at 03:40