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

Sep 02

SQL Server Analysis Services (SSAS) Connection Properties

Analysis Services 3 Comments »

Recently I was having some issues getting a new SQL Server Analysis Services (SSAS) cube to process on a new development (dev) server. The error I kept receiving was:

[Analysis Services Execute DDL Task] Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001; SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ; 08001.

After much toying around I was able to determine what was causing this. In my case I had my shared data source connection for my data warehouse using Windows Authentication and the Impersonation Info set to Default. The problem appears to stem from the translation from Windows Authentication to the Impersonation Info option.

To fix this I changed my shared data source connection to use a SQL Server account and left the Impersonation Info option as default. This then must have allowed SSAS to translate the username and password info when doing the impersonation. I could be wrong but it is now working.

If you have any comments on this or a deeper explination please share? If you too are experiencing this issue o I hope this helps!