February 3, 2012 at 2:16 pm
I enabled logging in an SSIS package in SQL Server 2005. I selected "log provider for SQL Server", created a connection to my database. Test connection works. All the containers have checkmarks and the provider is check marked. Events OnError, OnPostExecute, and OnTaskFailed are checked.
When I run the package it does not create a table in my db. The package has successfully and with errors. I've deleted the connection, disabled logging and redone everything (server times!), but it makes no difference.
Also tried writing the error log to a text file and that didn't work either.
Just had a thought, at some point I copied and renamed the project. I'm not sure if I have the original project or the copy. I deleted one. Could that cause the problem?
Any suggestion will be greatly appreciated.
February 6, 2012 at 12:37 am
Did you check the system tables? If nothing pops up you can try to run a trace with profiler against the database when the package is running, to see if something comes through.
Renaming or deleting a package does not have an influence on the logging.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 1:39 pm
Sorry it takes me so long to get back to this.
There are no tables in the database. I figured out how to run the default trace, and that shows the insert from the flat file into my Admissions db. But there's nothing for Error db. I created a test table and that's in the trace as object added. What events should I put in the trace?
Thanks,
Linda
February 7, 2012 at 2:14 pm
It's been a while since I used profiler, but I think RPC:Completed and SQL:BatchCompleted should do the trick.
Scenarios for Using SQL Server Profiler
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2012 at 2:14 pm
I've made some progress. 🙂 I ran the package in debug mode and it created the sysdtslog90 table. It does not display in Management Studio, but I can query it.
When I run the package in debug mode logging works, but if I just execute it it doesn't. Nothing I've read says logging only works for debugging. Is this the way it is supposed to work?
Linda
February 8, 2012 at 2:36 pm
Linda Robinson (2/8/2012)
I've made some progress. 🙂 I ran the package in debug mode and it created the sysdtslog90 table. It does not display in Management Studio, but I can query it.When I run the package in debug mode logging works, but if I just execute it it doesn't. Nothing I've read says logging only works for debugging. Is this the way it is supposed to work?
Linda
No.
Did you refresh the tables in ssms?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2012 at 3:11 pm
This time when I opened SSMS, sysdtslog90 is there! I really did try Refresh.
If I click on the package and select Execute Package logging works. It doesn't, if I execute the Data Flow task. Doesn't make sense to me.
I'll try setting up a job and see what happens.
Thanks for your help.
Linda
February 9, 2012 at 12:03 am
Linda Robinson (2/8/2012)
This time when I opened SSMS, sysdtslog90 is there! I really did try Refresh.If I click on the package and select Execute Package logging works. It doesn't, if I execute the Data Flow task. Doesn't make sense to me.
I'll try setting up a job and see what happens.
Thanks for your help.
Linda
Wait wait.
The table didn't appear when you executed the data flow. Do you mean you right-clicked on the task and selected 'Execute Task'? If yes, then the behaviour is normal. Logging is only started when the full package is executed, not only a single task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 7:36 am
Got it! I have to run the whole package for logging to be recorded, even though I can set up logging for just part of it. That was the piece that no one explained.
Thanks for your patience in helping me get this figured out.
Linda
February 9, 2012 at 9:54 am
Linda Robinson (2/9/2012)
Got it! I have to run the whole package for logging to be recorded, even though I can set up logging for just part of it. That was the piece that no one explained.Thanks for your patience in helping me get this figured out.
Linda
It's useful that you mention something like that at the beginning 😉
Glad that it works after all.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply