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.