July 11, 2010 at 5:07 am
Hi friends,
I have an OLE DB source and one excel destination, I have tried to make the Excel connection string dynamic.
In the Excel connection manages Expression I have selected the Property as Connection string (I have tried with Excel Path as well) and the expression is given as :
@[User::Path]+"Testfile_"+ (DT_STR,4, 1252) DATEPART("YYYY", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "MM", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART("DD", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "hh", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "mi", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "ss", GETDATE() )+
".xls"
so that the file will be saved with full name including the second of the day. The evaluate expression was showing it correctly , but when I tried to execute the package I'm getting the error as:
"The connection string format is not valid. It must consists of one or more components of the form X = Y, separated by semicolons. This error occurs when a connection string is set on database connection manager."
I have attached the screenshot of the error. Anyone came across this type of error? Can I know how to solve this.?
Thanks & Regards,
MC
July 12, 2010 at 5:26 am
You are trying to set the wrong property.
With the expression that you created you should set the ExcelFilePath property.
The connection string has the following form:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\myServer\myExcelfile.xls";Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";
which is a combination of different properties.
Let me know in this thread how it works out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 12, 2010 at 5:54 am
This was removed by the editor as SPAM
July 12, 2010 at 5:57 am
stewartc-708166 (7/12/2010)
Instead of inserting this at the ConnectionString property, try setting the ExcelFilePath property
re·dun·dan·cy (r-dndn-s)
n. pl. re·dun·dan·cies
1. The state of being redundant.
2. Something redundant or excessive; a superfluity.
3. Repetition of linguistic information inherent in the structure of a language, as singularity in the sentence It works.
4. Excessive wordiness or repetition in expression.
...
😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 12, 2010 at 7:06 am
Hi Da - Zero,
Thanks for the reply.
As you specified I have tried the new expression in the Excel Path expression (New connection string.jpg attached) but Im getting error as attached (Error With new connection string.jpg)
I tried by using == as it is mentioned in the error but still it is not resolved.
Also Im wondering how the connection string property was working with SSIS 2005...????
Thanks & Regards,
MC
July 12, 2010 at 9:56 pm
Sorry friends...
2005 SSIS also giving the same error. But both 2005 n 2008 are working for flat file destination but not for excel....
Any idea how to make it work ? Im not sure whether any of you tried this.. are you people able to make the dynamic connection string as I have mentioned above for excel destination...?
Thanks in advance.
Thanks & Regards,
MC
July 13, 2010 at 12:11 am
I have succeeded many times. Use the ExcelFilePath property as mentioned earlier, with the expression you build in the beginning.
When you start designing, configure your excel connection manager by pointing to a test excel file. Edit the connection string manually to add IMEX=1 if necessary. Then, use the ExcelFilePath property to set the location at run time.
Try it out and let us know. If it still won't work, I will send you a template package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2010 at 12:46 am
This was removed by the editor as SPAM
July 13, 2010 at 3:06 am
stewartc-708166 (7/13/2010)
the excel connection manager requires that the destination excel files exists beforehand;refer:
Indeed. That's why you need to point it to a test file (with the same structure) during design time, so you can set up the excel source. At runtime you can use the expressions, as long as the file exists at the start of the package (I think).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2010 at 5:28 am
This was removed by the editor as SPAM
July 13, 2010 at 6:52 am
Thanks for the replays...
I have tried the same thing as mentioned initially it was pointed to one test file and then in the expression of the excel connection manager I have given the expression for the Excel path as :
@[User::Path]+"Testfile_"+ (DT_STR,4, 1252) DATEPART("YYYY", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "MM", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART("DD", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "hh", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "mi", GETDATE() )+
"_"+ (DT_STR,4, 1252) DATEPART( "ss", GETDATE() )+
".xls"
Now the Excel destination is showing error mark (x) then I have double clicked it and checked the table or view option just below the Data Access Mode option, the table or view drop down was empty, then I clicked the New button and did the mapping again.
but it was throwing error again. Then I have removed the last line of the connection string (the part corresponding for seconds) and tried the same steps then it worked... 🙂
But....
Again when I tried to execute I have to do the re mapping again... that means each time when I tried to execute it was giving the error then I have to double click the Excel destination and do the mapping by clicking the New button for the table or View option...
Are you people able to execute the package when you make the connection string appended with time , with out remapping each time ?
Thanks & Regards,
MC
July 13, 2010 at 7:14 am
dear friends one more strange thing what I have noticed is,
As I have given the connection string including the year,month,day,hr,minute if I execute the package within a minute say 35th minute it will execute how many times Im executing, but the moment when the time changes to 36th minute it is throwing the error and then I have to double click the Excel destination and do the remapping same way again I can execute any number of times within this minute (36th) , and when it crosses to 37th it throws error again.
So think this why I got error when I have appended the seconds part of the time (DATEPART("ss",GETDATE())) in the connection string... as it keeps on changing.
Not sure whether you people able to execute it successfully (with the same connection string I have mentioned)
Please let me know if the explanation I have given confuses you.
Advanced thanks to all who is trying to help me to solve this issue.
Thanks & Regards,
MC
July 13, 2010 at 7:48 am
I don't think it is a good idea to build your connection string with getdate().
Since getdate() gives you the current timestamp, it will always change when you run the package at a later time. So you just can't pick up the Excel file because the time portion will always be different (unless you are very fast :-)).
What does the Excel filename look like?
Is it something like myFile_20101307.xls?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2010 at 10:03 am
I agree with your point about Getdate() , the excel file name looks like
Name_2010_7_13_17_55 (Name_Year_Month_Day_Hr_Minute)
But I'm wondering then how the same is working fine with .csv (ie if I use flat file destination)....
In case of Flat File Destination I'm able to give the full connection string(including the year_month_day_hr_minute_second , in the expression by selecting the Connection String Property). Here also I'm using the Getdate() function only.
My intention was to keep separate copy of the excel file for each execution thats why I tried append the year_month_day_hr_minute_second parameter....
Is there any other way to keep separate copy of the excel file in the same destination folder for each execution..?
Thanks & Regards,
MC
July 13, 2010 at 2:32 pm
What it sounds like is that the current time has changed between when you named the file and when the connection manager is trying to connect. Is it necessary to have the seconds and minute on the file? Are you renaming the first before processing? How about renaming and moving it to an archive folder AFTER processing so you don't have to use a dynamic connection string? Or when you set your filename to the current time save that value in a variable and use that variable as the filename in the connection?
Anyhow, I have several packages that use Excel 2003 as a source and the name changes with each load. We set the ConnectionString and ExelFilePath in the Expressions for the connection. We use a variable called [User::SourceFile] for the file ExcelFilePath, which is pulled in from a For Loop. Then the ConnectionString is built from that:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourceFile] + ";Extended Properties=\"" + "Excel 8.0;HDR=Yes;IMEX=1;"+ "\""
We set DelayValidation to TRUE and RetainSameConnection FALSE for the connection manager. For the Excel Source ValidateExternalMetaData is set to FALSE.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply