June 16, 2010 at 3:59 am
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!
June 16, 2010 at 4:48 am
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 16, 2010 at 7:33 pm
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 🙂
June 16, 2010 at 8:10 pm
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 🙂
June 17, 2010 at 6:44 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2010 at 8:34 pm
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 🙂
June 17, 2010 at 11:42 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 20, 2010 at 11:38 pm
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. 🙂
June 21, 2010 at 12:20 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply