DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql

DTS Scheduling Issue

I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.

I have checked the Agent and it says the job is executing, however, it seems to have hung.

The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.

How do I check this? or find out what is causing it to hang?

Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,

Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge

I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.

Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?

I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see

"Executed as user: SYD\blah" etc|||Thanks!

Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?

Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"

upalsen:

Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.

OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."

So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"

Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?

Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.

DTS scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >

DTS runs manually, fails as scheduled job

I've mined countless threads all over the Net where people are having my problem, but none of the solutions I've seen seem to apply.

When I run a DTS package from Enterprise Manager, everything works great. When I schedule the same package as a job, it fails immediately and reports this error:

Executed as user: SERVER-NAME\SYSTEM. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.

My hunch is the SERVER-NAME\SYSTEM.DTSRun account doesn't have sufficient privileges, but I can't find how/where to fix that.

Help, please.

Thanks,

Doug

Checkout the following links

http://www.sqlmag.com/articles/index.cfm?articleid=46723& - Security in SSIS
http://support.microsoft.com/?kbid=918760 - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

Thanks,
Loonysan

|||

"Invalid class string" is not strictly a SQL Server error in DTS. Essentially it means in cannot find the CLSID for a required object when reading the package. This error can also occur when loading a package that contains a CustomTask which has not been registered on the current machine. (http://www.databasejournal.com/features/mssql/article.php/1461391)

PRB: "Invalid class string" error message when opening DTS package
(http://support.microsoft.com/?scid=kb%3Ben-us%3B288910&x=15&y=16)

The top two hits I got from Google, both say pretty much the same thing. Something is missing from your server, which causes the "Invalid class string", or "I cannot find your task".

FYI, general DTS info on jobs-

INF: How to Run a DTS Package as a Scheduled Job
(http://support.microsoft.com/kb/269074/en-us)

DTS runs great... but not from the job list

I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Dts Run Problem

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

My ExecuteDts Function is :

public static void ExecuteDTS(string PackageName,params object[] DtsVariableValues) {string ServerName = ConnectionNames.OlapServerName;string ServerUserName = ConnectionNames.OlapUserName;string ServerPassword = ConnectionNames.OlapPassword;object pVarPersistStgOfHost =null; DTS.PackageClass DtsPackage =new DTS.PackageClass(); DtsPackage.LoadFromSQLServer(ServerName, ServerUserName, ServerPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default ,null,null,null, PackageName,ref pVarPersistStgOfHost);try {string[] DtsVariableNames =new String[DtsPackage.GlobalVariables.Count];int VariableIndex = 0; DtsPackage.UseTransaction =true;foreach (GlobalVariable globalin DtsPackage.GlobalVariables) { DtsVariableNames[VariableIndex] = global.Name; DtsPackage.GlobalVariables.Remove(global.Name); VariableIndex++; } VariableIndex = 0;foreach (String DtsVariableNamein DtsVariableNames) { DtsPackage.GlobalVariables.AddGlobalVariable(DtsVariableName, DtsVariableValues.GetValue(VariableIndex)); VariableIndex++; } DtsPackage.Execute(); }catch (Exception ex) { //Console.WriteLine(ex.Message); }finally { DtsPackage.UnInitialize(); DtsPackage =null; } }

Any idea?

Regards..

sourvil:

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

Can you give us more details about the two machines you are using?|||

My 2 dts are on the same machine, on the same sql server.

Hmm, i think also it would be permission issue but i could not solve it yet?

|||

I think if you give the DTC creater local admin previleges will solve the issue.

Good luck.

|||

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

|||

sourvil:

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

I think you typed the password incorrectly or Did you forget the sa password? (Try to login on the SQL Server, will you will be able to login successfully?).

Good luck..

|||

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

|||

sourvil:

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

Check out these links:
http://www.computerperformance.co.uk/Logon/code/code_80004005.htm
http://www.lazydba.com/sql/1__7259.html
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html

Good luck.

|||

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

|||

sourvil:

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

I guess it related to a permission on the folder where the filer is reside.
Make sure ASPNET user has the needed permissions on that folder (maybe this is the why its fail not sure).

Good luck.

|||

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

|||

sourvil:

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

Your are welcomesourvil.

Suggestion (will not solve the problem but will help you in the approach you decided to go with):

Have two DTS, one is the real one (has all the logic and transdormations) and the other have a task to run the first one.

This will help you a lot I guess,

Good luck.

DTS run from .NET

My dts package has to pick up a file which is in a mapped drive of the
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.

Joel Scavone|||I did use a full netwok path and even did add identity impersonate in
the web.config... but does not help..

The package runs fine when I register the sql server my machine and run
the package..

Sigh! Don't know what wrong

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTS question

I have package that Extracts a zip file. Now the zip file is updated bi-weekly between 9-11am. So the DTS package is schedule to run at 9am. Once the zip is extracted it is renamed (different from original).

But before extraction, i have a few tasks that check whether the zip exists or not.

PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.

This polling for the file is creating a alot of problems for me.

Any help/advise would be appreciated.

ThanksHello!

I assume that you have an ActiveXTask as one of the first steps in that DTS Package, that checks the existence of the ZIP-File.

If you make this task loop until it finds the ZIP-File, that would be the first step. In this loop there should be a command that calls a VBScript with something like "WScript.Sleep 600000" (which should stop the script from continuing for 10 minutes!).

Hope that gave you some hints!

Greetings,

Carsten

Originally posted by vmlal
I have package that Extracts a zip file. Now the zip file is updated bi-weekly between 9-11am. So the DTS package is schedule to run at 9am. Once the zip is extracted it is renamed (different from original).

But before extraction, i have a few tasks that check whether the zip exists or not.

PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.

This polling for the file is creating a alot of problems for me.

Any help/advise would be appreciated.

Thanks

DTS question

Say I would use a ActiveX vbScript to query an LDAP server.

If i then would use that script to run in a DTS package. Witch steps do I have to take? Do i need to make a connection with the database, how do I create links.

I not looking for an out of the box solution, just maybe some good links to good sites witch explain this problem / question. My search wasn't that successfull

Greetings,
GodofredoYour DTS may contain nothing but an ActiveX script. You create connections inside the script using ADO, RDO, or whatever else you think you can use for it.|||yes but i want to fill up a table with the information retrieved by the ActiveX script|||you could just create a job that runs your activex script by querying ldap
and you could make that available to the next available step in the job. you dont really have to run dts. BUt if you want to, SQL Server has access to the ADSI oledb provider . in other words you could link a server to active directory and run a query based on your appropriate LDAP architecture.|||This is what a call a straightforward answer

Thanks,
Godofredo|||Hey, that's because he teaches this stuff...

Scott does DTS and LDAP...|||"at the same time baby!!!!"

is this thing on ?? 1.. 2.. 3 ...sqlsql

DTS Problem

Hi,
When i create a DTS and schedule it, the DTS don't run, but if i run this
DTS using the SQL Enterprise manager, everything works fine! Is it a
permission problem ? A Scheduled DTS must have a specific permission ?
Thanks in advance,
VictorDid you start SQL Server Agent ?
Jens Suessmeyer.
"Victor Pereira" <taintmode@.yahoo.com.br> schrieb im Newsbeitrag
news:eLIohxRRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>|||From BOL
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the
DTS Run utility, or from the command prompt executes under the security
context of the user who is currently logged in. However, a package scheduled
for execution runs under the security context of the SQL Server Agent job
that runs the package.
"Victor Pereira" wrote:

> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>
>|||My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
Agent scheduled jobs run on the SQL Server, while interactive ones execute
on the same system on which they were launched. So, when i run the
interactive job, i read the local C:\foobar.csv and everyting works! But
scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
file won't exist so, the process fail! Am i right ?
Thanks in advance,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> From BOL
> Usually, a package run from DTS Designer, the DTS Import/Export Wizard,
the
> DTS Run utility, or from the command prompt executes under the security
> context of the user who is currently logged in. However, a package
scheduled[vbcol=seagreen]
> for execution runs under the security context of the SQL Server Agent job
> that runs the package.
>
> "Victor Pereira" wrote:
>
this[vbcol=seagreen]|||Yes. If you need to read a remote file, make share on remote location, set
appropriate permission for SQL Server Agent Service and in your DTS specify
UNC name for that file (\\Server\Share\foobar.csv)
Hope this help
"Victor Pereira" wrote:

> My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
> Agent scheduled jobs run on the SQL Server, while interactive ones execute
> on the same system on which they were launched. So, when i run the
> interactive job, i read the local C:\foobar.csv and everyting works! But
> scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
> file won't exist so, the process fail! Am i right ?
> Thanks in advance,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> the
> scheduled
> this
>
>|||Hi Ana,
But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
scheduled job will try to open the local C:\foobar.csv right ?
Thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> Yes. If you need to read a remote file, make share on remote location, set
> appropriate permission for SQL Server Agent Service and in your DTS
specify[vbcol=seagreen]
> UNC name for that file (\\Server\Share\foobar.csv)
> Hope this help
> "Victor Pereira" wrote:
>
Server[vbcol=seagreen]
execute[vbcol=seagreen]
this[vbcol=seagreen]
Wizard,[vbcol=seagreen]
security[vbcol=seagreen]
job[vbcol=seagreen]
?[vbcol=seagreen]|||If your DTS is saved on SQL server on your local machine, yes. But if your
DTS is on remote server, then your DTS will run on remote machine and with
account specified in dtsrun.exe command.
"Victor Pereira" wrote:

> Hi Ana,
> But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
> scheduled job will try to open the local C:\foobar.csv right ?
> Thanks,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> specify
> Server
> execute
> this
> Wizard,
> security
> job
> ?
>
>|||Ana, thanks for your help, i solved this "misterious" problem, god bless you
;-)
thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:2A1013F8-EEE1-4EA7-98B2-40FE38ACA849@.microsoft.com...
> If your DTS is saved on SQL server on your local machine, yes. But if your
> DTS is on remote server, then your DTS will run on remote machine and
with[vbcol=seagreen]
> account specified in dtsrun.exe command.
> "Victor Pereira" wrote:
>
my[vbcol=seagreen]
set[vbcol=seagreen]
But[vbcol=seagreen]
but[vbcol=seagreen]
Agent[vbcol=seagreen]
run[vbcol=seagreen]
it a[vbcol=seagreen]
permission[vbcol=seagreen]

DTS Problem

Hi,
When i create a DTS and schedule it, the DTS don't run, but if i run this
DTS using the SQL Enterprise manager, everything works fine! Is it a
permission problem ? A Scheduled DTS must have a specific permission ?
Thanks in advance,
Victor
Did you start SQL Server Agent ?
Jens Suessmeyer.
"Victor Pereira" <taintmode@.yahoo.com.br> schrieb im Newsbeitrag
news:eLIohxRRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>
|||From BOL
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the
DTS Run utility, or from the command prompt executes under the security
context of the user who is currently logged in. However, a package scheduled
for execution runs under the security context of the SQL Server Agent job
that runs the package.
"Victor Pereira" wrote:

> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>
>
|||My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
Agent scheduled jobs run on the SQL Server, while interactive ones execute
on the same system on which they were launched. So, when i run the
interactive job, i read the local C:\foobar.csv and everyting works! But
scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
file won't exist so, the process fail! Am i right ?
Thanks in advance,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> From BOL
> Usually, a package run from DTS Designer, the DTS Import/Export Wizard,
the
> DTS Run utility, or from the command prompt executes under the security
> context of the user who is currently logged in. However, a package
scheduled[vbcol=seagreen]
> for execution runs under the security context of the SQL Server Agent job
> that runs the package.
>
> "Victor Pereira" wrote:
this[vbcol=seagreen]
|||Yes. If you need to read a remote file, make share on remote location, set
appropriate permission for SQL Server Agent Service and in your DTS specify
UNC name for that file (\\Server\Share\foobar.csv)
Hope this help
"Victor Pereira" wrote:

> My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
> Agent scheduled jobs run on the SQL Server, while interactive ones execute
> on the same system on which they were launched. So, when i run the
> interactive job, i read the local C:\foobar.csv and everyting works! But
> scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
> file won't exist so, the process fail! Am i right ?
> Thanks in advance,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> the
> scheduled
> this
>
>
|||Hi Ana,
But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
scheduled job will try to open the local C:\foobar.csv right ?
Thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> Yes. If you need to read a remote file, make share on remote location, set
> appropriate permission for SQL Server Agent Service and in your DTS
specify[vbcol=seagreen]
> UNC name for that file (\\Server\Share\foobar.csv)
> Hope this help
> "Victor Pereira" wrote:
Server[vbcol=seagreen]
execute[vbcol=seagreen]
this[vbcol=seagreen]
Wizard,[vbcol=seagreen]
security[vbcol=seagreen]
job[vbcol=seagreen]
?[vbcol=seagreen]
|||If your DTS is saved on SQL server on your local machine, yes. But if your
DTS is on remote server, then your DTS will run on remote machine and with
account specified in dtsrun.exe command.
"Victor Pereira" wrote:

> Hi Ana,
> But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
> scheduled job will try to open the local C:\foobar.csv right ?
> Thanks,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> specify
> Server
> execute
> this
> Wizard,
> security
> job
> ?
>
>
|||Ana, thanks for your help, i solved this "misterious" problem, god bless you
;-)
thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:2A1013F8-EEE1-4EA7-98B2-40FE38ACA849@.microsoft.com...
> If your DTS is saved on SQL server on your local machine, yes. But if your
> DTS is on remote server, then your DTS will run on remote machine and
with[vbcol=seagreen]
> account specified in dtsrun.exe command.
> "Victor Pereira" wrote:
my[vbcol=seagreen]
set[vbcol=seagreen]
But[vbcol=seagreen]
but[vbcol=seagreen]
Agent[vbcol=seagreen]
run[vbcol=seagreen]
it a[vbcol=seagreen]
permission[vbcol=seagreen]

DTS Problem

Hi,
When i create a DTS and schedule it, the DTS don't run, but if i run this
DTS using the SQL Enterprise manager, everything works fine! Is it a
permission problem ? A Scheduled DTS must have a specific permission ?
Thanks in advance,
VictorDid you start SQL Server Agent ?
Jens Suessmeyer.
"Victor Pereira" <taintmode@.yahoo.com.br> schrieb im Newsbeitrag
news:eLIohxRRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>|||From BOL
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the
DTS Run utility, or from the command prompt executes under the security
context of the user who is currently logged in. However, a package scheduled
for execution runs under the security context of the SQL Server Agent job
that runs the package.
"Victor Pereira" wrote:
> Hi,
> When i create a DTS and schedule it, the DTS don't run, but if i run this
> DTS using the SQL Enterprise manager, everything works fine! Is it a
> permission problem ? A Scheduled DTS must have a specific permission ?
> Thanks in advance,
> Victor
>
>|||My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
Agent scheduled jobs run on the SQL Server, while interactive ones execute
on the same system on which they were launched. So, when i run the
interactive job, i read the local C:\foobar.csv and everyting works! But
scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
file won't exist so, the process fail! Am i right ?
Thanks in advance,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> From BOL
> Usually, a package run from DTS Designer, the DTS Import/Export Wizard,
the
> DTS Run utility, or from the command prompt executes under the security
> context of the user who is currently logged in. However, a package
scheduled
> for execution runs under the security context of the SQL Server Agent job
> that runs the package.
>
> "Victor Pereira" wrote:
> > Hi,
> >
> > When i create a DTS and schedule it, the DTS don't run, but if i run
this
> > DTS using the SQL Enterprise manager, everything works fine! Is it a
> > permission problem ? A Scheduled DTS must have a specific permission ?
> >
> > Thanks in advance,
> >
> > Victor
> >
> >
> >|||Yes. If you need to read a remote file, make share on remote location, set
appropriate permission for SQL Server Agent Service and in your DTS specify
UNC name for that file (\\Server\Share\foobar.csv)
Hope this help
"Victor Pereira" wrote:
> My DTS read a file C:\foobar.csv and import it into SQL Server. SQL Server
> Agent scheduled jobs run on the SQL Server, while interactive ones execute
> on the same system on which they were launched. So, when i run the
> interactive job, i read the local C:\foobar.csv and everyting works! But
> scheduled job will try to open a C:\foobar.csv at the SQL Server, but this
> file won't exist so, the process fail! Am i right ?
> Thanks in advance,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> > From BOL
> > Usually, a package run from DTS Designer, the DTS Import/Export Wizard,
> the
> > DTS Run utility, or from the command prompt executes under the security
> > context of the user who is currently logged in. However, a package
> scheduled
> > for execution runs under the security context of the SQL Server Agent job
> > that runs the package.
> >
> >
> > "Victor Pereira" wrote:
> >
> > > Hi,
> > >
> > > When i create a DTS and schedule it, the DTS don't run, but if i run
> this
> > > DTS using the SQL Enterprise manager, everything works fine! Is it a
> > > permission problem ? A Scheduled DTS must have a specific permission ?
> > >
> > > Thanks in advance,
> > >
> > > Victor
> > >
> > >
> > >
>
>|||Hi Ana,
But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
scheduled job will try to open the local C:\foobar.csv right ?
Thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> Yes. If you need to read a remote file, make share on remote location, set
> appropriate permission for SQL Server Agent Service and in your DTS
specify
> UNC name for that file (\\Server\Share\foobar.csv)
> Hope this help
> "Victor Pereira" wrote:
> > My DTS read a file C:\foobar.csv and import it into SQL Server. SQL
Server
> > Agent scheduled jobs run on the SQL Server, while interactive ones
execute
> > on the same system on which they were launched. So, when i run the
> > interactive job, i read the local C:\foobar.csv and everyting works! But
> > scheduled job will try to open a C:\foobar.csv at the SQL Server, but
this
> > file won't exist so, the process fail! Am i right ?
> >
> > Thanks in advance,
> > Victor
> > "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> > news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> > > From BOL
> > > Usually, a package run from DTS Designer, the DTS Import/Export
Wizard,
> > the
> > > DTS Run utility, or from the command prompt executes under the
security
> > > context of the user who is currently logged in. However, a package
> > scheduled
> > > for execution runs under the security context of the SQL Server Agent
job
> > > that runs the package.
> > >
> > >
> > > "Victor Pereira" wrote:
> > >
> > > > Hi,
> > > >
> > > > When i create a DTS and schedule it, the DTS don't run, but if i run
> > this
> > > > DTS using the SQL Enterprise manager, everything works fine! Is it a
> > > > permission problem ? A Scheduled DTS must have a specific permission
?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Victor
> > > >
> > > >
> > > >
> >
> >
> >|||If your DTS is saved on SQL server on your local machine, yes. But if your
DTS is on remote server, then your DTS will run on remote machine and with
account specified in dtsrun.exe command.
"Victor Pereira" wrote:
> Hi Ana,
> But if i schedule at my local machine using (at.exe + dtsrun.exe) then my
> scheduled job will try to open the local C:\foobar.csv right ?
> Thanks,
> Victor
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> > Yes. If you need to read a remote file, make share on remote location, set
> > appropriate permission for SQL Server Agent Service and in your DTS
> specify
> > UNC name for that file (\\Server\Share\foobar.csv)
> >
> > Hope this help
> >
> > "Victor Pereira" wrote:
> >
> > > My DTS read a file C:\foobar.csv and import it into SQL Server. SQL
> Server
> > > Agent scheduled jobs run on the SQL Server, while interactive ones
> execute
> > > on the same system on which they were launched. So, when i run the
> > > interactive job, i read the local C:\foobar.csv and everyting works! But
> > > scheduled job will try to open a C:\foobar.csv at the SQL Server, but
> this
> > > file won't exist so, the process fail! Am i right ?
> > >
> > > Thanks in advance,
> > > Victor
> > > "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> > > news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> > > > From BOL
> > > > Usually, a package run from DTS Designer, the DTS Import/Export
> Wizard,
> > > the
> > > > DTS Run utility, or from the command prompt executes under the
> security
> > > > context of the user who is currently logged in. However, a package
> > > scheduled
> > > > for execution runs under the security context of the SQL Server Agent
> job
> > > > that runs the package.
> > > >
> > > >
> > > > "Victor Pereira" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > When i create a DTS and schedule it, the DTS don't run, but if i run
> > > this
> > > > > DTS using the SQL Enterprise manager, everything works fine! Is it a
> > > > > permission problem ? A Scheduled DTS must have a specific permission
> ?
> > > > >
> > > > > Thanks in advance,
> > > > >
> > > > > Victor
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Ana, thanks for your help, i solved this "misterious" problem, god bless you
;-)
thanks,
Victor
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:2A1013F8-EEE1-4EA7-98B2-40FE38ACA849@.microsoft.com...
> If your DTS is saved on SQL server on your local machine, yes. But if your
> DTS is on remote server, then your DTS will run on remote machine and
with
> account specified in dtsrun.exe command.
> "Victor Pereira" wrote:
> > Hi Ana,
> >
> > But if i schedule at my local machine using (at.exe + dtsrun.exe) then
my
> > scheduled job will try to open the local C:\foobar.csv right ?
> >
> > Thanks,
> >
> > Victor
> > "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> > news:78E6E68E-4267-4C6F-8657-0B10DDCC3974@.microsoft.com...
> > > Yes. If you need to read a remote file, make share on remote location,
set
> > > appropriate permission for SQL Server Agent Service and in your DTS
> > specify
> > > UNC name for that file (\\Server\Share\foobar.csv)
> > >
> > > Hope this help
> > >
> > > "Victor Pereira" wrote:
> > >
> > > > My DTS read a file C:\foobar.csv and import it into SQL Server. SQL
> > Server
> > > > Agent scheduled jobs run on the SQL Server, while interactive ones
> > execute
> > > > on the same system on which they were launched. So, when i run the
> > > > interactive job, i read the local C:\foobar.csv and everyting works!
But
> > > > scheduled job will try to open a C:\foobar.csv at the SQL Server,
but
> > this
> > > > file won't exist so, the process fail! Am i right ?
> > > >
> > > > Thanks in advance,
> > > > Victor
> > > > "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> > > > news:A20074DA-3522-406D-87EA-B96655E34A8A@.microsoft.com...
> > > > > From BOL
> > > > > Usually, a package run from DTS Designer, the DTS Import/Export
> > Wizard,
> > > > the
> > > > > DTS Run utility, or from the command prompt executes under the
> > security
> > > > > context of the user who is currently logged in. However, a package
> > > > scheduled
> > > > > for execution runs under the security context of the SQL Server
Agent
> > job
> > > > > that runs the package.
> > > > >
> > > > >
> > > > > "Victor Pereira" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > When i create a DTS and schedule it, the DTS don't run, but if i
run
> > > > this
> > > > > > DTS using the SQL Enterprise manager, everything works fine! Is
it a
> > > > > > permission problem ? A Scheduled DTS must have a specific
permission
> > ?
> > > > > >
> > > > > > Thanks in advance,
> > > > > >
> > > > > > Victor
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >sqlsql

DTS permission

I created a DTS package to extract data to a text file.
I want to create a login for a user who should be able ONLY to run this DTS. I don't want to give him/her any access to any database .
Is that possible?
Thanks in advance
GiorgioWithout permission on database user cannot complete the execution of DTS package.|||I find out a workaround:

1) I created a user that has permission only on the sample DB Northwind.

2) The DTS package is protected by "DTS Owner password" and DTS User password

3) The "Connection" in the DTS package has inside a username and password that has acess to the appropriate DB.

With this workaround I obtained that:

1) The user has access only to the Northwind DB
2) The user can run only the DTS package (because is protected with DTS User password).

Thanks anyway to everyone who replied to me!|||Then my reply was intended to light when you said no permission to any database.

dts packages take a long time to run

Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:

>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>

dts packages take a long time to run

Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:

>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>

DTS Packages in SQL 2005 - what are they now, etc

OK - so it looks like DTS Packages are "legacy". I installed the component to be able to edit and run them from SQL 2005 Management console and that seems to work fine. (I haven't acually run my packages yet, but at least I can open and edit the dang things). And I also migrated the existing ones from the networked server on which they lived (which is still running SQL 2000) to my local server (which is the one on 2005).

So assuming I want to do the new methodology - what IS the new methodology?

Where are the migrated packages? I looked at all of the links in the SQL Management Console for anything that references these newly migrate packages, and I don't see them - or anything that looks like them - anywhere.

What do I run them in - from what, how, etc? This really isn't clear at all.OK - so I think I have this figured out. You have to connect to Integration Services for a specific server. If you do that, they are then in the Stored Packages. So I'll post back if I have any problems when I actually get up the courage to try and run these puppies...|||That's correct. If you used the destination as a server, you have to connect to Integration Services and then see the packages under MSDB in stored packages. Alternatively, you can choose "DTSX File" as the destination for your migrated packages, and then save your packages as .dtsx files in your chosen folder. You can then add these packages to an SSIS Project and view/edit them in BI Studio.|||Hi.
Yeah, they are unregular XML-files, I guess. Not to be edited.

Try to enable and start the "DTS Server" service, then run
cmd
dtexec.exe /?

They can also be scheduled from within managment studio and run from bi studio.

dts package?

I have a DTS package schedule to run once each day and the package is save in Enterprise Manager. Does Enterprise Manager have to be loaded for the DTS package to run on schedule? Thanks.EM is just an application interface to sql server.

sql server stores the jobs and dts packages in the master database.

sql server agent takes care of the execution of all your jobs, and knows where to find your "stuff"

The is service the is constantly running

Anyone know how the internals of this works.|||To answer the original question, no SQL Enterprise Manager does not need to be running for a scheduled package to run. SQL Agent needs to be running on the server.

The DTS packages and the Job scheduling information are actually stored in the MSDB database, not in master.

While I don't know nearly as much as I'd like to, I know enough to get both of us into major trouble without even breaking a sweat!

-PatP|||msdb, huh...

Hell, failed that interview...

Had a dba here who wanted to "clean up" his server and get rid of un-needed databases...

Northwind
Pubs
model
msdb
master

scrub...

He didn't last very long...|||How do I get slq agent running? I keep getting that error that sqlagent is not running.

Thanks|||The easy way is to start the SQL Service Manager, select the correct server in the top drop down box, select SQL Server Agent in the lower box, then click the button with the green "start" triangle.

-PatP|||I have heard of some places that will drop pubs and Northwind. I never do. Too useful for testing stuff.

As for the DTS internals, I will take an ill-advised stab at explaining it all.

When you create a new DTS package, you are really creating a VB script which will do all the things that you told (not necessarily wanted) the DTS package to do. Technically, you could write a VB script in notepad to run a DTS package, but I already tried it. It stank.

The generated script is stored by default in the msdb database (sysdtspackages), but can be stored as a VB script, a proprietary DTS formatted file, or in the SQL Server metadata repository (have not touched that, myself).
A VB script version could be run on any windows machine, but will promptly choke, if it can not find the .dlls in order to get all the fancy functions it needs.

Actual package formatted DTS packages (from msdb, Metadata, or the proprietary file format) can all be run by the dtsrun.exe utility. This is what SQL Agent calls, when you schedule a package to run. It also happens to be the same executable that Enterprise Manager calls to run a package for you on your laptop. This is where the client dependencies start. If you have a SQL 2000 Enterprise Manager, you can run your package locally no problem. When you try to run the "same" package via SQL Agent on a SQL 7.0 server, you get nothing but errors. This is because you have asked the script to created with one library, and run with a separate library, and your package has only one library card (sorry, it is past 5:00).

So, now that you are no doubt utterly confused, the answer is "No, Enterprise Manager does not have to be running, but SQLAgent has to be running (in order to spawn the process), the dtsrun.exe utility has to be there (it is for any SQL Server install), and the dts*.dlls have to be there, and be the right version." Clear as mud?|||Thanks everyone for the help. I tried it and it's working just how I want it too. Thanks agian.