March 11, 2011 at 7:25 am
Hi,
Is it possible to connect to SQL Server 2008 database with in a DTS package. Can a DTS package be used to pull data from SQL Server 2008 database, should the compatibility level on the SQL Server 2008 server be set to SQL Server 2000.
Any suggestions/thoughts.
Thank You
March 11, 2011 at 8:49 am
Yes you can, as long as it doesn't need to use SQLDMO to create objects.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2011 at 7:13 am
Hmm - I'm trying to connect and I can't get anywhere. I've got the server identified properly and the SQL Server authentication correct, but I keep getting the error message:
"Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied"
I'm trying to use the Microsoft OLE DB Provider for SQL Server.
April 13, 2011 at 7:26 am
is TCP/IP enabled for your sqlinstance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2011 at 8:11 am
Yes, it is. However, I'm investigating whether installing some utilities from the site below will help. They indicate that they're backward compatible, and will enable my SS 2000 install to talk cleanly to SS2008R2.
(I wish my clients would upgrade!)
Thanks for responding! I'll post results of my experiments when able.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1b2bd555-cb5b-47b9-88c7-3f89f3b43779
April 14, 2011 at 12:49 am
Roger Thomas (4/13/2011)
(I wish my clients would upgrade!)
Thanks for responding! I'll post results of my experiments when able.
Of course "sqlncli.msi" wouldn't hurt, but keep in mind your dts packages will not be able to use the new features if sqlncli isnt installed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2011 at 5:50 am
Thank you. I don't know much about all these packages, so I'm hoping that nothing I install will "break" my current installation. Of course, this is just in my lab, not on the production site, so I can always undo it (note to self: create restore point before installing packages...)
April 14, 2011 at 12:12 pm
Okay, taking a minimalist approach, I started out by installing just the SQL Server 10.0 Native Client on my Server 2003 machine (that's package sqlncli.msi). That turned out to be all I needed - all my other clients still work, and my DTS could now talk to the SQL Server 2008 running on Win Server 2008 without difficulty.
Some odd things:
It seemed that the package installed two drivers: an ODBC and a native driver. The native driver was identified within the DTS package as a small hatched square icon with a pink corner while the ODBC just had the icon that looks like most ODBC items. I only used the pink-cornered native driver and ignored the ODBC.
Within my DTS package, I have basically one data source pointing to the source and another to the destination. Hitherto, if I've changed something about either one (e.g. server, password, whatever), that change has automatically been reflected in all the copies of that source. So when my client shifted their source DB to a new server, I just updated one instance of the Source data element and it was reflected in all of them. However, in this case that didn't work: I had to visit every instance of the source and update it to tell it to use the native (not the ODBC) SQL Server 10.0 driver, and then I had to supply the parameters (server, logon, password, etc.) It was tedious, but better than redoing the entire DTS script.
I also had to refresh the transformations, however DTS does that behind the scenes. Updating the client wasn't enough, I had to open every transformation, click on the tab for the source, click on the destination, and then click on the transformations tab. I didn't have to change anything, I just had to do it. Once I did, and then saved the package, the script ran perfectly.
Thanks for your help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply