Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » Use DTS package to connect to SQL Server 2008...
Add to briefcase

Use DTS package to connect to SQL Server 2008 Database... Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Ramdas Narayanan
Posted Friday, March 11, 2011 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 02, 2012 10:11 AM
Points: 34, Visits: 33
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
Post #1076857
ALZDBA
Posted Friday, March 11, 2011 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047
Yes you can, as long as it doesn't need to use SQLDMO to create objects.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- 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
Post #1076951
Roger Thomas
Posted Wednesday, April 13, 2011 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 12:01 PM
Points: 6, Visits: 6
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.
Post #1092801
ALZDBA
Posted Wednesday, April 13, 2011 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047
is TCP/IP enabled for your sqlinstance ?

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- 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
Post #1092813
Roger Thomas
Posted Wednesday, April 13, 2011 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 12:01 PM
Points: 6, Visits: 6
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
Post #1092855
ALZDBA
Posted Thursday, April 14, 2011 12:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047
Roger Thomas (4/13/2011)
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


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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- 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
Post #1093330
Roger Thomas
Posted Thursday, April 14, 2011 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 12:01 PM
Points: 6, Visits: 6
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...)
Post #1093426
Roger Thomas
Posted Thursday, April 14, 2011 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 12:01 PM
Points: 6, Visits: 6
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!
Post #1093721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.