SQL2000/2005 DTS/SSIS Nightmare Trainwreck
-
God please someone help.
I am a not very SQL literate .NET developer (but please don't hold that against me).
Our DBA recently changed one of our servers to SQL2005, and converted my DTS to an SSIS. The problem is I need to be able to access the SSIS from a machine that only has SQL 2000 installed (and 2005 management studio express). I do not have dtexec nor do I have the suggested libraries of Microsoft.SQLSomething.DTS.whatever. My guess is those libraries only install with a full version of SQL 2005.
When I asked my DBA for help, he was an AHole and handed me a book.
THe internet is full of simple explanations that work fine if you have sql 2005 on the client machine....
Can someone please help me figure out how I can access this SSIS from an SQL 2000 box?
-
You are going to need to SQL Server 2005 Client tools loaded on the SQL Server 2000 box. To access the SSIS package you need the Busness Intelligence Developer Studio. This is nothing more than Visual Studio 2005.
The SQL Server 2005 client tools can coexist with the SQL Server 2000 client tools.
-
Where can I get the SQL 2005 Client Tools?
Also, I have VS 2005 but the required libraries are nowhere to be found
Perhaps the libraries come with the client tools? -
I guess let me further clarify what I DO have in VS2005
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.RegSvrEnum
Microsoft.SqlServer.Rmo
Microsoft.SqlServer.ServiceBrokerEnum
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
Microsoft.SqlServer.WmiEnum
But none of these seem to have what I need.
-
You will need to ask your friendly DBA to install the SQL Server 2005 client tools on your box (or where ever it is you need them). If you have problems going that direction and if you have the time to wait, you could spend $50 and order SQL Server 2005 Developers Edition for yourself, and then use it to install the client tools.
I'm not sure if you install the client tools from the eval version will give you what you need or not.
-
A bit more clarification.
With the client tools, you will be able to open, edit, and run the package through the designer (Visual Studio). You will not be able to simply execute the SSIS package from your SQL 2000 server without installing an SSIS server on the SQL 2000 box as well.
So, if you are trying to say execute an SSIS package that is stored in the MSDB database on server B from a SQL Agent task or command line on server A, you need to install an SSIS Server on server A as well. This can be done without interfering with the SQL 2000 install, but it does require another SQL 2005 license.
-
You could set up a job on the 2005 server to run the SSIS package and run the job from the commandline on the 2000 server though (cmdexec in SQLAgent)....
-
You can also SQL command to the SQL 2005 server and xp_cmdshell, remote command line, etc. These options do not require the SQL 2005 tools to be installed on the SQL 2000 server.
-
A followup... I want to install the SQL2000 client tools on my machine where I already have the 2005 client tools. Do I need to apply the SQL service pack (our servers are are sp3a) also?
-
It depends on what you are doing with the client tools. If you intend to execute or edit DTS packages, you will need to. If not, you may not need to.
It is, however, good practice to try to keep the client tools at the same service pack as the server they will be used to connect to and manage.
-
One more thing to add. If you intend to have your SSIS packages run Analysis Services 2000 cube or dimension processing, you will need to install the AS 2000 tools on the SSIS server running the packages. The issue is you cannot install these tools after SQL 2005 is already installed - you have to install 2000 first.
-
Thanks Michael!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply