November 9, 2011 at 10:08 am
Hi All,
I have created an ssis package that dynamically creates an excel file each time it runs and is then populated by a data flow task. I have couple of columns that have datetime datatype. I created the excel file by giving the appropriate datatype to the columns but after I run the package and the data is exported to the excel file the datatype for the date columns show as "general". I try to manually change the datatype to "Date" but does not unless and until I double click each row they wont change to "Date" datatype. I checked my ssis package and see that the "Destination Excel" file properties, the datatypes of the date columns are changed to dt_wstr. Here too I tried to change them manually but no use.
Please help me resolve this issue !!!
Regards,
Rachel
November 9, 2011 at 11:58 pm
A trick that usually works is having a dummy row right below the headers. This dummy row will contain date values, so the JET provider will normally insert the data as dates.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 10, 2011 at 9:53 am
well I do have date values in the first row. The thing is that this used to work in sql server 2005 and the excel was 2003. Now that I have recreated the same package in 2008 r2 and excel being in 2010 I dont know why the package doesnt work the same way ?
November 10, 2011 at 10:50 am
rachel_13 (11/10/2011)
well I do have date values in the first row. The thing is that this used to work in sql server 2005 and the excel was 2003. Now that I have recreated the same package in 2008 r2 and excel being in 2010 I dont know why the package doesnt work the same way ?
Well, for starters Excel 2010 uses the ACE OLE DB provider instead of the JET provider. Maybe this provider behaves a little differently. (although I thought that worse then the JET provider was impossible)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 4:44 pm
Any suggestions people ?!!!!!!
This is ridiculous Microsoft comes up with something new and forgets all the good features of the old version. Why microsoft ? Arent you guys testing it properly ?
People...is there a way for me to program it or anything like that ?!!!!!
Regards,
Rachel
November 16, 2011 at 12:01 am
The data inside the dataflow, does it have a date datatype?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply