October 12, 2007 at 4:53 pm
Hi Guys,
I need help please, so thanks in advance for any response.
Currently I have a logger that writes to a text file. I need to make the connection string for the file manager dynamic so that destination can be changed, ideally via an entry in a package configuration file. Now, having tried using variables, expressions and setting the connection value via an XML package configuration file, I am yet to succeed. Every time I recieve an error saying that SSIS is unable to locate the file. I assume that this is becuase the logger starts before any dynamic connection string can be set - hard coding the fle destination to C:\log\ for example, works fine.
I'm new to SSIS so am not sure if I'm approaching this in the correct way, or indeed this can be done at all (i assume it is, anything is possible right?), so any guidence on how to do this will be much appreciated.
Thanks,
James
October 12, 2007 at 7:02 pm
Hi Pat
Not sure what the problem is. How are you logging?? If you're using a stream via System.IO in vb.net - then I don't know what the problem is!!?? I do this all the time and pass in the log file path/name by a task- or package- variable. You know you can get at the variables by the dts object as long as you specify them in the script task editor
Hope some of this sticks.
October 13, 2007 at 3:31 am
Thanks for your reply. Having started fresh this morning (after racking my brain for hours yesterday) I have now solved the issue.
For the completness of the post I'll explain the problem.
I have a SSIS log porvider for text files outputting to a File Connection Manager in my package. I need the connection string of the file manager to be dynamic so I'm able to change the destination of the log. I was using an expression on the connection string of the File Manager to incorporate a variable into the output file path. Previously I couldn't get this to worK, SSIS was saying that it could find the file. I thought that this may have been becuase the logger had started before any variables could have been set (the variables are set via a XML package configuration file at runtime). I was partly correct. My expressoni on the ocnnection string was:
(DTR_STR, 50, 1225) @User::LogDir + "\\ErrorLog.csv"
Becuase the LogDir variable is set at run time, I left its value blank in the package design thinking it would be ok. However, this was my error. I found that this needs to be set. At runtime, SSIS will create an initial log using the hardcoded value of the variable and then create another log for the variable connection string and continue to log into that. Nothing is logged in the initial log but it needs to be created before it can create the dynamic log. So, the process went:
Package Design:
File Manager Connection Sting Value = c:\ErrorLog.csv
- which is made up of variable User::LogDir (hardcoded value = c:\) + expression = "\\ErrorLog.csv"
RunTime:
Log created at: c:\ErrorLog.csv
LogDir variable value change to = e:\ via Package Configuration File
Log Created at e:\ErrorLog.csv
So now i'll have to add a task to delete the initial log, bit dirty, but it'll have to do. If anyone knows an cleaner way please let me know.
Thanks again for your response,
James
October 13, 2007 at 7:11 am
Sounds like a good solution to me. Thanks, James.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply