Sep 10

Using Excel 2007 files as a Source in SSIS 2005

Excel, Integration Services, SQL Server 2005 No Comments »

Thanks to “jaegd” for his post here: http://forums.microsoft.com/… on how to accomplish this. Below please find some more details along w/ a example which can be downloaded here (ssis-2005-excel-2007-source-example).

Steps

  1. Create new OLEDB Connection by right clicking on the Connection Manager tray
    2. New OLEDB Connection in SSIS

    New OLEDB Connection in SSIS

  2. Chose any valid type or value for the connection and click okay
    3. OLEDB Connection Manager

    OLEDB Connection Manager

  3. Rename the Connection Manager to something that makes sense, like “Excel 2007″
  4. Now that the the connection exists right click on it and select properties
  5. Edit the Connection String property IN THE PROPERTIES WINDOW to match below, updating with your Excel File Location
    • Data Source=C:\MyExcelFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES”;

    6.  

    manually-edit-connection-string

    manually-edit-connection-string

You can now use this connection by referencing it with any task/object that would normally connect to an OLEDB source. To query it use [Sheet1$] for the table name (eg. SELECT * FROM [Sheet1$])

Do you have anything to add? Please add your comments below!

Cheers,
Ben

Aug 14

Excel Tip - Identifying the max length of values in a column

Excel No Comments »

Sometimes files are received such as client feeds that contain values in columns that exceed the maximum number of characters allowed in the destination database. When these records are imported an error occurs when trying to fit this value into the column with the character limitation. In these cases it is helpful to have a quick way to identify which fields and specific records are causing the issue. Ideally the ETL process that loads the data would provide this information to us but, in the spirit of catching errors before they occur, here is a quick way to accomplish this in Excel.

Assumptions

  1. The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
  2. The number of rows does not exceed the maximum allowed by the version of MS Excel being used.
    • Excel 2003 = 65,536 rows
    • Excel 2007 = 1,048,576 rows

Steps

  1. Open the file in excel (parse it if it’s text)
  2. Insert a new row above the header row
  3. Enter the following formula in every column of the new row that is being imported
    • =MAX(LEN(A2:A65636))
    • The range inside the forumla should cover all of the rows in the column (eg. A2:A100) for records 2 - 100
    • In order for this formula to work properly you must press ctrl+shft+enter after typing in the formula.
    • The best way to get this into all the additional columns would be to simply copy and paste.

At this point you will be able to see the max length of the values in the columns with the formula entered. To make this more readable and even easier to identify try completing these additional steps below.

Additional Steps

  1. Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
  2. In a new sheet under the Home > Paste > Paste Special
  3. From the paste special dialogue box check ‘Values’ and ‘Transpose’ then click ok

You will now see the fields and their max lengths stored vertically instead of horizontally as they were on the original sheet which I’ve found to be the easiest to read.

Hope this helps!