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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy