SSIS and Excel 2007

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the item SSIS and Excel 2007




    My Blog: http://dineshasanka.spaces.live.com/

  • Jonathan Butler

    Right there with Babe

    Points: 718

    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

    SSC-Insane

    Points: 24681

    Nice one...

  • Madhivanan-208264

    SSCertifiable

    Points: 7516

    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

    Right there with Babe

    Points: 718

    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

    Hall of Fame

    Points: 3552

    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

    SSCertifiable

    Points: 5973

    "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

    SSCertifiable

    Points: 5973

    "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

    SSC Enthusiast

    Points: 118

    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

    Right there with Babe

    Points: 775

    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

  • btainer

    SSC Enthusiast

    Points: 118

    I haven't experienced that particular anomoly. I'll have to keep an eye out for that. I do try to avoid using Excel files... general nuisance and best avoided. But when the files are maintained by people in excel v.???? the best thing to do is go with it if you can't convince them to change formats. It's easier to deal with it from that perspective then it is to count on the user to save the file in a specified format for the process or to add a manual step to your own process.

    Sometimes we just don't have a choice.

  • DavidSimpson

    Hall of Fame

    Points: 3552

    I really hope the driver gets updated to fix this issue, at least for Excel 2007 files. Really annoying and potential dangerous when you define an input column as varchar yet the import process throws out all the data where there are characters in the column just because the first few rows are numeric.

    Because of this I do like Gosta stated and export the data to a delimited file and bulk insert from that. If forced to import from Excel files, make sure you have plenty of quality control checks running on the imported data and explain to management the risks involved.

    David

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    The article was well done, but it was much lighter on content than most articles here. A little more depth to this otherwise good work would have been nice.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • bilx10

    SSC Enthusiast

    Points: 187

    I know my data very well and also that ADO in excel doesn't handle columns with mixed data. A cell with numeric data will be taken as numeric and any data with alphanumeric characters ignored even if the destination is varchar datatype. Columns of data where for example there is a column of order numbers from different customers with different order number formats are a problem and even values can be - individual cells in a column of money values can be formated as text especially if they are pasted in from a word document. ADO just ignores the value. Any of these values could be easily fixed by a convert transformation or script transformation if provided to the package but they are left out by the excel export process so they don't reach the package for the errors to be handled. Of course if the excel files where properly created with each column formatted to the right datatype and data validated and verified there would be no problem with ADO (its an ADO problem not BIDS) but if you have been able to get the business units providing the excel files to do that please let us in on the secret of how thats done - most of the business users creating these sources don't have a clue what a datatype is and have no interest in learning. That includes their Management so there is no support for improvement.

    I have found that the only way to guarentee getting all data (including bad data) from excel sheets into my package is to convert them to delimited files, either tab delimited or csv then load those. All data will be brought in which allows me to set up error handling if needed. I use an activex script do this by creating an excel automation object to open the excel workbook and save the individual sheets as txt or csv that can then be handled by SSIS loops and transformations. This was a quick fix copied from my original DTS version that I will replace with a script task when I have the time to work out how to do it with a script task in BIDS - you can't directly add the references needed in BIDS. (Unless someone out there has a way!)

  • Gosta Munktell

    SSCertifiable

    Points: 5973

    This is an interesting discussion. We are not alone with need to import data

    from Excel.

    At first look at the front Excel. Is the user permitted to key in any garbage to

    be imported to the database? To be short I still keep to the tab textfiles but I also

    do it in un other way.

    I design budget/prognoses system(s) with Excel as front and SQL-server as back.

    All coding (shuffling data to and from Excel is VBA remote OLE DB ADO in Excel.

    Very short the user fetch an Excel template from an intrant server and populate

    one or more sheets with data by choise ie cost center. The user then see actual

    figures and existing budget/prognoses. All cells showing values are locked!

    Certain cells on certain sheets are mark with a different colour and open for just numbers

    to be inserted in a certain range. The workbook/sheets recalculate then the user key in

    values (so it is a bit of simulation). Also comments can be entered or references to other

    documents /stored in the database. When the user is satisfied he/she press a button

    and the values (only in the open cells) are inserted in the database by a traditional insert

    statement. (Can be update also).

    Best luck with your Excel/database adventure.

    //Gosta

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply