May 27, 2010 at 10:41 am
Hi guys,
We are in the process of upgrading to 2005 from 2000. We have a lot of legacy DTS packages that we have successfully converted and can now run in SSIS...with 1 exception.
We have a legacy DTS package that connects to Sybase and pulls data into our system. We have installed the sybase client on the sql 2005 server.
My problem begins with sybase not being compatible with 64 bit. The sybase connection is 32 bit.
I have migrated the old sybase DTS package to an SSIS package and when I open the package in BIDS, I was able to get it to test successfully to the sybase connection. Of course when I run it in BIDS it says that this task does not support 64 bit and to run it with WOW 32 bit.
So, I created a job based off an article I found online that explains how to execute a package with the 32 bit DTExec.exe. I did that and it appears to start executing the package but after about 35-40 seconds I get this error message:
Microsoft (R) SQL Server Execute Package Utility<nl/>Version 9.00.4035.00 for 32-bit<nl/>Copyright (C) Microsoft Corp 1984-2005. All rights reserved.<nl/><nl/>Started: 12:26:20 PM<nl/>Error: 2010-05-27 12:26:51.57<nl/> Code: 0x00000000<nl/> Source: Transform Data Task HEREMS Assistance Contract <nl/> Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.<nl/> at DTS.PackageClass.Execute()<nl/> at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()<nl/>End Error<nl/>DTExec: The package execution returned DTSER_FAILURE (1).<nl/>Started: 12:26:20 PM<nl/>Finished: 12:26:51 PM<nl/>Elapsed: 31.422 seconds,00:00:31,0,0,,,,0
The command line I'm using to execute the package in the job is this:
"D:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" /SQL "\Load HEREMS Data test" /SERVER MyServerName /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Any advice or guidance would be awesome! 🙂
May 28, 2010 at 7:14 am
Any insight at all would be super helpful. 🙂
June 2, 2010 at 6:36 am
Hmm ok, nobody has an experience with trying to connect to sybase from sql server? 🙂
I kinda solved the problem... but not ideally...
I found an article online about how to add a linked server in sql server to sybase. Basically, it involved downloading a new driver which came from DataDirect ODBC Driver for Sybase. I guess this driver is unique in that it doesn't care if the sybase database is 32 bit or 64 bit.
So, I installed the 64 bit driver from DataDirect and wah la, the linked server connection now works.
My problem is that this driver is not free and you gotta pay for it. I called for pricing and its, to me, outrageous! They want like 3,500 dollars per cpu core.
So, does anybody know a cheaper solution? Is there another driver that is free or relatively inexpensive that I can obtain? I am a contractor for the gov't and suggesting a solution to copy 1 table from a sybase database daily that would cost over 10,000 is just silly.
Any help is appreciated. 🙂
June 2, 2010 at 3:57 pm
DataDirect is a 3rd party so all their money is made off these drivers, which is why they're a bit pricey. There are also some other 3rd parties you could price check.
I'm pretty sure Sybase has a 64 bit version of their Open Client. If you have a licensed Sybase product I would think you would have access to obtain the client software, but I haven't dealt with them directly so I don't know if that's something they would charge extra for.
June 2, 2010 at 4:08 pm
Thanks for the reply... all the research I've been looking at has pointed to Sybase not developing a 64 bit driver and that people were having all kinds of problems trying to connect sql server 64 bit to sybase 32 bit. So, I dunno we'll see what happens. 🙂
June 2, 2010 at 5:35 pm
Yeah I'm not sure ... I didn't do a ton of research, just some quick Google lookups. I know there are other 3rd party driver providers though.
Your worst case scenario would be to maintain a small portion of the 2k environment with the driver that works and then move the data to 2005 from there ... at least until an affordable and viable driver is available to go directly from Sybase to your SQL 2005.
June 2, 2010 at 5:52 pm
What sybase driver is the sql 2000 database using?
I have ran into issues connecting to 32 bit Oracle from 64 bit sql with ssis. The solutions deal with changing the path to the oracle driver (that resides on sql server) to point to a path that does not contain the (x86) in the program files path. Apparently Oracle can't handle the long path with parentheses, maybe Sybase has the same issue?
My thought is you should be able to install the same driver you are using on sql 2000 box to the new sql 2005 box, possibly changing/re-registering the path to the dll to something other that c:\program files (x86)....?
June 10, 2010 at 1:06 pm
I actually tried doing that with the path and forcing SQL Server to use the 32 bit drivers...but no go.
We actually ended up scrapping the concept. Our client was ok with the idea of releasing our upgrade without this procedure. We are currently only pulling 1 table from sybase to sql server.
For a short term on demand solution, we basically created an Access Database to connect to sybase and grab the table and then I configured sql server to connect to the access database to pull the table in.
I was able to fix it by using the DirectDrive sybase driver but they really don't want to pay so much money for just a driver. So.... we'll see what happens.
Thanks for the replies. 🙂
June 11, 2010 at 7:16 am
We actually have an old SQLanywhere instance that we have to hit with SSRS once a day. We use an old Pervasive 32 bit client. We have several ol legacy Dbs that we have to hit this way. We set up a 32 bit instance along side the 64 bit default instance for the sole purpose of accessing these (both are SQL 2008). We then created links back to the 32 bit instance so we could access our older DBs (like Oracle 8.0.5 and SQLanywhere).
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
February 20, 2018 at 1:03 pm
So did you settle on the 3rd party drivers, or figure out the connection. I'm using the Sybase.AdoNet2.AseClient and Destination and it's working ok.
February 20, 2018 at 1:12 pm
Wow! Holy old post batman.... I did that 8 years ago...so I honestly can't tell you what we did. 🙁 I really don't remember. I hope there are better solutions after 8 years though. 🙂 Wish I had some better news.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply