SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

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






My Blog: http://dineshasanka.spaces.live.com/
Jonathan Butler
Jonathan Butler
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

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



Madhivanan-208264
Madhivanan-208264
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3781 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
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2158 Visits: 1105
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3278 Visits: 2172
"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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3278 Visits: 2172
"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
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 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-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

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