Post reply

Annoying SSIS Truncation Error

  • June 10, 2009 at 6:31 am

    #138589

    I have an expression used for an Excel Connection Manager. I'm replacing the ExcelFilePath with a dynamic naming convention and I want to keep zeros before months and days if those values are 1 digit instead of two. So '6' will print as '06', '10' will print as '10', etc.

    To achieve this, I'm replicating zero for Len(Month(GetDate())) - 2 times. Now the package is complaining about the possibility of the string exceeding 4000 characters when I know it doesn't. I can't figure out how to change my code to get rid of the error. I tried casting the REPLICATE string to a length of 1, but that didn't work. And I don't know where to look in the package to have it ignore this particular error. Thoughts?

    Error and expression code quoted below.

    Nonfatal errors occurred while saving the package:

    The result string for expression &ltMyExpression&gt may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.

    "\\\\ServerName\\ShareName\\FolderName\\MyExcelFile_"

    + (DT_STR, 1, 1252) (REPLICATE( "0", 2- LEN((DT_STR, 2,1252) MONTH( GetDate()))) )

    + (DT_STR, 2,1252) MONTH( GetDate())

    + (DT_STR, 1, 1252)(REPLICATE( "0", 2- LEN((DT_STR, 2,1252) DAY( GetDate()))))

    + (DT_STR, 2,1252) DAY( GetDate())

    + (DT_STR, 4,1252) YEAR( GetDate()) + ".xls"

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • June 10, 2009 at 6:44 am

    #1007318

    Nevermind. I finally figured out how to make the stupid Conditional Operator work correctly. It kept complaining that I couldn't use a LEN() function in conjunction with a string. a DT_WSTR and DT_I4 incompatability. Once I put the LEN() function in additional parenthesis, though, it stopped erroring out.

    New code:

    "\\\\Server\\ShareName\\Folder\\MyExcelSheet_"

    + (LEN((DT_STR, 2,1252) MONTH( GetDate())) == 1 ? "0" : "")

    + (DT_STR, 2,1252) MONTH( GetDate())

    + (LEN((DT_STR, 2,1252) DAY( GetDate())) == 1 ? "0" : "")

    + (DT_STR, 2,1252) DAY( GetDate())

    + (DT_STR, 4,1252) YEAR( GetDate()) + ".xls"

    If you test it, take away the paren to the left of the LEN and the last right paren before the + sign and you'll see the other error that was giving me a headache before I skipped to the REPLICATE function.

    Wow. SSIS can really be a pain sometimes over stupidly simple stuff.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 2 (of 2 total)

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