Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem while importing Data from Excel to SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, June 16, 2010 3:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 3:40 AM
Points: 39, Visits: 93
Hi,

I am having problem, due to the presence of mixed data within a column, which is why I get null values for minority datatypes from my query report of sql. I know where the root of the problem is, and after going through many postings in the forum I realized how it can be solved. Set IMEX = 1 at the Excel Connection Manager Setting. But my question is, how can I actually do this? I can't find Excel Connection Manager. And changing the setting, is it done through writing a code in sql query editor before the import or do we have to do it in excel VB or something? Im kind of confused as I am new in this area, so would really appreciate if anyone could help me.

Thanks in advance!
Post #938023
Posted Wednesday, June 16, 2010 4:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 13,635, Visits: 11,506
If you want to read an Excel file, you probaby have already an Excel File Connection Manager set-up.
Do the following:
Click on the connection manager. Then, go to the properties window (usually at the right in BIDS).
There you will find the propery ConnectionString. Edit this connection string manually by placing IMEX=1 at the end. The connection string should end like this:

;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

Another thing to do is changing a registry setting (on all the computers that will use the package). This registry setting determines how many rows Excel will scan to determine the datatype of a column. Standard this is 8, meaning that if your first 8 rows are numerical and all the other rows are strings, IMEX will do nothing. See here:

http://support.microsoft.com/kb/189897




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #938043
Posted Wednesday, June 16, 2010 7:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 3:40 AM
Points: 39, Visits: 93
Hi,

Could you tell me where the connection manager is located, apparently I cannot find its location. I was in fact looking to get this answer..

Thanx :)
Post #938614
Posted Wednesday, June 16, 2010 8:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 3:40 AM
Points: 39, Visits: 93
Hi,

Apparently, I cannot modify the Connection String, atleast when the connection manager has a server in it. The database I am connected to is through a server. (By the way, this connection manager I found was in Microsoft Visual Studio 2005 and then new projects as described here-> http://www.mssqltips.com/tip.asp?tip=1405 ). Now I found the connection string, and based on the above site, I got stuck in step 10, where I cannot modify the connection string.

I am not even sure whether I am headed the right way.

Your inputs will be much appreciated. Thanx :)
Post #938618
Posted Thursday, June 17, 2010 6:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 13,635, Visits: 11,506
You should edit the connection string of the Excel connection manager. Have you got an Excel connection manager in your SSIS-package?
The place to look is in the properties window of the connection manager, not in the package configurations.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #938852
Posted Thursday, June 17, 2010 8:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 3:40 AM
Points: 39, Visits: 93
Hi,

Correct me if I am wrong, the connection manager is the one part of Microsoft Visual Studio 2005 (or the SQL Server Business Intelligence Development Studio).. In that case, where to find the connection manager. I only encountered the connection manager when I created a new project. And thats when I could not change the connection string.

If the above is wrong, could you point me where I can find or bring about the connection manager window, because I am kind of lost here.

Thanks in advance :)
Post #939326
Posted Thursday, June 17, 2010 11:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 13,635, Visits: 11,506
In your first post, you said that you were reading from an Excel file from a SSIS package. How did you achieve this?

I have attached a screenshot of a simple package that has an Excel source. I have indicated the connection manager and the property that you should modify. Hope this helps...

EDIT: another hint: you should create the Excel connection manager yourself. Either in the Excel Source component by selecting New, or by right-clicking in the connection managers window, choosing New Connection and then picking Excel from the list.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence


  Post Attachments 
excel_source.JPG (19 views, 85.01 KB)
Post #939368
Posted Sunday, June 20, 2010 11:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 3:40 AM
Points: 39, Visits: 93
Hi,

I have changed the connection string, as u showed in the picture. It is still not working, I guess I am missing something huge here. Look, I am simply doing this in order to import from excel to Sql.

1) Go SQL Server 2005.
2) Go to databases. Right Click. Then under tasks, import data.
3) Then SQL Server Import and Export Wizard window pops out (see attached). From there, I chose source as Excel and destination as SQL Native Client. Then go on next and select the Excel Sheet to be transferred to SQL.
4) Excel data transferred in the form of table in SQL.

Now, problem arises due to mixed data type, for a particular column. I cannot change it manually since I am dealing with 100000 + data entries. Even after selecting everything as text in Excel, this problem persists and any column entry with 1 or 1.3 gets converted to NULL whereas 1.2.3 or anything else remains correct. So I thought I hhave to change the connection string as you described earlier. I have done that, simply opened up the MS Visual Studio 2005 and new project->Integration services Project-> Data flow->right click connection manager and excel connection->Excel File Path (location of the excel file which will be imported to SQL)->Select Excel Connection Manager->Change the connection string properties as stated. Then Data Flow Destinations->SQL Server database and I made the connection between the Excel Source and SQL Server Destination. Tried debuggin but get error. I have only done till this. Then I went back to the SQL import and export wizard and did the transfer as described above in step 3, choosing Excel File I want to transfer as the source.

Outcome: Still the same.

I just described to you what I did. I cannot relate MS Visual Studio and the Excel connection manager to the SQL import and export wizard, totally lost there. Please direct me on this.

My main mode of transferring is through SQL Import and Export Wizard. By changing the connection string, am I supposed to only do the transfer using MS Visual Studio 2005?

Thanx in advance. :)


  Post Attachments 
SQL Server Import and Export Wizard.bmp (9 views, 3.75 MB)
Post #940097
Posted Monday, June 21, 2010 12:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 13,635, Visits: 11,506
You don't have to open a new integration services project.
The Import wizard makes a SSIS package for you. Find this package (the wizard asks you if you want to save the package, so click yes and save it to a known location), and then edit the connection string.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #940107
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse