DTS from XML

Does anyone know how to create a dts package that will take a XML document and import the contents to a new table in sql.
I have looked in the tasks of DTS and cannot find the xml connection.
Any pointers to some samples would be great.
ThanksConsider upgrading to SQL Server 2005, which has extended support for XML including data import through the Integration Services application. (Interestingly, Integration Services does NOT export XML. Go figure...).sql

DTS from stored procedure.

This issue has come up in our office.
Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?
blindmanI think this one has been addressed before here (but I can't find it right now either). I think it basically involved using sp_start_job to start a job linked to the DTS task.

Regards,

hmscott

Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman|||http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&forumid=6&threadid=227

for your reference, I copy the code in the above link and post it here

the user executing this package must have execute rights to the sp_OA* sps
in master.

CREATE PROC <SP Name> as

DECLARE @.hr int, @.oPKG int

EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Loading the Package:
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS
Package Name>")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN
END

--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END|||Why don't you want to use xp_cmdshell?|||Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman

the holy book also lists how to do the same from VB|||Thanks everybody.

blindman

DTS from MS SQL to Excel Spreadsheet Issue

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. Im using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named Results. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. Ive Googled this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jimright click on the spreadsheet and go to properties. what do you see?

the application dev team spent a week tossing something similar to this with Access for one their internal processes. I pointed it out a couple minutes after they asked me. I spent an hour laughing at my team of geniuses.

I am a joy to work with.|||Thanks for the idea. I thought of the read only flag as soon as I saw the read only error in the error log. Unfortunately that wasnt it. The issue was within the DTS package. If you open up the connection properties and click the top option to New Connection in an attempt to re-name the object, you actually create a new object with a new name leaving the older one in tacked but hidden in the background. This can only be seen if you go into Disconnected Edit under connections. There was an object names Connection1 and Connection2. These old connection objects where pointing to the development environment where the functional ID that was used to run the DTS did not have access to. Oops

Again thanks for the idea.

Jimsql

DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Check Pkg Errors
EXEC @.ret=spDisplayPkgErrors @.oPKG

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

RETURN @.ret
GO

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTS from excel file (excel filename is different everyday)

Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:

http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this

1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D

Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function

There's a syntax error. I will debug this later.

3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.

Please help :o|||Hi upalsen,

I got it already!
I followed your instructions. Many thanks to you. :)

Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************

Option Explicit

Function Main()
Dim vDay, vMonth, vYear, vDate

vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear

DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"

Main = DTSTaskExecResult_Success
End Function

Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code

Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if

Main = DTSTaskExecResult_Success
End Function

in your starting ActiveX script consider vCounter and write code to get branch code for each value

if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....

.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,

Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.

And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF

I saw it in the Dynamic Property Task under Steps. Am I correct?

Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.

if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....

DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"

yes, u r right. step names r listed in dynamic prop under "steps" heading.

dts from command line

if a user runs a dts package from a command line say something like this
C:\>dtsrun /s ServerName /u username /p P1l0t /n DTS_Package
using that /p password switch is the passwork example above P1l0t saved off
somewhere in a log file?
same question for if the command fails.
thanksNo...it won't report or log the password that was used.
-Sue
On Tue, 11 Oct 2005 15:05:05 -0700, "jason"
<jason@.discussions.microsoft.com> wrote:

>if a user runs a dts package from a command line say something like this
>C:\>dtsrun /s ServerName /u username /p P1l0t /n DTS_Package
>using that /p password switch is the passwork example above P1l0t saved off
>somewhere in a log file?
>same question for if the command fails.
>thanks|||thanks sue
"Sue Hoegemeier" wrote:

> No...it won't report or log the password that was used.
> -Sue
> On Tue, 11 Oct 2005 15:05:05 -0700, "jason"
> <jason@.discussions.microsoft.com> wrote:
>
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
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.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
--
BUTTONS:
OK
--
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services '
ANYbody have any ideas, pleases... Thank you!!
========================================================"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> >I migrated a DTS package from my 2000 test server to my 2005 test server
> >and I seem to be lost... The log file shows that everything converted
> >fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>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.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services '
>ANYbody have any ideas, pleases... Thank you!!
>========================================================>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>> >I migrated a DTS package from my 2000 test server to my 2005 test server
>> >and I seem to be lost... The log file shows that everything converted
>> >fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>> In SQL Server Management Studio, in Object Explorer, connect to the
>> Integration Services. Then chack whether the paclage is registered in the
>> local msdb database. If it is somewhere on the file system, search for the
>> *.dtsx files with Windows Explorer.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>
|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&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.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
BUTTONS:
OK
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services ?
ANYbody have any ideas, pleases... Thank you!!
================================================== ======
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>TITLE: Microsoft SQL Server Management Studio
>--
>Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>For help, click:
>http://go.microsoft.com/fwlink?ProdN...r&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.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services ?
>ANYbody have any ideas, pleases... Thank you!!
>================================================= =======
>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>

DTS from 2000 to 2005

I migrated a DTS package from my 2000 test server to my 2005 test server and
I seem to be lost... The log file shows that everything converted fine,
where will I find that package? PLEASE HELP ME!!!1 THANK YOU>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
In SQL Server Management Studio, in Object Explorer, connect to the
Integration Services. Then chack whether the paclage is registered in the
local msdb database. If it is somewhere on the file system, search for the
*.dtsx files with Windows Explorer.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Im SSMS click on connect and choose Server Type-- Integration Services
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ujXNdIX8GHA.1188@.TK2MSFTNGP05.phx.gbl...
>I migrated a DTS package from my 2000 test server to my 2005 test server
>and I seem to be lost... The log file shows that everything converted
>fine, where will I find that package? PLEASE HELP ME!!!1 THANK YOU
>|||something just doesn't seem right here >> I have SQL2000 installed as the
default instance, and 2005 as a named instance, when I register integration
services I only see the server name (which is by default the default
instance) I do not see, and can not find the 2005 named instance. So I
tried to work with that and I registered it and drilled down to the MSDB
container, when I open that container I get this error >>
TITLE: Microsoft SQL Server Management Studio
--
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&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.
Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)
--
BUTTONS:
OK
--
<< I am just guessing here but is this because I have the wrong SQL server
registered in integration services '
ANYbody have any ideas, pleases... Thank you!!
========================================
================
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> In SQL Server Management Studio, in Object Explorer, connect to the
> Integration Services. Then chack whether the paclage is registered in the
> local msdb database. If it is somewhere on the file system, search for the
> *.dtsx files with Windows Explorer.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||I'm a bit confused but I think your issue is that you are
using a named instances for 2005 and possibly didn't modify
the configuration file as indicated in the Books Online
topic so that SSIS would work with the named instance.
Locate the file MsDtsSrvr.ini.xml in your 2005 installation
path Program Files\Microsoft
SQL Server\90\DTS\Binn
The server name entry for a named instance should be
something along the lines of:
<ServerName>YourServer\YourInstanceName</ServerName>
-Sue
On Tue, 17 Oct 2006 09:39:53 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>something just doesn't seem right here >> I have SQL2000 installed as the
>default instance, and 2005 as a named instance, when I register integration
>services I only see the server name (which is by default the default
>instance) I do not see, and can not find the 2005 named instance. So I
>tried to work with that and I registered it and drilled down to the MSDB
>container, when I open that container I get this error >>
>TITLE: Microsoft SQL Server Management Studio
>--
>Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>For help, click:
>http://go.microsoft.com/fwlink?Prod...er&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 th
e
>Integration Services Service" in Server 2005 Books Online.
>Client unable to establish connection
>Encryption not supported on SQL Server. (MsDtsSrvr)
>--
>BUTTONS:
>OK
>--
><< I am just guessing here but is this because I have the wrong SQL server
>registered in integration services '
>ANYbody have any ideas, pleases... Thank you!!
> ========================================
================
>"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>message news:eJGXbXb8GHA.2128@.TK2MSFTNGP05.phx.gbl...
>

