I'm just beginning to use SSIS (bracing for a steep learning curve due to lack of helpful documentation) and am starting out trying use the Import and Export Wizard. On the "Choose a Data Source" page there is a dropdown for the Data Source. I see a list of possible data providers, but not one of "Microsoft OLE DB Provider for ODBC drivers," which is the one I wanted to use because I'm trying to connect to an obscure database. So I figured that I need to use ".Net Framework Data Provider for Odbc." Unfortunately, regardless of what I enter for the Connection string or the Dsn or the Driver I invariably get an error, although it's somewhat dependent on that I have entered for those three items.
Either this (when I type in a DSN)
Cannot get the supported data types from the database connection "Dsn=Terrascan_Okanogan_WA".
or this (if I enter a full connection string and a driver)
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
of this (if I enter a DSN and a driver)
Cannot get the supported data types from the database connection "Dsn=Terrascan_Okanogan_WA;Driver={SmartWare Driver}".
------------------------------
ADDITIONAL INFORMATION:
Specified cast is not valid. (System.Data)
So I have a couple questions. First, why doesn't "Microsoft OLE DB Provider for ODBC drivers" appear in the list of data sources, and secondly, when using the ".Net Framework Data Provider for Odbc" data source what inputs are expected because whatever I'm doing doesn't seem to work
SSIS Import and Export Wizard Data Sources
sassenach
I am flabergasted that Microsoft would throw away such a simple, and usefull means of connection.
I have spent hours trying to figure out how to setup a recurring import of a group of about 105 DB2 tables into SQL Server 2005 using the Import/Export wizard. About 2 hours into trying to figure out how to do it, I used DTS for SQL Server 2000, and did it in about 2 minutes. I have now gone back to SQL Server 2005, and after spending another couple of hours, am about to give up. I finally got past the "Choose a Data Source" window through trial and error (wheres the documentation) when it accepted the string: "Dsn=SQLATS;Driver={IBM DB2 ODBC DRIVER};uid=XXXX;pwd=XXXX". I get to the "Specify Table copy or Query, and the copy data from one or more tables is greyed out! Do I have to write a query for each table. Completely unacceptable. I don't want to have to go to a 4 day class to try to duplicate things I can do in minnutes using SQL Server 2000. I am about ready to cancel my company's order for SQL Server 2005. (you may be able to tell that I am completely frustrated) I have been a SQL Server administrator since v7, continually singing the praises of MS and SQL Server to everyone. I use mostly the gui tools, and I want to continue it that way.
Please let me know that this will be improved soon as it is terrible right now!
vhalexxs
That is the schema you get when you query your connection like this:
DbConnection.GetSchema("DataTypes");
You can get an idea of the content you have to provide in the resulting DataTable if you get this schema from a SqlClient connection and analyze its data.
I am sorry I misnamed the schema in the previous message (ProviderTypes), it is actually called "DataTypes".
Thanks.
Chunguang Jia
The import/export tool of SQL server 2005 is quite unuseful without a usable ODBC connection.Writing a query for each table is totaly unacceptable. We will keep our SQL server 2000 and we will probably search different DB servers for our customers.
Jeremy M. Iiskoff
Bob,
Its been awhile since the last post. I am also getting the "Specified cast is not valid" error just trying to connect to and AS400 using the Wizard in SQL 2005 Enterprise. How or why is it that Microsoft would discontinue support of ODBC on their flagship product when I can seamlessly bring in data in MS Access though ODBC Do you know if there has been any improvement to the tool or if there are any planned
I am not a developer, and don't want to become one. I am but a lowly project manager working with scarce resources trying desperately to solve this problem. This is the only place I could find others dealing with the same issue as I am. I just need to get data out of an AS400, and don't know of any other way to do it. As far as I know, the only way to make a data connection to an AS400 is though IBM's Client Access ODBC driver. Any suggestions That don't involve programming
Thanks!
Eric
CDS DBA
I am sorry it made you feel this way. It was definitely our mistake not to recognize how many people still depend on ODBC connectivity. I wish you would not drop the entire SQL Server box because of this. If you give it a chance, I am sure you will find enough value to prove the decision worthwhile.
Please channel your frustration through the product feedback site:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
It would make an official request for improvements in this area ASAP.
Thanks.
dxguy
I am struggling with a similar issue trying to connect using an ODBC Driver where we cannot get past the message:
We wrote the driver and should be able to provide the necessary information.
We've typically provided this information through the SQLGetTypeInfo() function.
Can you provide more information on the ProviderTypes schema
Thanks,
Dave
Dick Campbell
>> First, why doesn't "Microsoft OLE DB Provider for ODBC drivers" appear in the list of data sources <<< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
The support for this provider has been discontinued and we couldn't expose it in SSIS.
>> when using the ".Net Framework Data Provider for Odbc" data source what inputs are expected because whatever I'm doing doesn't seem to work <<
From the error message it seems the ODBC provider you use does not expose the ProviderTypes schema. The wizard relies on the information from this schema to define mappings between source and destination data types.
You may want to try accessing your data source manually by using Data Reader Source component in the SSIS Data Flow designer.
HTH.