March 5, 2009 at 1:41 pm
I am trying to migrate from SQL 2000 to SQL 2005 but having problems with SSIS. I cannot import from an ODBC data source. I have purchased a driver called FLEXODBC, which is used to extract data from DataFlex databases. When using the DTS Import/Export Wizard in SQL 2000 it appears in the list of data sources as DataFlex Driver, and everything works just fine. However in SQL 2005 Import and Export Wizard the data source does not appear. The only choice for ODBC is the .Net Framework Data Provider for Odbc, but I cannot get that to work.
I have spent many hours trying to resolve this, and searched the internet for forums or blogs on the topic, such as the following:
It seems totally unbelievable that after all this time since SQL 2005 was introduced that Microsoft do not appear to have fixed this. I am ready now to give up. Can I have both DTS and SSIS for SQL 2005?
March 5, 2009 at 2:00 pm
You can have DTS in SQL 2005. Not in a wizard though. You can build a DTS package and deploy it on an SQL 2005 server. Will that accomplish what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2009 at 2:32 pm
Thanks. I'm not sure. I will need to look into that suggestion. I'm guessing that I won't be able to keep both our SQL 2000 server and the SQL 2005 server with only 1 SQL Server license. So the question is can I somehow keep DTS, at least with with designer so I can create and edit packages, on the SQL 2005 server, or will I still have licensing issues?
March 5, 2009 at 3:33 pm
Check out the SQL 2000 DTS Designer for SQL Server 2005. It can be installed as part of the "Feature Pack" found in the link below:
This might give you the functionality you need. You can use it to maintain existing DTS packages in a 2005 environment. Disclaimer: I haven't used it....
March 5, 2009 at 4:12 pm
Thank you very much! I haven't installed it yet but that looks as though it will do the trick.
March 6, 2009 at 7:52 am
You should be able to get a Dev copy of SQL 2000 that you could use to build DTS packages for deployment on your production server. That's going to be quite cheap, and it's definitely legal.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 8, 2009 at 5:45 am
Thanks GSquared. I didn't think of that. I do already have a Dev copy somewhere, so it won't cost us a cent (assuming I can find it).
March 9, 2009 at 10:22 am
You can use DTS in both SQL Server 2005 and 2008. You need to install the 'DTS Designer' component from the SQL Server Feature Pack.
On SQL Server 2005 many people find they need to install the fix given in KB 917406 to get DTS Designer working with all their packages.
On SQL Server 2008 this fix is not necessary or available. However, if you install 32-bit SQL Server 2008 you will also need to install the 'Backward Compatibility' package from the Feature Pack. On 64-bit SQL Server 2008 the backward compatibility stuff is installed by default. You also need to copy some files to get DTS Designer working correctly - the details for this are given in the Readme for the DTS Designer download for SQL 2008.
Finally, if you want to use DTS on a 64-bit server this is possible. However, anything that you used to put in the Windows \system32 folder on a 32-bit server (e.g. DTS OCX modules) must be put in the \sysWOW64 folder on a 64-bit machine.
I also recommend you install the freeware DTS Backup 2000 tool to move your packages around. If you use standard Microsoft tools you will loose the layout and any annotations you put into the package with DTS Designer.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 7, 2009 at 5:53 pm
I did not end up trying to run DTS packages in SQL 2005. My search for answers to my original problem of ODBC sources not being visible was not successful, which led me down the track of trying to keep the DTS packages. However a consultant pointed me in the right direction. So, for the benefit of other users who have similar problems with ODBC connections, this is how to do it:
1. In setting up connection manager select New ADO.NET Connection
2.In the Provider drop down list select Odbc Data Provider then set up ODBC connection
3. In Data Flow Task select a DataReader Source
4. On the Connection Managers tab of the DataReader source choose the ODBC connection from steps 1 & 2
5. On the Component Properties tab of the DataReader source input the SQL statement to extract data from the ODBC database
Thanks to Montage (Christchurch, New Zealand) for giving me this solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply