DTS to .net: February 2012

Sunday, February 26, 2012

dtsrun to start a package - cannot find xp_cmdshell

Newbie here.

In my database I'm needing to automate some data imports. I have the
import set up as a DTS package and it works wonderfully. But I'm
having trouble kicking it off as a stored procedure, or even from the
Query Analyzer. I used dtsrunui to get a proper connection string, but
when I enter

EXEC xp_cmdshell 'dtsrun /S "(local)" /N "MyPackage" /A
"KeyNum":"19"="19687627" /W "0" /E'

I get an error that says "Could not find stored procedure
'xp_cmdshell'". xp_cmdshell is indeed there, under Master, Extended
Procedures. I tried calling it dbo.xp_cmdshell, but that didn't help.
I'm guessing that I need to point the command to the location of the
SP, but I have no idea how to do that. Anyone willing to shed a little
light would get my eternal gratitude. :)

Thanks in Advance, maddmanuse master first or add master. to the procedure name. xp's don't operate
like sp's where you can call them from any database.
kevin Ruggles

"Maddman" <maddman_75@.yahoo.com> wrote in message
news:1102626201.340243.92890@.f14g2000cwb.googlegro ups.com...
> Newbie here.
> In my database I'm needing to automate some data imports. I have the
> import set up as a DTS package and it works wonderfully. But I'm
> having trouble kicking it off as a stored procedure, or even from the
> Query Analyzer. I used dtsrunui to get a proper connection string, but
> when I enter
> EXEC xp_cmdshell 'dtsrun /S "(local)" /N "MyPackage" /A
> "KeyNum":"19"="19687627" /W "0" /E'
> I get an error that says "Could not find stored procedure
> 'xp_cmdshell'". xp_cmdshell is indeed there, under Master, Extended
> Procedures. I tried calling it dbo.xp_cmdshell, but that didn't help.
> I'm guessing that I need to point the command to the location of the
> SP, but I have no idea how to do that. Anyone willing to shed a little
> light would get my eternal gratitude. :)
> Thanks in Advance, maddman|||That did the trick. Thanks!

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.com
Replace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com
|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script
.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/ ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A99
9760D84E91CD170332E5D7CB590B764064631A19
51589D6F310C930314F0F68864FCADAF0232
E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B
352C4319ED868A5232FAEDCA9031A0BB43FB
FF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> / ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A
999760D84E91CD170332E5D7CB590B764064631A
1951589D6F310C930314F0F68864FCADAF02
32E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE36
1B352C4319ED868A5232FAEDCA9031A0BB43
FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an I
D
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job vi
a
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
--
Message posted via http://www.sqlmonster.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?
Message posted via http://www.sqlmonster.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
SQLMonster.com" <u3288@.uwe> wrote:
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some
>> sort
>> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>> So what you are telling me is there the long string does not refer to an
>> ID
>[quoted text clipped - 14 lines]
>> sort
>> that I can compare?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1

DTSRun remote execution

Hi,

I am trying to figure out a way for a machine other than the SQL Server 2000 machine hosting my DTS jobs to fire off a DTS job on request. The jobs are scheduled, but I want to be able to fire them off remotely if the schedule fails. I don't have access Enterprise Manager on the second machine I want to start the jobs from.

An added restriction I have is that I can only execute command line shell scripts on the second machine. This could be a batch file, an executable or any other function available from the command line.

In an initial attempt I made the SQL Server Binn directory available via a restricted network share so the second machine could access the DTSRun.exe file. I then created the DTSRun commands and set them up to run. This worked fine, except I ran into another complication. The DTS Jobs I am running make use of an ActiveX DLL and an ActiveX Control (OCX). These files are registered on the server, but since using DTSRun forces execution to take place on the machine issuing the DTSRun command, the jobs fail because the components are not installed on the second machine.

I would like to try and aviod installing these components on the second machine and was hoping someone could suggest an alternative method for remotely fireing off these DTSJobs.

Any help would be greatly appreciated!

Thanks!
- BradMy first suggestion would be to use a VBScript that uses ADO to manually force execution of the remote job that runs the DTS package.

Just about anything else (SP, CmdExec, etc) is going to run the DTS in the context of your client which may not be desirable for reasons other than the registered ActiveX controls you mention.

Another option might be to create a job or DTS package that checks the original and makes sure that it has run (and executes it if it has not). Or you could simply add this as an additional error check into the original DTS package (set a retry counter and a Global Variable with the maximum number of retries). You would need to examine the business requirements and design a strategy that suits your environment.

Regards,

Hugh Scott

Originally posted by BradC
Hi,

I am trying to figure out a way for a machine other than the SQL Server 2000 machine hosting my DTS jobs to fire off a DTS job on request. The jobs are scheduled, but I want to be able to fire them off remotely if the schedule fails. I don't have access Enterprise Manager on the second machine I want to start the jobs from.

An added restriction I have is that I can only execute command line shell scripts on the second machine. This could be a batch file, an executable or any other function available from the command line.

In an initial attempt I made the SQL Server Binn directory available via a restricted network share so the second machine could access the DTSRun.exe file. I then created the DTSRun commands and set them up to run. This worked fine, except I ran into another complication. The DTS Jobs I am running make use of an ActiveX DLL and an ActiveX Control (OCX). These files are registered on the server, but since using DTSRun forces execution to take place on the machine issuing the DTSRun command, the jobs fail because the components are not installed on the second machine.

I would like to try and aviod installing these components on the second machine and was hoping someone could suggest an alternative method for remotely fireing off these DTSJobs.

Any help would be greatly appreciated!

Thanks!
- Brad|||Hi Hugh, thanks for the reply.

The VBScript option sounds like it may be the way to go. Using a number of retries approach may not work in this case because we need to be able to manually fire off the jobs on request. Catch is, the people who will be firing off these jobs don't have access (or skills) to go into enterprise manager to run them.

If a VBScript file is used to fire off a job using ADO, would the job be executed on the server or on the local client machine? From your comments on context I gather it will be run on the server (which is a good thing in this case as it avoids the ActiveX components needing to be installed on client machines).

Thanks again for the help!

- Brad

[SIZE=1]Originally posted by hmscott
My first suggestion would be to use a VBScript that uses ADO to manually force execution of the remote job that runs the DTS package.|||Im using a stored procedure to run my DTS from the outside whenever I want.

CREATE PROCEDURE ProcImportFile AS
EXEC msdb.dbo.sp_start_job @.job_name = 'Import_file'
GO

I scheduled the DTS package to get a job to call from the procedure.

Haven't tried this solution fully yet beacuse there is some problems regarding the permissions on my database. If i execute the package manually it works fine. But the scheduled job fails averytime...
Helpdesk hasn't fixed it yet.

dtsrun problem

Hello...
I can run:
dtsrun /SMyServer /Usa /P***** /Ntesting3
from the command line on the server and it works fine...
when I run it from a workstation on the lan, it
says "Not associated with a trusted SQL Server connection"
The security is set for both Windows and SQL authentication.
Can anyone help with this?
thanks in advance,
bob mcclellanHi
Do you have any aliases set for this server? You may want to try the servers
IP address instead if the name.
John
"John 3:16" wrote:

> Hello...
> I can run:
> dtsrun /SMyServer /Usa /P***** /Ntesting3
> from the command line on the server and it works fine...
> when I run it from a workstation on the lan, it
> says "Not associated with a trusted SQL Server connection"
> The security is set for both Windows and SQL authentication.
> Can anyone help with this?
> thanks in advance,
> bob mcclellan
>
>

DTSRUN privileges?

I am trying to get a DTS package to be run from the command line with
the dtsrun utility. The DTS package is stored in the database. The user
I supply is a user in the database. I get an error stating "SQL Server
does not exist or access denied." It looks to me like the SQL Server
instance does exist because it tries to start the package. I get
"DTSRun: Executing". If I put in a server that is non-existent, I do not
get that message. I also know that my username and password are correct.

Here is output from my attempt to run dtsrun for my DTS pkg (server,
user, password change to protect my db security):

C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.

I suspect that my user I am connecting to the database with does not
have privileges to execute the DTS package. I cannot determine, from
BOL, what privs I need to grant to this user to let them execute this
package. Any ideas?

TIA,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - UnknownHi Brian.

The fact that it says:
> DTSRun: Loading...
> DTSRun: Executing...

indicates your user can run DTSRUN. What is more likely is that user cannot
connect to the servers and databases named in the package.

Try going into Query analyzer and connect to the server as that user and see
if you can connect to the database and select from some of its tables.

--
-Dick Christoph

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||To add to Dick's response, be aware that the DTS package runs on same
machine as DTSRUN. You'll get these errors if you run the package on a
client machine and have specified SQL Server name 'local' in the DTS
connection.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||DickChristoph wrote:
> Hi Brian.
> The fact that it says:
>> DTSRun: Loading...
>> DTSRun: Executing...
> indicates your user can run DTSRUN. What is more likely is that user cannot
> connect to the servers and databases named in the package.
> Try going into Query analyzer and connect to the server as that user and see
> if you can connect to the database and select from some of its tables.

The user can connect to the database and query tables just fine with QA.
This is not a Windows user, but rather one that is authenticated in the
database.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Dan Guzman wrote:
> To add to Dick's response, be aware that the DTS package runs on same
> machine as DTSRUN. You'll get these errors if you run the package on a
> client machine and have specified SQL Server name 'local' in the DTS
> connection.

I do plan on running the DTSRUN utility from a client machine, but I
have stated the SQL Server's name in the /S switch. I did not use
'local' or '.' for the server name since I know the package is not local
to the client.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Hi Brian

Can you open the package and look at the connections. Dan was suggesting
that one of these connections might be "(local)". also I would add to my
previous comment, the username specified for these connections might lack
the privileges to connect to the database.

Also if to open the package and execute it from the designer you can see
which step is crashing.

--
-Dick Christoph
"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:Iyrtp9.25D@.igsrsparc2.er.usgs.gov...
> Dan Guzman wrote:
>> To add to Dick's response, be aware that the DTS package runs on same
>> machine as DTSRUN. You'll get these errors if you run the package on a
>> client machine and have specified SQL Server name 'local' in the DTS
>> connection.
>>
> I do plan on running the DTSRUN utility from a client machine, but I have
> stated the SQL Server's name in the /S switch. I did not use 'local' or
> '.' for the server name since I know the package is not local to the
> client.
> Thanks,
> Brian
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

DTSrun permissions

Hi

We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.

C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

We have also tried this with the /E option with the same results.

Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'

well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.

DTSRUN password and username

Hello,
I run DTSRUN command in SP on many copmuters.
No problem with local server parameter but how can i use constant of the /U and /P parameters not to write username and password every time. could this be taken from the local server itself?
Thx
InonliYou can use /E to use integrated security.|||Originally posted by nigelrivett
You can use /E to use integrated security.

thank you very much
and maybe you know this
i have active x script inside the DTS that do
cnADODB.Open "Driver={SQL Server};Server=212.179.64.215;UserName=sa;Password =Ropin1970;Database=SoftAd"
i want to change the ip to local but it not let me
any idea?|||Have you tried Server=(local)

I usually put the server in a glabal variable and use that in the script.
If you are loading from an SP then you can use dtsrun to set it to @.@.servername.

Thanks for your sa password :).|||Originally posted by nigelrivett
Have you tried Server=(local)

I usually put the server in a glabal variable and use that in the script.
If you are loading from an SP then you can use dtsrun to set it to @.@.servername.

Thanks for your sa password :).
not working with server = (local)
the problem with global variable that i use many servers (by the way they have other passwords for sa...) and cant run it with /A as have mssql97... not 2000|||In that case see
http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

To load the package and set global variables before running.|||Originally posted by nigelrivett
In that case see
http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

To load the package and set global variables before running.

thank you a lot this will help me also in other thing!

DTSRUN is it available in SQL Server 64-bit

Hi There,
Tried to look for the DTSRUN utility in SQL server 64-bit , but wasn't
able to locate it, is it supported to run on the 64-bit server? if not , are
there any alternatives rather than using BCP or running the DTSRUN utility
from a 32-bit machine.
Thanks in advance
Asser MaanyHi
The short answer. No.
The long answer. You could install it in the WOW64 (Windows on Windows - a
64 to 32 bit Thunking layer present in 64 Bit Windows), but, it will not be
supported by Microsoft.
It is best to run EM and DTSRUN on a 32 bit server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Asser Maany" <asser.maany@.microtech.com.eg> wrote in message
news:OCL17fVrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> Hi There,
> Tried to look for the DTSRUN utility in SQL server 64-bit , but wasn't
> able to locate it, is it supported to run on the 64-bit server? if not ,
> are there any alternatives rather than using BCP or running the DTSRUN
> utility from a 32-bit machine.
> Thanks in advance
> Asser Maany
>

DTSrun in Stored Procedure

Hi,

I'm writing a stored procedure to run a dts package and I've successfuly
got this working using my sotred proc and the syntax of dtsrun is
correct.

However, I'm trying to pass a variable to the DTSrun command and this is
where I'm having the problem

the code for the proc is:

Declare @.partcode nvarchar(255)

EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '

Everytime I run it I get this error:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.

can someone help me with this please?

M3ckon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You'll to build the command string before executing xp_cmdshell. For
example:

DECLARE @.Command varchar(1000)

SET @.Command = 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '

EXEC master..xp_cmdshell @.Command

--
Hope this helps.

Dan Guzman
SQL Server MVP

"m3ckon" <anonymous@.devdex.com> wrote in message
news:407eb7e2$0$206$75868355@.news.frii.net...
> Hi,
> I'm writing a stored procedure to run a dts package and I've successfuly
> got this working using my sotred proc and the syntax of dtsrun is
> correct.
> However, I'm trying to pass a variable to the DTSrun command and this is
> where I'm having the problem
> the code for the proc is:
> Declare @.partcode nvarchar(255)
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '
> Everytime I run it I get this error:
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> can someone help me with this please?
> M3ckon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

dtsrun from stored procedure

Hi all,

I am currently using a series of dts packages to extract and export data. To run the packages, I have a table (TW_DTS) which lists each dts package name, I then use a stored procedure to loop through each and 'dtsrun' it. If a package fails then the error is stored in another table from within the individual dts.

This solution works in principle, however when the stored procedure runs some of the dts packages fail for an unknown reason. Unfortunately the problem is not consistent - it is not always the same package, or even same number of packages that fail. Each package will work when run individually.

I have listed the stored procedure below, any advice may save me from throwing my laptop through the window.

Cheers.

----

CREATE PROCEDURE [spTWDTS] AS

DECLARE @.unique_id varchar(50)
DECLARE @.dts_name varchar(50)
DECLARE @.start_dttm datetime
DECLARE @.end_dttm datetime
DECLARE @.CMD varchar(1000)
DECLARE @.ERROR varchar(1000)

DECLARE
dts_cur CURSOR FOR SELECT unique_id, dts_name, start_dttm, end_dttm from TW_DTS where status = 'A'

open dts_cur

FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

while @.@.fetch_status = 0

BEGIN
-- Set as No Error
SET @.ERROR = 0

--update the start date
UPDATE TW_DTS set start_dttm = getdate() where unique_id = @.unique_id

--run the package
SET @.CMD = 'dtsrun /S myserver /U myusername /P mypassword/N '+@.dts_name
EXECUTE @.ERROR = master..xp_cmdshell @.CMD

--update the end date
UPDATE TW_DTS set end_dttm = getdate() where unique_id = @.unique_id

--move to next dts package
FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

END

CLOSE dts_cur
DEALLOCATE dts_cur

RETURN @.ERROR
GONot sure about this but perhaps dtsrun /L might help in finding out where/when the execution fails. You might want to add a select 'executed: ' + @.dts_name, 'Error', @.error after the xp_cmdshell. I assume there's a space between the password and the /N ? And perhaps adding a 'start /w ' to the command might help.

Like I said, I'm not sure if their any help but it might get you on the right track.|||Thanks Kaiowas,

Tried that and got the following error message in the log file:

Step 'Copy Data from Results to [export].[dbo].[extract] Step' failed

Step Error Source: Microsoft OLE DB Provider for Oracle
Step Error Description:Oracle error occurred, but error message could not be retrieved from Oracle.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:

The step just runs an oracle query and put the results into the 'extract' table.

Not really any the wiser now, do you know how I can get to the oracle error?|||It's an MDAC error message, so Oracle won't help you there. I find google to be very helpful in such cases, it came up with: http://support.microsoft.com/kb/255084/EN-US/

DTSRun Fails while executing from COM

Hi,
i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute.

Function ExeDTSRun(DTSName, mKey As String)
Dim mDTS, spServerName, spUid, spPwd As String

spUid = "sa"
spPwd = "test"
spServerName = "server"

mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0"""
Shell mDTS, vbNormalFocus
ExeDTSRun = "Successfully Run DTS : " & DTSName
End Function

Regards
AdiDo you get an error?|||First guess: Does the account running the IIS thread have permission (NT permission) to execute DTSRUN.EXE. Also, if the DTS package is accessing resources other than on the local machine, you will need to grant permission for that as well.

DTSRun error

Hi,
I am trying to run the following command within my Query Analyzer and get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.

The command is:
DTSRun /S fa314146 /U sa /P admin /N TXTFile_To_SQL

I tried using quotes around my optional parameters but it didn't help.
Thanks for you help.
Igor - isheyman139@.worldsavings.comI found it: xp_cmdshell needs to be used from within Query Analyzer.

Quote:

Originally Posted by isheyman

Hi,
I am trying to run the following command within my Query Analyzer and get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.

The command is:
DTSRun /S fa314146 /U sa /P admin /N TXTFile_To_SQL

I tried using quotes around my optional parameters but it didn't help.
Thanks for you help.
Igor - isheyman139@.worldsavings.com

I found it: xp_cmdshell needs to be used from within Query Analyzer.|||

Quote:

Originally Posted by isheyman

I found it: xp_cmdshell needs to be used from within Query Analyzer.

Good approach and Reply keep it up.

DTSRun Encrypted

In a Multi Server Job under SQL Server Agent (MSX), we have a series of
steps like:

DTSRun /~Z0x8E8635E6BBA...

The ~Z means it's an encrypted hexidecimal string. The person who created
this step is no longer accessible, and the string can't be trivially
decrypted, so I don't know what this runs.

Someone once mentioned that I could go to Query Analyzer, run it, and
watch the execution plan. However, if I start the job, the execution
plan simply walks through the steps required to find the job, and doesn't
go into the details of the job. I don't know how to run the DTSRun
command directly within Query Analyzer.

Is there a way I can use the execution plan to determine what package this
DTSRun line is executing? Please give me the appropriate command or
commands.

-AdamIn theory you could do this using profiler, I have never tried it though...

All DTSRun does does is call the loadpackage and then executepackage methods
on the DTS.Package object, as long as the package is a SQL Server package
and hence gets loaded out of msdb the calls to the sp's in msdb should show
up

"Adam Augusta" <roxton@.wpi.edu> wrote in message
news:Pine.LNX.4.58.0309191701430.27905@.ccc8.WPI.ED U...
> In a Multi Server Job under SQL Server Agent (MSX), we have a series of
> steps like:
> DTSRun /~Z0x8E8635E6BBA...
> The ~Z means it's an encrypted hexidecimal string. The person who created
> this step is no longer accessible, and the string can't be trivially
> decrypted, so I don't know what this runs.
> Someone once mentioned that I could go to Query Analyzer, run it, and
> watch the execution plan. However, if I start the job, the execution
> plan simply walks through the steps required to find the job, and doesn't
> go into the details of the job. I don't know how to run the DTSRun
> command directly within Query Analyzer.
> Is there a way I can use the execution plan to determine what package this
> DTSRun line is executing? Please give me the appropriate command or
> commands.
> -Adam

DTSRun doesn't complete

From a cmd prompt I am trying to DTS data out of an old version of
Btrieve to SQL Server 2000. It does the work just fine.
The last thing it displays is
DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
Step
However, at this point it just sits there and never comes back to the
command prompt.
Anyone know what may be wrong?John.Wentworth@.emersonprocess.com (JohnW) wrote in message news:<5d096b4e.0312300929.42ac5776@.posting.google.com>...
> From a cmd prompt I am trying to DTS data out of an old version of
> Btrieve to SQL Server 2000. It does the work just fine.
> The last thing it displays is
> DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
> Step
> However, at this point it just sits there and never comes back to the
> command prompt.
> Anyone know what may be wrong?
Nevermind I figured it out|||Wat was the reason for it not to come back to cmdprompt ?
jobi
"JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
news:5d096b4e.0312301428.348e1a2f@.posting.google.com...
> John.Wentworth@.emersonprocess.com (JohnW) wrote in message
news:<5d096b4e.0312300929.42ac5776@.posting.google.com>...
> > From a cmd prompt I am trying to DTS data out of an old version of
> > Btrieve to SQL Server 2000. It does the work just fine.
> >
> > The last thing it displays is
> >
> > DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
> > Step
> >
> > However, at this point it just sits there and never comes back to the
> > command prompt.
> >
> > Anyone know what may be wrong?
> Nevermind I figured it out|||"jobi" <jobi@.reply2.group> wrote in message news:<bstuqh$rte$1@.reader08.wxs.nl>...
> Wat was the reason for it not to come back to cmdprompt ?
Well for some reason you have specifically set the "Close Connection
on Completion" switch. This is found in the Workflow Properties
screen, Options tab. As soon as I set this the DTS package finishes
just like it should and comes back to the command prompt.
I am trying to DTS an entire database consisting of 198 tables. I have
to go to each connection and set this. They don't give you a way to
set this across the entire package. What a pain in the ass!
> jobi
> "JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
> news:5d096b4e.0312301428.348e1a2f@.posting.google.com...
> > John.Wentworth@.emersonprocess.com (JohnW) wrote in message
> news:<5d096b4e.0312300929.42ac5776@.posting.google.com>...
> > > From a cmd prompt I am trying to DTS data out of an old version of
> > > Btrieve to SQL Server 2000. It does the work just fine.
> > >
> > > The last thing it displays is
> > >
> > > DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
> > > Step
> > >
> > > However, at this point it just sits there and never comes back to the
> > > command prompt.
> > >
> > > Anyone know what may be wrong?
> >
> > Nevermind I figured it out|||So do it using the object model.
Function Main()
dim tsk
dim stp
dim pkg
dim cusTask
dim prp
set pkg = DTSGlobalVariables.Parent
for each tsk in pkg.Tasks
if tsk.CustomTaskID = "DTSDataPumpTask" THEN
for each stp in pkg.steps
if stp.TaskName = tsk.Name then
stp.Properties("CloseConnection").Value = -1
end if
Next
End if
Next
Main = DTSTaskExecResult_Success
End Function
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
news:5d096b4e.0312310501.9624dee@.posting.google.com...
> "jobi" <jobi@.reply2.group> wrote in message
news:<bstuqh$rte$1@.reader08.wxs.nl>...
> > Wat was the reason for it not to come back to cmdprompt ?
> Well for some reason you have specifically set the "Close Connection
> on Completion" switch. This is found in the Workflow Properties
> screen, Options tab. As soon as I set this the DTS package finishes
> just like it should and comes back to the command prompt.
> I am trying to DTS an entire database consisting of 198 tables. I have
> to go to each connection and set this. They don't give you a way to
> set this across the entire package. What a pain in the ass!
>
>
> >
> > jobi
> > "JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
> > news:5d096b4e.0312301428.348e1a2f@.posting.google.com...
> > > John.Wentworth@.emersonprocess.com (JohnW) wrote in message
> > news:<5d096b4e.0312300929.42ac5776@.posting.google.com>...
> > > > From a cmd prompt I am trying to DTS data out of an old version of
> > > > Btrieve to SQL Server 2000. It does the work just fine.
> > > >
> > > > The last thing it displays is
> > > >
> > > > DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
> > > > Step
> > > >
> > > > However, at this point it just sits there and never comes back to
the
> > > > command prompt.
> > > >
> > > > Anyone know what may be wrong?
> > >
> > > Nevermind I figured it out|||thanks for the feedback.
Happy newyear.
"JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
news:5d096b4e.0312310501.9624dee@.posting.google.com...
> "jobi" <jobi@.reply2.group> wrote in message
news:<bstuqh$rte$1@.reader08.wxs.nl>...
> > Wat was the reason for it not to come back to cmdprompt ?
> Well for some reason you have specifically set the "Close Connection
> on Completion" switch. This is found in the Workflow Properties
> screen, Options tab. As soon as I set this the DTS package finishes
> just like it should and comes back to the command prompt.
> I am trying to DTS an entire database consisting of 198 tables. I have
> to go to each connection and set this. They don't give you a way to
> set this across the entire package. What a pain in the ass!
>
>
> >
> > jobi
> > "JohnW" <John.Wentworth@.emersonprocess.com> wrote in message
> > news:5d096b4e.0312301428.348e1a2f@.posting.google.com...
> > > John.Wentworth@.emersonprocess.com (JohnW) wrote in message
> > news:<5d096b4e.0312300929.42ac5776@.posting.google.com>...
> > > > From a cmd prompt I am trying to DTS data out of an old version of
> > > > Btrieve to SQL Server 2000. It does the work just fine.
> > > >
> > > > The last thing it displays is
> > > >
> > > > DTSRun OnFinish: Copy Data from Account to [Test123].[dbo].[Account]
> > > > Step
> > > >
> > > > However, at this point it just sits there and never comes back to
the
> > > > command prompt.
> > > >
> > > > Anyone know what may be wrong?
> > >
> > > Nevermind I figured it out

DTSRun causes The Memory could not be "read" application error

Hi,

I've got this dts package in sql server 2000 sp4 that has several
transformation tasks with an oracle database (10g) as the destination.
The package executes successfully when run through the dts designer but
when run using dtsrun, I get the following error at the completion of
all tasks in the dts package. The data inserts into the oracle database
but i always get this Application error

The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
memory could not be "read"

Does anyone know how I can fix this?

Thanks
LynHi

I am not sure why this should happen but you may want to check that your
arguements are correct http://support.microsoft.com/?kbid=308801 and that
you can the same versions of MDAC on the machine it is running on and that
it is consistent (see MDAC component checker http://tinyurl.com/6gsv )
http://support.microsoft.com/?kbid=255900

John
<lyn.duong@.gmail.com> wrote in message
news:1130286935.981909.22760@.g43g2000cwa.googlegro ups.com...
> Hi,
> I've got this dts package in sql server 2000 sp4 that has several
> transformation tasks with an oracle database (10g) as the destination.
> The package executes successfully when run through the dts designer but
> when run using dtsrun, I get the following error at the completion of
> all tasks in the dts package. The data inserts into the oracle database
> but i always get this Application error
> The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
> memory could not be "read"
> Does anyone know how I can fix this?
> Thanks
> Lyn|||Hi John,

The mdac problem is relevant to sql server 7.0 and I've checked the
other kb. I don't pass any arguments. I have already got the latest
service pack installed for sql server 2000.

Lyn

John Bell wrote:
> Hi
> I am not sure why this should happen but you may want to check that your
> arguements are correct http://support.microsoft.com/?kbid=308801 and that
> you can the same versions of MDAC on the machine it is running on and that
> it is consistent (see MDAC component checker http://tinyurl.com/6gsv )
> http://support.microsoft.com/?kbid=255900
> John
> <lyn.duong@.gmail.com> wrote in message
> news:1130286935.981909.22760@.g43g2000cwa.googlegro ups.com...
> > Hi,
> > I've got this dts package in sql server 2000 sp4 that has several
> > transformation tasks with an oracle database (10g) as the destination.
> > The package executes successfully when run through the dts designer but
> > when run using dtsrun, I get the following error at the completion of
> > all tasks in the dts package. The data inserts into the oracle database
> > but i always get this Application error
> > The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
> > memory could not be "read"
> > Does anyone know how I can fix this?
> > Thanks
> > Lyn

DTSRUN /A Issue

Hi,
I have a DTS named "Rahul_Test" where i have a global variale named "Rahul" INteher (1 Byte). N trying to assign this variable from SQL statement. Here is the syntax that I am writing.

exec master..xp_cmdshell 'DTSRUN -E -S142.102.27.222 -Usa -Psa -N"Rahul_Test" -ARahul:8="aaa"'

After executing the syntax I am getting the following message...

DTSRun: Loading...
DTSRun: Executing...
DTSRun: Package execution complete.
NULL

But the global variable "Rahul" doesnot get changed to 225.

Kindly help me out in this regard.

Thanks in advance.

Rahul Jhaone qn,

"Rahul" is integer or string data type?|||Rahul is a string data type|||Have tried even with this syntax....... still it failed with the same output

exec master..xp_cmdshell 'DTSRUN -E -S142.102.27.222 -Usa -Psa -N"Rahul_Test" -A"Rahul:8"="225"'|||Rahul is String Data Type. By Mistake it was written as Integer while posting the issue........

DTSRUN

When I am on the sql server and I issue my dtsrun statement, the package
runs fine, however, when I run it from another server, it reports that the
package ran successfully, however, upon further inspection I see that the
package did not run at all.
Any ideas ?
I would check security issues first.
Secondly, what do you mean by it ran successfully? Does your DTS package
throw errors when it runs into problems, or does it just go merrily on about
it's business and report that it finished all of it's steps. (Whether they
were successful or not).
Not much help, but more information would be useful and I'm sure others here
will have more ideas for you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net. ..
> When I am on the sql server and I issue my dtsrun statement, the package
> runs fine, however, when I run it from another server, it reports that the
> package ran successfully, however, upon further inspection I see that the
> package did not run at all.
> Any ideas ?
>
>
|||I execute the following from the DOS prompt from a server other than the SQL
server...
C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
1. Messages appear in the DOS window which state that the package is running
2. The final message states that the package successfully completed
However, upon further review, none of the tasks in the package were
completed.
I did get permission errors to begin with, but after I fixed them, I just
get that the package completed successfully when in fact, none of the tasks
were completed.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> I would check security issues first.
> Secondly, what do you mean by it ran successfully? Does your DTS package
> throw errors when it runs into problems, or does it just go merrily on
about
> it's business and report that it finished all of it's steps. (Whether
they
> were successful or not).
>
> Not much help, but more information would be useful and I'm sure others
here[vbcol=seagreen]
> will have more ideas for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "rob" <rwc1960@.bellsouth.net> wrote in message
> news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net. ..
the[vbcol=seagreen]
the
>
|||Try using the /L option and specifying a log file for the package status
information. This may help you narrow down the problems.
When you run the package from the SQL Server machine, are you logged in as a
local Admin? I'm not sure about the security issues with the DTS packages,
but that is still the first place I would be looking in this case. Check
the log file and see what information is coming back to you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:GcI%c.76012$N11.16495@.bignews5.bellsouth.net. ..
> I execute the following from the DOS prompt from a server other than the
SQL
> server...
> C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
> 1. Messages appear in the DOS window which state that the package is
running
> 2. The final message states that the package successfully completed
> However, upon further review, none of the tasks in the package were
> completed.
> I did get permission errors to begin with, but after I fixed them, I just
> get that the package completed successfully when in fact, none of the
tasks[vbcol=seagreen]
> were completed.
>
>
>
> "Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
> news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
package[vbcol=seagreen]
> about
> they
> here
package
> the
> the
>

DTSRUN

When I am on the sql server and I issue my dtsrun statement, the package
runs fine, however, when I run it from another server, it reports that the
package ran successfully, however, upon further inspection I see that the
package did not run at all.
Any ideas ?I would check security issues first.
Secondly, what do you mean by it ran successfully? Does your DTS package
throw errors when it runs into problems, or does it just go merrily on about
it's business and report that it finished all of it's steps. (Whether they
were successful or not).
Not much help, but more information would be useful and I'm sure others here
will have more ideas for you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> When I am on the sql server and I issue my dtsrun statement, the package
> runs fine, however, when I run it from another server, it reports that the
> package ran successfully, however, upon further inspection I see that the
> package did not run at all.
> Any ideas ?
>
>|||I execute the following from the DOS prompt from a server other than the SQL
server...
C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
1. Messages appear in the DOS window which state that the package is running
2. The final message states that the package successfully completed
However, upon further review, none of the tasks in the package were
completed.
I did get permission errors to begin with, but after I fixed them, I just
get that the package completed successfully when in fact, none of the tasks
were completed.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> I would check security issues first.
> Secondly, what do you mean by it ran successfully? Does your DTS package
> throw errors when it runs into problems, or does it just go merrily on
about
> it's business and report that it finished all of it's steps. (Whether
they
> were successful or not).
>
> Not much help, but more information would be useful and I'm sure others
here
> will have more ideas for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "rob" <rwc1960@.bellsouth.net> wrote in message
> news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> > When I am on the sql server and I issue my dtsrun statement, the package
> > runs fine, however, when I run it from another server, it reports that
the
> > package ran successfully, however, upon further inspection I see that
the
> > package did not run at all.
> >
> > Any ideas ?
> >
> >
> >
>|||Try using the /L option and specifying a log file for the package status
information. This may help you narrow down the problems.
When you run the package from the SQL Server machine, are you logged in as a
local Admin? I'm not sure about the security issues with the DTS packages,
but that is still the first place I would be looking in this case. Check
the log file and see what information is coming back to you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:GcI%c.76012$N11.16495@.bignews5.bellsouth.net...
> I execute the following from the DOS prompt from a server other than the
SQL
> server...
> C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
> 1. Messages appear in the DOS window which state that the package is
running
> 2. The final message states that the package successfully completed
> However, upon further review, none of the tasks in the package were
> completed.
> I did get permission errors to begin with, but after I fixed them, I just
> get that the package completed successfully when in fact, none of the
tasks
> were completed.
>
>
>
> "Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
> news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> > I would check security issues first.
> >
> > Secondly, what do you mean by it ran successfully? Does your DTS
package
> > throw errors when it runs into problems, or does it just go merrily on
> about
> > it's business and report that it finished all of it's steps. (Whether
> they
> > were successful or not).
> >
> >
> > Not much help, but more information would be useful and I'm sure others
> here
> > will have more ideas for you.
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> > "rob" <rwc1960@.bellsouth.net> wrote in message
> > news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> > > When I am on the sql server and I issue my dtsrun statement, the
package
> > > runs fine, however, when I run it from another server, it reports that
> the
> > > package ran successfully, however, upon further inspection I see that
> the
> > > package did not run at all.
> > >
> > > Any ideas ?
> > >
> > >
> > >
> >
> >
>

Dtsrun

I run DTS run from the VB6 using SHELL, when i run each time new process is invoking and previous process is not closing. bcz. of this lot of memory is taking and my system is hanging. pls. help me how to resolve this.
Thank You all
D. Shyam Benagal
dsb@.inspira.com
Inspira TechnologiesA bit more information would be helpful:

1. Is the VB6 app running on the same machine as the SQL Server? or is it a different client?

2. Have you considered using SQL Agent to schedule and run the DTS package?

3. Does the prior instance of the DTS package actually terminate and finish its actions? or is it still running when the next instance pops up?

Also pls include some information regarding what the DTS package is doing, what tables/procs it references.

Regards,

Hugh Scott

Originally posted by SHYAM
I run DTS run from the VB6 using SHELL, when i run each time new process is invoking and previous process is not closing. bcz. of this lot of memory is taking and my system is hanging. pls. help me how to resolve this.
Thank You all
D. Shyam Benagal
dsb@.inspira.com
Inspira Technologies|||Thank's for u r reply Hugh Scott. That problem is rectified (some memory leak in the Pakage)

rgds
shyam

Originally posted by hmscott

A bit more information would be helpful:

1. Is the VB6 app running on the same machine as the SQL Server? or is it a different client?

2. Have you considered using SQL Agent to schedule and run the DTS package?

3. Does the prior instance of the DTS package actually terminate and finish its actions? or is it still running when the next instance pops up?

Also pls include some information regarding what the DTS package is doing, what tables/procs it references.

Regards,

Hugh Scott|||what's the sql server version?

DTSProperty.GetValue

Hi,
Seems a pretty straightforward problem however, I have now spent some time on it. I have a DTSProperty object and want to get its value. I am able to get all the other attributes like Name, Type just fine. The GetValue method on the DTSProperty class takes a object and returns a object. If I want to get a value of the current property what do I pass to GetValue?
I have something like

foreach (DtsProperty property in controlFlow.Properties)
{
string propName = property.Name;
//I want to do this
if(property.Get)
property.GetValue(?)//what do I pass in here

}

Thanks,
-Suri.

object x = property.GetValue(controlFlow);|||Thanks Darren. It works just fine. However I am not sure why the GetValue method was designed to take a TaskHost object as opposed to the IDTSCustomProperty90 which has a value property on it.|||Yes I know what you mean. I read the BOL section and thought that, but looking at code I have that works, well that is what worked.|||This is an optimization. Each object type (more accurately, interface) is chached internally for property objects. Were it not so, property objects would multiply geometrically. When you get a pointer to the properties collection, you're actually getting a pointer to a template collection for the interface. When you get the value, then the template asks the object you pass in for the value.
Make sense?
K

DTSLookup error handling

Hello all,
Does any one know how I can capture the sql error message when the sql statement in DTSLookup step fails?
e.g.
I have this sql statement in a DTSLookup task:
insert into table1 (col1) values (?); select @.@.ERROR

Let's say for some reason (bad data), my insert statement fails and I want to know why. So I added "select @.@.ERROR" to capture the error. However, from the Lookup window, I cannot save the result of @.@.ERROR to a global variable or to an output parameter. I want to log this error in the next step that handles the OnFailure workflow, but I don't know how.

Can someone help please?

Thanks,
K.Hello all,

Can someone help please?

Thanks,
K.

Check out this link (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q221193).

From what I recall, you cannot handle DTS errors in VB Script, which is maddening to me. We sorta got around this by creating a VB DLL and then instantiated the VB DLL from within a calling DTS package. I've never been really happy with the solution, but I'm just the production DBA and not the "real" DBA :rolleyes: .

Regards,

hmscott

DTSing Oracle data into SQL Server

I need to import some data from Oracle into Sql Server.
I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).
Do I need to install anything else on the SQL Server so that I can import the data?
Cheers
NickOriginally posted by riggers
I need to import some data from Oracle into Sql Server.

I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).

Do I need to install anything else on the SQL Server so that I can import the data?

Cheers

Nick
I don't think so|||linked server|||I've tried just installing the OleDb provider but it appears from furthering reading on the web that I need to install the oracle client tools aswell as the OleDb provider.

I've been told that we are running Oracle 10.7 and I can not appear to find the client tools for this on Oracle's website.

Does anyone know where the client tools for this version of Oracle are?

Cheers

Nick

DTSExecResult.Canceled - Misspelled?

I just noticed that the enum item

DTSExecResult.Canceled

...............is misspelled. Tis a pity.

Hi Joe,

The Mirriam-Webster online dictionary indicates that "Canceled" and "Cancelled" are each acceptable, but I will check with our documentation folks for a definitive answer. Thanks for the note.

Mark

|||That's not misspelled.|||

I stand corrected.

I guess I am used to the being only one correct spelling for the word.

|||

Joe S wrote:

I stand corrected.

I guess I am used to the being only one correct spelling for the word.

According to numerous online dictionary sources two "L"s is the british (UK) version, while the singular "L" is the US English version.|||

I have verified this with our documentation folks as well.

Thanks Joe,

Mark

DTSExec and Logging to Windows Event Log

I am trying to use either DTExec or DTExecUI to run a package while logging to the Windows Event log. This purports to be supported based on books online.

I have tried passing the CLSID and the ProgId on the command line and always get a message about the logger parameter being invalid.

I am using a command line like this:

dtexec /FILE "C:\Etl.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "DTS.LogProviderEventLog.1"; /SET "\Package.DestinationDatabase";"Test15-1Warehouse" /SET "\Package.SourceDatabase";ATEST15D1

Message I get is:

Argument "DTS.LogProviderEventLog.1;" for option "logger" is not valid.

Same message with the CLSID.

Help!

Normally the Logger switch expects a 2nd configstring argument within the quotes. The Event Log doesn't have anything to configure, but it seems to want the semicolon that would normally precede the 2nd argument.

Instead of /LOGGER "DTS.LogProviderEventLog.1",
please try /LOGGER "DTS.LogProviderEventLog.1;".|||Actually the semicolon is already specified (just outside the quotes instead of inside which should work (if not it is a bug). The problem is that the config string is not optional so the argument is missing the config string as far as DTExec is concerned. Try putting any character there and it should work as the event log provider ignores the config string.

/LOGGER "DTS.LogProviderEventLog.1;x".

thanks,|||Thanks.

Kind of stupid but that was it.

DTS-example

I found this useful:
http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.
|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/...ortxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/...ortxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> > I found this useful:
> > http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> >
> > But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> > work.Got the error message:
> > 'Can't create object: MSXML2.DOMDocument.4.0'
> >
> > /Kent J,
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTSDestination - equivalent in SSIS?

Hi,

I am converting an ActiveX script. I am looking to replace the functionality of this statement:

If DTSDestination("CartoonCharacter") = "Felix" Then ...

So, it's checking the value in the TABLE, not the file.

I need to rewrite this for SSIS. What is the recommended approach? Is there a built-in task that will do this and how?

Thanks much

You'd use a lookup component to query the destination and then later use a derived column or a conditional split transformation to compare the lookup result with the source value (or a hardcoded value, if you wish)

DTS-copy without dropping indexes?

When copying all tables of a database from a server to another using DTS, is it crucial to drop the indexes before copying?
If "just" a performance issue, is there any other way to postpone index updating until the DTS has finished copying?This depends on the type of data transfer you choose.

if you choose to append records to destination tables it's not necessary to drop indexes.

if you choose to drop tables the correlated indexes are dropped too.

if you choose to copy objects from one DB to another usually existing objects (tables and indexes ) are dropped.

If indexes exist it's not possible to stop index updating, I think.

bye|||What I intended to do was to copy a database from one instance to another this way:

- 'alter table nocheck constraint all' on every table
- 'truncate table' on every table, to make sure that no data from before DTS-copying can be left.
- DTS (which replaces data, not appends)
- 'alter table check constraint all' on every table

However, I now realized Truncate Table requires the Foreign Keys to be dropped, disabling them through Nocheck is insufficient.

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either way
It seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either wayIt seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

DTSConfig SourceSafe

When adding a SSIS DTSX package solution to Sourcesafe the SSIS Package Configuration file (.DTSConfig) is not included. We are using sourcesafe version 6.0d and Visual Studio 2005 Team Edition for Developers.It has to be added manually.|||Thanx for your feedback. I understood that. I don't agree with the fact that it shouldn't be automated. Think of a multi property config file with 100 or so lines. I realize that the configuration is machine specific, but would not want to reenter those 100 lines if a system was lossed.|||You should file a bug report on Microsoft Connect (or join an existing one - there was another post about this a while back that may have resulted in a bug report). I tend to agree with you, I'd like to have the option to include it automatically. Just not sure how you would work around the machine specific nature of the configurations.

DTSConfig SourceSafe

dtsconfig file is binary?

Hello all,

I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.

Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?

Any suggestions GREATLY appreciated.

I won't go into the reason I have my servername in 80 places...

Andy

The .dtsconfig files are XML files - i.e. mostly text, but sometimes you need to be careful with the encoding (typically utf-8) to keep it valid XML. I have no experience with WinGrep, or why it thinks it is "binary". I would suggest using XML-aware tools to modify .dtsconfig.|||

Thanks for the tip Michael. Do you know if notepad is XML-aware? Because that's the only tool I used to edit them.

Also, I tried just creating a clean .dtsconfig file from the package configurations wizard and then pointing WinGrep at that file and I got the same thing, saying it was binary. I didn't modify it at all outside of the wizard.

|||The .dtsConfig file is a text file, in UTF-8 encoding. If you don't configure any localized (non-English) properties, UTF-8 is identical to ASCII encoding - in that case any text processor can work with it. Notepad can typically detect the encoding correctly even if you have non-English properties, so it should be OK in any case.

Again, I have no idea why WinGrep thinks it is binary, maybe because it is just one line.|||

Hi Michael,

You hit the nail on the head with that last sentence. I just put a carriage return in the file so it was more than 1 line and now WinGrep doesn't think it's binary anymore. Weird. Anyway to make it so that a configuration file is created on more than 1 line?

|||

Ok, so in order to make it so it's not just 1 line, in BI studio, with your config file open in then center pane and your cursor inside the file, go to Edit/Advanced/Format Document. This will format the document better as well as put it on more than 1 line. Now WinGrep has no problem.

DTS-Conection with sybase 12.5

Good day.I have dts running every 10 minutes in a server of slqserver 2000 who is in a country and that it sends data to another country which has a data base sybase12.5.The problem is when by reasons for the WAN the connection fails from a country to another one the dts or job fails executing its processes and I must soon give click to stop and start so that it continues. That I can do so that when the connection is lost and i need to dts reconected automatically and is continued its processing?
thanks and sorry my english :)The best answer that I've found for problems like this is to establish a "watchdog" job. The watchdog runs very frequently (possibly every minute) and checks for evidence of the main job being interrupted... If the watchdog finds that evidence, then the watchdog restarts the main job.

In your example, I would suggest that you create a simple table to maintain both a log of events and to allow detection of the main job failing. Add a step before the main job to insert a row showing the DATETIME that it started. Add a step after the main job completes to show that it ended. This effectively logs the activities of the main job.

Create a whole new watchdog job that logs its beginning and end exactly the same way as you just added to the main job, then checks to be sure that the last entry from the main job is either a "success" or a start that could still be reasonably running (in other words, that the end for this job isn't overdue yet). If the main job needs to be restarted, let the watchdog job start it, otherwise let the watchdog end gracefully.

You should periodically check the log table to see when the watchdog runs, and how often it needs to restart the main job. This can be useful information, and will often warn you of trouble before the trouble becomes critical.

-PatP

DtsBackup2000 for SSIS packages?

hi everyone,

Up to moment I've been using Dtsbackup2000 for do backup for our DTS but now, which let's allow do the same with dtsx?

TIA

I don't realize and I've deleted a package. If I would have a backup I could restore it as in old times by dtb/dts|||

If its a .dtsx file then a file system backup will do the job.

-Jamie

|||you're right

DtsBackup 2000 for SSIS?

Hi everyone,

Day in day out I use this application in order to generate dts-backups but I wonder how do the same with our dear SSIS packages?

Jamie, any idea?

TIA

This silence means that there will be to built an application for that?|||

The silence meant I was doing some work!

I had no plans to produce a 2005 version. The way we work with SSIS, developing with local storage not on the server means we should always have copies. The easy integration with source control systems as well as the inability to edit packages in server storage just means that I cannot see the need for such tools anymore.

|||

Hi,

I'm joking. That's fine by me.

dts2000 migration, activex script and odbc connections help needed

Apologies if this has been covered before or if i am in the wrong place.

I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS package that connects to an AS400 to retrieve teh record count of a table for integrity checking. Under SQL server 2000 this worked fine but in SSIS it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server 05 on windows 2003. The odbc driver for the as400 has been installed also.

The activex script is as follows:

dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string

set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%

'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"

set rs1 = cn.execute (insertstr1)

'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function

I have since changed MSDASQL to SQLNCLI but (after errors re ad hoc distributed queries which i have enabled) i now get errors re linked servers:

ole db provider "sqlncli" for linked server "(null)" returned message "invalid connection string attrbute"

Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks

Chris

I am contacting the SSIS team but I suspect that you need to run the 32-bit version of SSIS since - as you pointed out - the ODBC driver is a 32-bit DLL, somehwat similar to the case discussed at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1.

|||

I confirmed: if there is only 32-bit ODBC driver, you need to run the package using 32-bit runtime.

DTS2000 error

Hi
When I try to use DTS 2000 Packages in SQL2005 CTP I obtain this error:

Could not load file or assembly 'Microsoft.SqlServer.Dts80, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Impossibile trovare il file specificato. (Microsoft.SqlServer.DtsObjectExplorerUI)

Anyone can help me?

ThanksWhich CTP version are you using?

Look in your DTS\binn folder (by default this is at "C:\program files\Microsoft SQL Server\90\DTS\Binn") and see if there are any files of the names "Microsoft.SQLServer.DTS8*.dll"

I don't have "Microsoft.SqlServer.Dts80.dll" but I do have "Microsoft.SQLServer.DTS8HelperObjectModel.dll", "Microsoft.SQLServer.DTS8HelperUtility.dll" & "Microsoft.SQLServer.Exec80PackageUtil.dll"

and in "C:\program files\Microsoft SQL Server\90\DTS\Tasks" I have "Microsoft.SQLServer.Exec80PackageTask.dll".

Do you have these files, or similarly named files, on your system?

I am on the June CTP by the way. All of these files are version number 9.0.1187.0 which leads me to think that you may be on an earlier version.
You have the choice of whether to install the DTS2000 runtime or not when you install SSIS. Do you remember if you chose to install it?

-Jamie

Sunday, February 26, 2012

Blog Archive

About Me