DTS from .NET (C#)?

I have a C# app that loads and runs a DTS package. It works great on my dev system but when I move it to the production system (which has SQL Server 2000 installed) it doesn't work. As soon as my app runs, it throws the exception:

"File or assembly name Interop.DTS, or one of its dependencies, was not found."

I've manually done: "regsvr32 dtspkg.dll" which succeeds but doesn't fix the problem.

Any ideas?Nevermind, I figured it out. DTS is a COM object and .NET generates a glue DLL: "Interop.DTS.dll".

I just needed to copy this DLL as well as my .exe

cool...|||Yeah!

DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS File Created After Stored Proc executed now what?

I'm trying to fire a DTS package through a stored procedure.

After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.

How would I use the DTS file to update my data.

Thanks,

RB

RB,

There is no direct way to do this via a SP. In my experience, I've seen three ways to work around this:

1) Use xp_cmdshell. This simply executes a command in the command shell. Usefull if you already have a batch script running your DTS.

2) Use OLE. You can create and work with Com objects via the sp_OA* methods. You will need to create the DTS objects and run properties from them.

3) Use a scheduled Job. Saw this recently, the idea is to create a new scheduled job that runs your DTS, then run the job, then delete the scheduled job.

None are pretty, but they all work.

-- Alex

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Mo
you can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
sql

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Moyou can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>

DTS execution from T-SQL

I really need help!!!

I built a DTS package that makes data pump from Paradox 7.0 to SQL Server. If I execute this package from Package Designer everything goes fine no matter if Paradox files are located in local or mapped folders.

However, If I execute the package from T-SQL, it crashes when if Paradox files are located in a mapped drive saying "not valid path" (With local folder it works)

Im using xp_cmdshell 'DTS_Run /S "server name" /N "package name" /E'

Can anyone tell me whats wrong with this

ThanksFirst of all check whether the account used have necessary privileges to carry tasks on paradox and on the machine.

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.

Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.

"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:

> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus

The Oracle client is required. So is paying attention to the license
agreement.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

DTS execution account

hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.The answer is that it depends (you knew I was going to say that).

If it is run interactively, it will run under the login of whoever is logged in (it will also run in the client context of your login, so if you are using EM from a client workstation and are not using it through Terminal Services, watch out!)

If it is run via a SQL Server job, then it will run in the context of the SQL Agent Service.

If you schedule it using NT Scheduled tasks, you can specify the user when setting up the task.

If ou execute it from an SP, I think you can specify the user context (though I don't swear to that -- it might pick up the user context of the person executing the SP).

Originally posted by mvargasp
hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.

DTS execution

I'm having trouble with dts. I used the dtswizard to create a dts package, yet when I use dtsrun with the package name, I get a message saying that the package cannot be found. I've even copied the .dtsx file into the same folder as dtsrun and still gotten the same message. Can someone shed some light on what I might be messing up. Btw, I'd love to run the dtsrunui, but apparently that file wasn't part of my distribution.where have u saved your package? inside sql server or as a disk file. it appears that it was saved as disk file. so u should mention the file path with /F switch during dtsrun call. to have it available under dtsrunui u should first select "Structured Storage File" and point to the location of the saved file if not saved inside sql server.|||I've tried saving the file or even copying the .dtsx file right to the root just to make the path simple, but then the process tells me it is not a dts package. How can the dtswizard create a file that isn't truly a dts package? In a related note, how can I "get my hands on" the dtsrunui executable. It's not on my system, nor is the dtsexecui. Either of these would make this process simpler for me I'm sure|||Greetings, Mark! For those of you following this thread, I'm coming into it with a fair bit of background from outside this thread... I didn't somehow magically divine the background from just what's posted.

I'm assuming that this is the SSIS package you built using the Transact-SQL that I provided, and that you are running this on the SQL 2005 Express machine... If any of those assumptions are bad, the rest of this message is worthless.

If I told you to use DTSRUN, that was my mistake... I should have specified DTSEXEC. See the web page on dtsrun to dtexec Command Option Mapping (http://msdn2.microsoft.com/en-us/library/ms345282.aspx) for more details on the conversion from DTSRUN to DTSEXEC.

Anywho, its good to see you here! Hopefully you'll get quicker answers here than waiting for me, but then again I do make "housecalls" for old friends when I'm in the neighborhood!

-PatPsql

DTS Execute from Com Object (Workgroup Version)

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

JayStang wrote:

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts