Post reply

SSIS: Package String Variable Length Issue

  • June 14, 2012 at 1:42 am

    #259770

    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

    #1500837

    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

    #1500874

    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

    #1500889

    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

    #1500894

    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

    #1500898

    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

    #1500901

    I've never been brave enough to try that option out, just in case something got 'upgraded' inadvertently 🙂

  • June 14, 2012 at 5:15 am

    #1500908

    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

    #1500909

    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

    #1500910

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy