August 12, 2008 at 8:24 am
I am trying to get creative with my ssis packages and do some dynamic logging to help with speed related issues and general information. I found this post that is close to what I am looking for. I am curious if there are better ways to do this or if there is any better best practices
August 12, 2008 at 8:28 am
It's not a bad way of logging. I have been using a similar version for a while and had no problems.
I would say though, include normal text file logging as well. If your package cannot connect to SQL to log the fact that it can't connect to SQL, it can always write to a text file.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 8:42 am
Would you be able to give me an example how to do that. that is where I am currently failing. I know what I want to do but not the how. this would be for either the text file or sql logging
August 12, 2008 at 8:48 am
The logging to Text or SQL (Not the version jamie is talking about) is quite simple.
Right click in the work space and select Logging.
1) Select a provider (Use text for now)
2) Click add.
3) Left hand tree view, select the top level node. Check the checkbox. You can refine this if you wish
4)Back on the right hand pane, select the provider in the list.
5) Create New COnnection Manager. Change to Create New File, Enter filename
6) In the details tab, select the events you want to log.
Simple as that. The same goes for any of the other providers listed.
Jamie's one is different but documented there.
As for your filename, hightlight the connection manager at the bottom. Press F4.
Go to expressions and set and expression for Connection String property. Using this you can dynamically change the filename for the log. I find a daily file works well. THis last step is totally optional.
HTH.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 9:53 am
Are you sure this is it. That is alot easier then Jamie's logging. As for the connection string how does it work with the expression? Or better yet what do I put in there for the connection string.
August 12, 2008 at 9:59 am
It is a lot easier than Jamie's. It's a lot simpler as well. One of the advantages of his is the row counts. But ultimately, they do the same thing. His could be more flexible as well.
As for the expression, look up SSIS expressions in BOL or google.
On jamie's site there is an example: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx
You then set the connection string of the connection manager (Highlight connection > F4 > Expressions > [...]) Choose connection string and past his code. When you click the Evaluate button, you will see the result. Change it to suit your needs such as drive letter etc. It's just building up a string.
A note: "\" is an escape character, you must use "\\" so "C:\\MyF Older\\" + @[User::SomeFileNameVariable] + ".txt"
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply