Post reply

Dynamic Excel Connection Manager

  • July 23, 2012 at 9:20 am

    #151005

    I am attempting to have a Dynamic Excel Connection Manager. Within the Expression I am wanting to append the date to the end of the file. I had this in the expression and it Evaluated fine:

    "C:\\CheckPrePay\\PrePayCommit_"+ (MONTH( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) MONTH( GETUTCDATE() ) 🙁 DT_WSTR,2)MONTH( GETUTCDATE() ))+(DAY( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) DAY( GETUTCDATE() ) 🙁 DT_WSTR,2)DAY( GETUTCDATE() ))+(DT_WSTR,4)YEAR( GETUTCDATE() ) +".xlsx"

    But it also gave this error:

    The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

    When I Googled that error I found that you have to put some extra Excel Specific wording in the front and the end, attempting to do this is not working out too well for me.

    Adding the first part, I again get it to Evaluate successfully:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\\CheckPrePay\\PrePayCommit_"+ (MONTH( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) MONTH( GETUTCDATE() ) 🙁 DT_WSTR,2)MONTH( GETUTCDATE() ))+(DAY( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) DAY( GETUTCDATE() ) 🙁 DT_WSTR,2)DAY( GETUTCDATE() ))+(DT_WSTR,4)YEAR( GETUTCDATE() ) +".xlsx"

    This returns: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CheckPrePay\PrePayCommit_07232012.xlsx

    Adding the last part, something similar to + ";Extended Properties\="Excel 12.0;HDR=NO\";" is not working to well.

    So far this is what I have:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\\CheckPrePay\\PrePayCommit_"+ (MONTH( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) MONTH( GETUTCDATE() ) 🙁 DT_WSTR,2)MONTH( GETUTCDATE() ))+(DAY( GETUTCDATE() ) < 10? "0"+(DT_WSTR,2) DAY( GETUTCDATE() ) 🙁 DT_WSTR,2)DAY( GETUTCDATE() ))+(DT_WSTR,4)YEAR( GETUTCDATE() ) +".xlsx" + ";Extended Properties\="Excel 12.0;HDR=NO\";"

    And this will not Evaluate, I keep googling stuff and trying tweeks, buy nothing is working.

    Any and All help will be greatly appreciated.

    Thanks!

  • July 23, 2012 at 2:55 pm

    #1516485

    try to add Excel File Path property instead of connection string in connection manager ,connection expressions.

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

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