Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and Excel 2007


SSIS and Excel 2007

Author
Message
Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
Comments posted to this topic are about the item SSIS and Excel 2007






My Blog: http://dineshasanka.spaces.live.com/
Jonathan Butler
Jonathan Butler
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
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?
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Nice one...



Madhivanan-208264
Madhivanan-208264
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 476
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
Jonathan Butler
Jonathan Butler
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
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.
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)

Group: General Forum Members
Points: 976 Visits: 1074
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. Crazy

David



Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 1406
"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
Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 1406
"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
btainer
btainer
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
mcloney
mcloney
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search