December 21, 2006 at 6:27 am
I am having problems troubleshooting a package which fails as a scheduled job. It starts to execute and fails a few steps in. Locating the exact step on which it fails I where I am struggling.
In DTS in SQL Server 200 I could query the dtslog for the execution of the package and link this to the execution of each step within the package. I can't see how you do this in the logging within SSIS.
Does anyone have any ideas?
Thanks
Daniel
December 21, 2006 at 6:49 am
I've worked it out.....
December 22, 2006 at 4:11 am
... how did you do it?
The best way I've found, so far, is to run the SSIS package as a command line from a SQL agent job, and under the advanced tab for the step, create a log file. That contain detail that, for example, yesterday allowed me to point to a error in an input file where I was receiving invalid data that was breaking primary key constraints on target destination.
I'd be interested in hearing what you did, and how well (and easy) it worked
December 22, 2006 at 4:58 am
I'd be interested in hearing this too. Please post your solution.
Thanks, @=)
January 2, 2007 at 2:59 am
Hi,
First thing you need to do is set your packages to log to your SQL Server. For more info on this check the following.
http://msdn2.microsoft.com/en-us/library/ms138020.aspx
SSIS will then log event messages to the database you specify within the stsdtslog90 table. In the next 2 posts i'll put the Sp's I have written to query the logs.
Regards
Daniel
January 2, 2007 at 2:59 am
Create Proc uspDTSLog As
Select
PackageStart.id,
PackageStart.source,
PackageStart.operator,
PackageStart.computer,
PackageStart.starttime,
PackageEnd.endtime,
DateDiff(s,PackageStart.starttime,PackageEnd.endtime) As duration,
--PackageStart.sourceid,
PackageStart.executionid,
Errors.[message]
From
(Select top 1000
id,
source,
operator,
computer,
sourceid,
executionid,
starttime
From
sysdtslog90
Where
event='PackageStart'
Order By
id desc) As PackageStart
Left Outer Join
(Select
sourceid,
executionid,
endtime
From
sysdtslog90
Where
event='PackageEnd'
) As PackageEnd
On PackageStart.sourceid = PackageEnd.sourceid
And PackageStart.executionid = PackageEnd.executionid
Left Outer Join
(Select
sourceid,
executionid,
[message]
From
sysdtslog90
Where
event='OnError'
) As Errors
On PackageStart.sourceid = Errors.sourceid
And PackageStart.executionid = Errors.executionid
Order By
PackageStart.id desc
January 2, 2007 at 3:00 am
Create Proc uspDTSLogSteps
@executionid Varchar(128)
As
--Declare @executionid Varchar(128)
--Set @executionid = '2E1EECB0-AF27-4D9F-85FC-4AF367AE988C'
Select
id,
source,
event,
operator,
computer,
starttime,
message
From
sysdtslog90
Where
executionid = @executionid
Order By
id asc
January 2, 2007 at 4:27 am
thanks for the responses Daniel. Haven't played with the SQL logging since my 1st/2nd package - our client preferred to to log for SQL.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply