Checking to see if file ran - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Checking to see if file ran

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-14 : 20:17:40

How can I do this in SSIS? I have a label on the client side saying last run date. I want to check to see if my package ran successfully then I want to add the run date of the file in a table.

Would I do this through Script Task then added a sql query? The package would run every day.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 01:08:14

you can do this inside package itself. As the last step in package add a execute sql task to do update in table for last run date column. map the value to @[System:PackageStartTime] variable value. This task will be linked to previous task by means of a on success precedence constraint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 10:36:33

Thanks for your reply but could you assist me with this.

I created a table called LastrunDate

I next created a stored procedure :



ALTER procedure [dbo].[LastRun]

@LastRunDte datetime

as

INSERT INTO LastRunDate 

(LastRunDte)


VALUES 

(@LastRunDte)

I added the SQL Task to the end of my ssis package. Now what do I do please?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 10:48:56

I found this on the internet but don't know how and where to put it in SSIS?


DECLARE @dtLastRun datetime
DECLARE @chLastRun CHAR(26)
SELECT TOP 1 @dtLastRun =
CAST(CAST(run_date AS CHAR(8)) + ' ' + -- Convert run_date to DateTime data type
STUFF(STUFF( -- Insert : into Time
RIGHT('000000' + -- Add leading Zeros
CAST(run_time AS VARCHAR(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime)
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobhistory B
WHERE A.job_id = B.job_id AND B.run_status = 1
AND A.name = @JobName
ORDER BY 1 DESC
SELECT 'Job Last Run Date', @dtLastRun


IF @dtLastRun IS NULL SET @dtLastRun = '1900-01-01'
SET @chLastRun = CONVERT(CHAR(26),@dtLastRun)
PRINT 'Job Last Run Date = ' + @chLastRun

DELETE FROM [rep].[SQL_Overview_Last_Run_Date]

INSERT INTO [rep].[SQL_Overview_Last_Run_Date]
([LastJobRun_date])
VALUES
(@chLastRun)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 10:58:07

I think I'm getting closer...

I am running the SSIS job manually through BIDS (I hope I'm saying it right, running it manually by clicking on the green arrow).

I added this in my sql task :

select top 1 convert(datetime, rtrim(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 ) as run_datetime ,* from msdb..sysjobs sj from msdb..sysjobhistory where step_id=0 and run_status=1 and job_id=@job_id order by run_datetime desc

It failed because I don't have my package in msdb. Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:41:48

you can use earlier posted procedure and in execute sql task call it like
EXEC [dbo].[LastRun] ?

and in parameter mapping tab map the @[System::PackageStartTime] value to 0 (ie position of ? in above query)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 11:44:47

thanks I added this to the Execute SQL Task:

INSERT INTO LastRunDate

(LastRunDte)

VALUES

(getdate())

I ran it two times and today's date went in should I keep it like that? They both had different times on them.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 11:46:08

I want to learn new things so how do I do this?

and in parameter mapping tab map the @[System::PackageStartTime] value to 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 13:44:03

quote:
Originally posted by JJ297

thanks I added this to the Execute SQL Task:

INSERT INTO LastRunDate

(LastRunDte)

VALUES

(getdate())

I ran it two times and today's date went in should I keep it like that? They both had different times on them.

even this is fine. if you want to map it to system variable you need to put ? instead of getdate() and map it in parameters tab

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-15 : 19:55:05

OK Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 12:47:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources