Error in dynamic connection string for Excel

• MWise

  SSCarpal Tunnel

  Points: 4074

  More actions

  July 15, 2010 at 8:27 am

  #1193517

  I suggested several ways that you could append the date and time to the file name:

  1. Rename the file AFTER loading to the database

  2. Use a variable to hold the date and time - set this once and reuse the variable so the value stays the same during the entire processing sequence

  Secondly, the reason why this might have worked with a flat file is because your Excel Connection String syntax is wrong. Please use the syntax that myself and others have posted. Yours is missing the necessary quotes around the Excel 8.0;HDR=Yes;IMEX=1; portion of the string. You need the backslashes before and after this portion of the string as escape characters:

  This expression:

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourceFile] + ";Extended Properties=\"" + "Excel 8.0;HDR=Yes;IMEX=1;"+ "\""

  Will evaluate to:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myfile.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"

  Have you tried fixing your connection string? Have you tried any of the suggestions here? Dynamic Excel connection work - I have them running on my system daily, so it is something on your side that you aren't setting correctly.

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  July 16, 2010 at 3:12 am

  #1193955

  Friends,

  The dynamic connection for excel is not working for me when I'm appending the full time string, other wise it will work.

  As I have mentioned when the connection string contain SECONDS (DATEPART("ss",GETDATE())) part then I'm not able to execute, but if I remove the SECONDS part and keep till minutes , the package will execute within the minutes (several number of times), but when it crosses that minute it is giving error.

  Then I have to double click the Excel destination and do the remapping, again the package will work for the duration of that minute...

  So what I understood is this is why when I append the SECOND (DATEPART("ss",GETDATE())) part it was giving the error(as the SECOND is keep on changing ).

  I'm not sure whether you people tried use the expression I have mentioned in the beginning of the post,(if not could you please try using that, it has full time stamp).

  Also you can see that we can use the same for flat file destination with out any problem.

  Thanks & Regards,
  MC