public query - 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

SSIS and Import/Export (2005)

public query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-07 : 07:53:45

i write query (INSERT INTO LOG SELECT .....)
and place it in Event Handler.

i want to put it in one place and not to make copy-paste in each packages in container.
How can i do it?

(i need to do it in all containers)

thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-07 : 13:41:38

You can use a variable expression, maybe.

What are you doing in the INSERT statement?
You can log directly to a SQL table by using the package logging and enable the Log Provider for SQL Server.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-08 : 07:36:21

i make something like that:

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

i want to do it in each package and i don't want to copy-paste.
how can write in global variable that all solution can use it?(not my solution,i want to use it in all solutions)

thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-08 : 16:57:24

Maybe this approach will work for you: http://www.sqlservercentral.com/blogs/michael_coles/archive/2007/10/09/3012.aspx
This method modifies the sp_dts_addlogentry procedure to perform custom logging.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-10 : 10:00:11

it give me direction, but i have 2 problem :
first i use with sql 2008 and i dont find the procedure :
sp_dts_addlogentryand i dont find the table sysdtslog90 .

do you have link for 2008?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 11:10:58

In SQL Server 2008, the objects were renamed to
table: dbo.sysssislog
procedure: [sp_ssis_addlogentry]

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-10 : 14:11:54

thanks, i have 2 more question:

1. what is the best way that you use for log in ssis?
2. i can do the same ,catch event that i want and make query on SSISLog table

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 17:26:53

1. I log to text files. The best way would depend on your environment and requirements.
2. Sorry. I don't understand the question.

BTW, this may be useful to you: http://ssisloganalyzer.codeplex.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-11 : 01:19:44

first thanks of link(i dont think that it will help me,becuase i need every day to check the dts- meandwhile we use sql 200,so i looking something same to log in sql 2000)

2.i say,i can write query of ssislog table instead create a new one (AuditPackage).
-- i create a Log Provider for SQL Server in my package
-- choose the event
-- write custome query of ssislog table to get the rows that i want

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-11 : 03:41:20

2. Yes. That would work (in SQL 2005 and 2008).

Above you say that you are using SQL 2000. What are you doing for the logging here?

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-11 : 10:56:43

i am not using sql 2000, i upgrade to sql 2008 from sql 2000

why do you use log file instead log in sql ?

until now, i don't find log that willl be global to all my packages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources