and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
KristinaHi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.ex
e
> to set up the DSN. I am not sure if that would allow you to configure a DS
N
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
[url]http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html[/ur
l]
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine)
,
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you cou
ld
> also install a second instance of SQL-Server (either 2000 or 2005) but thi
s
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 3
2
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you mu
st
> have an installation of SQL-Server running under 32 bit to do that or if y
ou
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 200
5
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2...-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
>
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an OD
BC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....
No comments:
Post a Comment