Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS and Excel 2007 Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 11:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:38 PM
Points: 912, Visits: 209
Comments posted to this topic are about the item SSIS and Excel 2007






My Blog: http://dineshasanka.spaces.live.com/
Post #590977
Posted Friday, October 24, 2008 3:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:46 AM
Points: 50, Visits: 128
We have IA-64 servers........other than using a CSV converted version......will Microsoft ever release excel (Jet) drivers so we can use the excel Tasks?

Do you have any methods for bringing in Excel files as is on an IA64 platform?
Post #591043
Posted Friday, October 24, 2008 3:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Nice one...


Post #591060
Posted Friday, October 24, 2008 5:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
If you want to do this in sql, refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926



Madhivanan

Failing to plan is Planning to fail
Post #591106
Posted Friday, October 24, 2008 6:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:46 AM
Points: 50, Visits: 128
Madhivanan (10/24/2008)
If you want to do this in sql, refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


Unfortunately "Microsoft.Jet.OLEDB.4.0" does not exist for IA-64..........unless you have heard otherwise.....

If you have we would be very pleased indeed!! if so please provide MS link to the IA-64 OLEDB jet driver.
Post #591157
Posted Friday, October 24, 2008 8:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:02 AM
Points: 916, Visits: 992
From what I've seen, importing data from excel can be very hazardous so I avoid it whenever possible. The import process will interpret your data type by sampling the data irregardless of how you define the data type in SSIS or on your destination table. So if the first part of your file contains numbers and the last contains characters... the import process will simply throw out the data with characters and load NULL's without any warnings or errors. I know there is a reg key you can set to determine the number of records that are sampled, but the fact that the Excel driver does this is bad enough for me.

David



Post #591245
Posted Friday, October 24, 2008 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 204, Visits: 1,328
"From what I've seen, importing data from excel can be very hazardous etc"
I agree on that.

But I have no experience with Excel2007 yet. Does it work as 2003 in this sence ?
Dinesh what do you say?

For me I will still save the sheets as TAB sep textfiles and import them so.

//Gosta
Post #591260
Posted Friday, October 24, 2008 8:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 204, Visits: 1,328
"Unfortunately "Microsoft.Jet.OLEDB.4.0" does not exist for IA-64..........unless you have heard otherwise"

If you keep to textfiles you don't need the Jet driver if you use bulk insert instead.//

//Gosta
Post #591266
Posted Friday, October 24, 2008 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2008 6:13 PM
Points: 2, Visits: 8
Awesome. Thank you. I could have used this last week... but I got through it.

I had to do a comparison of an excel 2007 file to an sql table and follow that with a report on the findings in SSRS.

I found that I had to export to an earlier version of excel for the reporting services piece. I couldn't find how to use a driver for excel 97 in reporting services. Did I miss something there?

Thank you
Post #591356
Posted Friday, October 24, 2008 10:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 3, 2013 8:20 PM
Points: 223, Visits: 83
David (10/24/2008)
From what I've seen, importing data from excel can be very hazardous so I avoid it whenever possible. The import process will interpret your data type by sampling the data irregardless of how you define the data type in SSIS or on your destination table. So if the first part of your file contains numbers and the last contains characters...


I have found this to be true as well. I haven't started experimenting with SSIS in 2008 yet, but hopefully there's a setting that addresses this. From what I remember, SSIS only looks at the first 200 rows to try to analyze the data type. It would obviously be better if it tried to find every possible data type in a column and then chose the lowest common denominator.

However, I'd make the argument that one should be familiar with the data that's being imported, and should therefore be able to anticipate any data types for each column, and handle any deviation with row redirects to text files so that the package execution doesn't come to a dead stop because of a few bad apples in a source.

Cheers,

-m



Post #591360
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse