August 4, 2010 at 1:42 pm
Hi
I am running my package on a weekly basis but the day and time is not fixed as its not put on a job, I can run it as a job as well, my concern is when the package is run i want it should transfer a run date and time, so that when my data is tranfered to SQL , I want to run the report in Report manager where I can access the field which has the date stamp of the last run of the SSIS package.
I have created a field on the destination tablle as 'Rundate" but I dont know how the date would get transfered.
I am newbie in SSIS.
Thanks in advance!
Balwinder
August 4, 2010 at 1:59 pm
My solution for this is a little more general purpose that what you're looking at, but it should work.
Turn on SSIS Logging. From the menu: SSIS > Logging. Check the box next the package name, and give it a provider. Personally, I have a database called Logs that was perfect for this sort of thing. On the details tab, personally I check everything, and it's a good start. You can turn some logging off if you find you don't need it.
It will log to a table called "sysdtslog90". The format is fairly straight-forward. Group by executionid and take the MIN(startdate) and MAX(enddate). You've got your total run time. I built a report on this that shows me the start and end times of each execution, as well as a flag if there were any onError or onWarning events. I can then drill down into it to see the whole log for a specific executionid. It's tremendously useful, and if you set up something like that, all you have to do is turn on logging on all your other SSIS packages, and they'll start showing up on the report as well.
August 4, 2010 at 2:00 pm
bsembhi (8/4/2010)
HiI am running my package on a weekly basis but the day and time is not fixed as its not put on a job, I can run it as a job as well, my concern is when the package is run i want it should transfer a run date and time, so that when my data is tranfered to SQL , I want to run the report in Report manager where I can access the field which has the date stamp of the last run of the SSIS package.
I have created a field on the destination tablle as 'Rundate" but I dont know how the date would get transfered.
Thanks in advance!
Balwinder
In your data flow, you can use "derived column" to populate the field "rundate" with getdate() function.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 4, 2010 at 3:10 pm
Thanks buddy! It worked!
August 5, 2010 at 3:31 am
An alternative is to set the default value of your run date field in SQL Server - then it will populate itself whenever a record is created - no SSIS work required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2010 at 11:28 am
Hi
I am now trying to run the same package as a job and its not running it gives me error as follows
"
JOB RUN:'MHRosterVerification' was run on 8/5/2010 at 1:11:08 PM
DURATION:0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by User ndtsa\Administrator. The last step to run was step 1 (MhRosterVerification).
"
I have the following steps for crrating the job
1) in the "General" i have Owner : ndtsa\Administrator
Category : {Unrecognized(Local)}
check box "Enable" was checked by default
2) Under "Steps" i Have step name: MHRoster....
Type: SQL SERVER INTEGRATION SERVICES
Run as: SQL AGENT SERVICE ACCOUNT
then under "General" tab Package source : File System
Package : i gave the path where package is stored
Under "Data Sources" tab initially none of the Connection Manager's were checked , when i saw the package was giving error I check these boxes, still running into problems.
Please help
Thanks
Balwinder
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply