June 14, 2012 at 1:42 am
Hello,
I tried posting the following in the "SQL Server 2008 General" forum yesterday without any replies, so hopefully by re-posting it here I may have more luck.
I am using SQL Server 2008R", so I hope that doesn't make too much difference.
Being new to SQL Server and SSIS in particular I set about following one of the many tutorials on using the For Each Loop control flow item in conjunction with using Package Configurations to dynamically provide the directory path to a set of files to be loaded.
After much trial and failure, I could not understand why my implementation of the tutorial sample wasn't working.
Finally, I seem to have found that the package variable of type String that I am using to hold the directory path is limited to 50 characters in length and as such my directory path of some 72 characters in length was being truncated.
I so far cannot find how - or if - I can increase the size of the relevant string variable to cope.
Also, as usual, a tutorial has completely failed to mention this limitation (or default) string length. This, particular tutorial failed doubly because typically a variable intended to hold a directory path can very opften exceed 50 characters in length.
In addition, whenever I change the directory path (held in an XML file) I have to exit BIDS and reload my project in order for the new path to be available to the package. I assume there's a better way to "reload" or somehow re-initialize the package variables to acomplish this.
Can any SSIS experts help please?
Regards
Steve
June 14, 2012 at 2:52 am
There must be some other issue, because SSIS string variables can go at least to 4000 characters.
ps: please don't crosspost. If someone else answers on your other thread, it will be a complete waste of his time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 4:06 am
Hello,
Thank you for responding and my apologies for cross-posting. Some sites I've seen appear to recommend this approach when not having any luck in a particular forum - apparently not all sites do this as I now know.
Well, I now appear to have "fixed" the string length issue I had. I had to start over and recreate the package from scratch and this has worked. I suppose the thing that worries me a little is that my limited experience (and knowledge) of SSIS has shown it to be a powerful yet unforgiving tool. Just yesterday I had an existing project become corrupt and unloadable for no apparent reason. An error dialog informed me that something had been removed or renamed, but I had done nothing of the sort as far as I'm aware.
Anyway, I digress.
Would you have any idea about solving my second query in my original post?
Thanks for your time.
Regards
Steve
June 14, 2012 at 4:43 am
What exactly do you mean with "directory path held in an XML file"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 5:00 am
Koen Verbeeck (6/14/2012)
What exactly do you mean with "directory path held in an XML file"?
Well, I have an SSIS package that loads a series of CSV files from a directory that's specified within an XML configuration file.
Thiis alows dynamic directory paths rather than a hard-coded one within the package. CHanging the <ConfuguyredValue> element within the XML configuration file will point the package to the desired directory when next executed.
The probkem I have is that during testing when I change the directory path stored in the XML file, the package doesn't pick this change up unless I first exist the package and reload it in BIDS.
June 14, 2012 at 5:03 am
You can right-click on the package in Solution Explorer and choose Reload with Upgrade. It's a bit easier than coming out of BIDS altogether.
John
June 14, 2012 at 5:10 am
I've never been brave enough to try that option out, just in case something got 'upgraded' inadvertently 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 14, 2012 at 5:15 am
John Mitchell-245523 (6/14/2012)
You can right-click on the package in Solution Explorer and choose Reload with Upgrade. It's a bit easier than coming out of BIDS altogether.John
Thanks John, that's just what I was looking for! 🙂
June 14, 2012 at 5:18 am
John Mitchell-245523 (6/14/2012)
You can right-click on the package in Solution Explorer and choose Reload with Upgrade. It's a bit easier than coming out of BIDS altogether.John
I didn't even know this option exists. Interesting 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 5:21 am
Phil Parkin (6/14/2012)
I've never been brave enough to try that option out, just in case something got 'upgraded' inadvertently 🙂
It does seem a strange name for the action. I use it all the time, though, and it's never bitten me.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply