April 27, 2010 at 7:53 am
Hi Friends,
Few questions on DTS and Integration Services
Q1)Why SSIS service is only one instance ? why can't we have multiple instance's for SSIS as well like Database instance,SSAS & SSRS?
Q2)How to call a dts package from stored procedure which is stored in SQL Server
Q3)How to call a dts packages from a stored procedure which is stored in File System
Q4)Can i store my dts package as it is(i.e with .dts extension not .dtsx package) under SQL 2008 instance?
I tried this while connecting to SQL 2005 instance Integration Services --> Expand MSDB node and tried to import the dts package with .dts extension only.
But i ended up with 2 Errors. I thought that whether am pointing to correct running SQL 2005 instance into which am trying to import the dts package .
I went to services.msc and checked whether the SQL 2005 instance is running or not and also SQL Server integration Serveices is running or not. Both the things are running...
My doubt is whether storing the ssis package in File system and use "dtsrun.exe" and wrap it up in a stored procedure and call it in SQL 2008 or else do we have other options thru which we can save the dts as it is in SQL 2008 instance and call thru stored procedure or any means????
Any suggestions would be greatly appreciated!!!
Error 1:
TITLE: Import Package
------------------------------
The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement that was issued has failed.
-----------------------------
ADDITIONAL INFORMATION:
The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement that was issued has failed.
------------------------------
BUTTONS:
OK
------------------------------
Error :2
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)
------------------------------
Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)
-----------------------------
BUTTONS:
OK
------------------------------
April 27, 2010 at 3:04 pm
Q1. It just isn't, deal with it. Sorry, there really isn't a technical reason I can think of just MS doesn't support it.
Q2 & Q3. The easiest way to execute a DTS or SSIS package is by starting a SQL Agent Job, the other is using xp_cmdshell to call DTSRUN or DTEXEC (respectively)
Q4. DTSX and DTS are very different. *.dts is for DTS packages and *.dtsx is for SSIS packages, they are NOT interchangeable. So the answer to your question is yes, they will stay *.dts.
However, I think your underlying problem is that you don't understand that DTS packages cannot be loaded into an SSIS server, only SSIS packages can.
There is a backward compatibility and Legacy DTS component in the SQL 2008 Feature pack that needs to be installed. This allows you to edit SSIS packages in SSMS 2008. DTS Packages are stored in the individual SQL instances just like they were in SQL 2000 and you can see the DTS packages by going to this is SSMS: ServerName | Management | Legacy | Data Transformation Services
CEWII
April 28, 2010 at 1:40 am
Thanks for the info.
April 28, 2010 at 1:52 am
I have a question here. As you said we have the .dts package under Legacy folder. But now, i want to execute the .dts package from front-end not like open the package there itseld after installing the SQL 2005 dts designer tools. for executing the package do we need to save the dts packages in the file system and then write a stored procedure to which the parameters will be passed
and inside the stored procedure using "dtsrun" command i will create a dynamic sql to build all the global parameters and execute the procedure.
My question, is there any other way for executing these dts packages?
Again, if am importing the dts packages under LEGACY folder where or in which database (like we save it in "msdb") the packages gets stored???
April 28, 2010 at 8:21 am
The DTS files can be in either the SQL Server OR the filesystem. That shouldn't be an issue either way.
When DTS packages are saved into SQL server they are written into MSDB. This is similar to when SSIS packages are saved to MSDB but they are stored in different tables.
As I said before, the easiest way is to run it from a SQL Agent job, but there are still some security issues there depending on security context of the caller.
You can build a sproc to call DTSRUN using xp_cmdshell. Depending on the security context that the sproc is running in that may require additional setup to make work.
So while I hate to say it, it depends, do you need to be able to run two copies of that package at one time, if so then a SQL Agent job won't work.
You say you want to open the package in a front-end, I am unclear what you mean, just execute it or see the contents, what?
So why don't you tell us exactly what you are trying to accomplish start to end and I'm sure a recommendation can be made.
CEWII
April 28, 2010 at 10:23 pm
Hi,
Thanks. All i want to just confirm whether if i store the dts packages under LEGACY folder means it is stored in MSDB database or not so that i can call it from the application or command line saying
DTSRUN /S "instance" /N "packagename" /E /W "0"
April 28, 2010 at 11:27 pm
It is stored in msdb..
CEWII
May 7, 2010 at 8:34 am
Thank u
May 7, 2010 at 11:04 am
You are welcome.
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply