Logging in SSIS - 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 2005 Forums

Analysis Server and Reporting Services (2005)

Logging in SSIS

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-01 : 08:00:42

hi,

in dts(SQL-2000) we use package logs to see log each DTS but in 2005 we have 5 Integration Services Log Providers.
1.Text file
2.SQL Server Profiler
3.SQL Server
4.Windows Event Log
5.XML File

what is the comfortable way to use of log, i use Text file but i dont like to use it ,a lot of rows of information and i dont know when i have error.

what is the way do you use or prefer?

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 12:21:47

I would prefer SQL server Table if you have space.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 13:03:05

also see
http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-02 : 01:24:17

visakh16
i know this blog, why in dts it was simple and in ssis i need to create something new?i dont thinks this is the way.

suppose i have 30 package ,so i need 30 tables?

what do you use?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 01:27:52

nope. single table will be enough. just include column called package name to correspond to which package a particular entry was logged.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-02 : 01:39:12

what size that will take from 30 package that runs every day?
(do you prefer table than file txt? did i need to create a new Database?)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 01:45:27

i prefer table as that's more readable and infrmative than text file

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-02 : 01:48:45

do you put it in a new Database?
what size it will take if it run every day?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:15:20

Nope. we put it in database where we have all other logging info enabled eg for sql jobs....
We also archive old data over a period as it will take quite some space over a period of time

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-02 : 03:08:39

ok,
1.so i need to create a new db for the tables of logging-package.?

2.what do you mean archive old data archive old data ,do you delete the table of logging over a period of time?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 09:18:13

1.Not mandatory, but if you've so many logging tables, better to keep in seperate db
2. yup delete after certain period.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-02 : 09:51:57

thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 10:20:11

welcome

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-08 : 04:40:11

How can I include column called package name to correspond to which package a particular entry was logged. ?

i mean, i create DB "Log" , in SSIS i Create Logging to SQL.
after i run the package it create a table sysdtslog90.
the problem ,when i do it to another package it add the log to the same table,How can i add a column that the name of each package.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:20:57

to get the package name use the system variable system::packagename

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-09 : 01:18:37

and how i added it to the Logging(sysdtslog90 table)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:08:36

https://www.sqlservercentral.com/blogs/michael_coles/archive/tags/SSIS/default.aspx

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-09 : 06:09:36

tell me am i right?
in my each package in my ssis, i need to add an Execute SQL Task and
write

INSERT INTO AuditPackage
........
UPDATE AuditPackage
........
UPDATE AuditPackage
........

2.
@event, @computer, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message

this is paramaters that i need to define in each package?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 06:16:12

yup...it is

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-09 : 07:37:28

in the value for example @event, @computer, what i need to define?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:26:49

event will have values from @[system::ErrorDescription] variable,
computer will have value from @[system::MachineName]