July 14, 2010 at 6:45 am
I have done something similar... but in my case excel source was dynamic..
I have set the connectionstring expresssion value of Excel connection manager as below
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::filepath]+";Extended Properties=\"Excel 8.0;HDR=YES\";"
I have a set a initial value for the @[User::filepath] variable to make the evaluate expression work..
__________________________________________________________
Yuvaraj
July 15, 2010 at 5:58 am
Hi Mhlewis,
Yes I would like to keep different excel file for each execution thats why I tried to append the full time.
As you told I can try taking the full time into a variable, is there any other option to achieve the same.?
Also as I have mentioned before any idea like how in flat file destination we are able to do this..?
Thanks & Regards,
MC
July 15, 2010 at 6:12 am
only4mithunc (7/15/2010)
Also as I have mentioned before any idea like how in flat file destination we are able to do this..?
I'm already surprised that it works for flat files.
Say you create a flat file with the name myFlatFile_20100715_141158 at the timestamp of 2010/07/15 at the hour 2:11:58 PM.
Your package starts running and when you create the connectionstring using the getdate() function, you get myFlatFile_20100715_141205, because there are already 7 seconds elapsed.
So it baffles me that it works. It shouldn't work, and you shouldn't use something like that because it is very prone to errors.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2010 at 8:27 am
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.
July 16, 2010 at 3:12 am
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply