DTS Import: schedule

Showing posts with label schedule. Show all posts

Thursday, March 29, 2012

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 scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.com
In step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***

dts scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn'
t
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***

dts scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
--
Message posted via http://www.sqlmonster.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***

DTS Scheduled Time

How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks

DTS schedule job fails urgent pls help!

Hi all,

We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.

DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.

TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.

-PatP

DTS schedule and oracle

Here's a weird problem. I've read everything I could find about permissions and scheduling a DTS but I'm still having problems. I've created a DTS on my production server (Windows 2000 Server) and it runs fine when I'm running it on the server. (I'm at the machine) When I schedule it, it starts to run and then it just hangs on step one. I tried removing steps from my dts to see which one is causing the problem and through process of elimination I found it was the step that connects to my oracle server. When I run the DTS manually I am logged in as SJKAdmin and the SQLServer Agent account is SQLAdmin. Both users are local administrators on both the production server and the server where the oracle DB is. I just can't figure it out. The real kicker is that I have a development server set up that's supposed to be identical to the production server and both the DTS (when run manually) and the scheduled jobs run without any problem. Any help would be greatly appreciated.

BrettHave you logged into the production server (@. the prodution server) as SQLAdmin and gone into Oracles Net Manager and tested the connection?

DTS schedule

I have 2 DTS and i want to make sure that DTS2 runs after the DTS1 has finished, how can i do this??
Thanks,
Joo MartinsCan you run dts2 within dts1 or does it have to be run outside of dts1 ?|||dts2 must run outside dts1|||When you schedule a DTS package using EM, it creates a job with the name same as the package. This job has only one step - with the name same as the package. However, you can add more steps to the job and specify the sequence in which they will run. The job will be under EM - Server - Management - SQL Server agent - Jobs.sql sql

Tuesday, March 27, 2012

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

Sunday, March 25, 2012

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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >sql sql

DTS permissions and ownership

We have a DTS package developed on our development PC's (SQL 7). It
runs fine. When we schedule it on the server (SQL 7), it fails. We
have been able to find that this is a known issue down to the use of
permissions (running it uses the users permissions, scheduling it uses
the servers). http://support.microsoft.com/?kbid=269074

Our Ops Support team insist that we have the full permissions to run
this, but it still fails. If we log on to the server as the service
and re-create (sample) the DTS package, we can schedule it. However,
we don't want to have to go the the comms room (or use VNC) to develop
DTS packages. If we then change this on our development PC's we get
the same problem as we appear to have taken ownership. It looks like
ownership is not just on the DTS package, but on the components as
well.

Is there are way of changing the ownership of the DTS package /
components ?

What can I check with regards permissions ?

Any help would be appreciated.

Thanks

RyanManaged to find the answer. It was down to a different version of
MDAC.
Ver 2.5 was on the server and 2.6 was on the development PC's. With
the connection properties, additional information is passed with 2.6
which causes a problem with 2.5

When executing the DTS package, it uses the current PC's version. When
scheduled, it uses the version on the server.

The way around this was to re-do the connection properties while
logged on the server (using VNC) and then save it.

Now we know what needs doing, we'll get all of the PC's & servers
checked and put on the same version.

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404190132.4438ecc2@.posting.google.com>...
> We have a DTS package developed on our development PC's (SQL 7). It
> runs fine. When we schedule it on the server (SQL 7), it fails. We
> have been able to find that this is a known issue down to the use of
> permissions (running it uses the users permissions, scheduling it uses
> the servers). http://support.microsoft.com/?kbid=269074
> Our Ops Support team insist that we have the full permissions to run
> this, but it still fails. If we log on to the server as the service
> and re-create (sample) the DTS package, we can schedule it. However,
> we don't want to have to go the the comms room (or use VNC) to develop
> DTS packages. If we then change this on our development PC's we get
> the same problem as we appear to have taken ownership. It looks like
> ownership is not just on the DTS package, but on the components as
> well.
> Is there are way of changing the ownership of the DTS package /
> components ?
> What can I check with regards permissions ?
> Any help would be appreciated.
> Thanks
>
> Ryansql sql

Wednesday, March 21, 2012

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.

DTS package to import a csv file on a web server

Im trying to schedule a dts package to import a database table from a csv
file.
In enterprise manager when i attempt to create the dts process, im not sure
what datasource should i be using to connect to my csv file that resides on
the web server?
where do i specify me server to connect to; eg: www.myserver.com\dbRight-click Local Packages and select New Package, Look on Connection and
select Text File (Source). Specify the file name location on the network.
Perhaps you will need to create a shared folder on your web server and give
permissions to the SQL Server service account.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> Im trying to schedule a dts package to import a database table from a csv
> file.
> In enterprise manager when i attempt to create the dts process, im not sur
e
> what datasource should i be using to connect to my csv file that resides o
n
> the web server?
> where do i specify me server to connect to; eg: www.myserver.com\db
>
>|||thanks ben
I think my biggest problem is working out what my UNC path to my web server
is to locate my text file
should it look something like this
\\syd2004\www.bradyaust.com.au\db\
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
> Right-click Local Packages and select New Package, Look on Connection and
> select Text File (Source). Specify the file name location on the network.
> Perhaps you will need to create a shared folder on your web server and
> give
> permissions to the SQL Server service account.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "bj" wrote:
>|||Go to your web server, select the folder containing the text file,
right-click and select Sharing and Security, Share this folder, give
Permissions (very careful here), maybe remove permissions to Everyone, Add
the SQL Server service account, etc.
Let us suppose you shared your folder as MyData, then SQL Server would be
able to access the file using \\webservername\MyData\MyTextFile.
Again, pay special attention to the permissions you give to the shared folde
r.
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> thanks ben
> I think my biggest problem is working out what my UNC path to my web serve
r
> is to locate my text file
> should it look something like this
> \\syd2004\www.bradyaust.com.au\db\
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
>
>

DTS Package scheduled to run as Job Fails

Hi,

I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.

I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.

Any ideas anyone?

MariaIs sa the owner of the job ? If not make sa the owner

Monday, March 19, 2012

DTS Package Schedule

Hallo

I have a DTS Package which reads data from a Access Database and
imports it to SQL Server. This package runs without error when it is
executed trough Enterprise Manager.

When I create a job with "Schedule Package" and then try to run the
job, it gives errors like this:

Ausfhrt als Benutzer: SQL-S002MUR\SQL_Admin. ...db Das System kann
das angegebene Laufwerk nicht finden.DTSRun: Ldt... DTSRun: Fhrt
aus... DTSRun OnStart: DTSStep_DTSCreateProcessTask_1 DTSRun
OnError: DTSStep_DTSCreateProcessTask_1, Fehler = -2147220330
(80040496) Fehlerzeichenfolge: CreateProcessTask
'DTSTask_DTSCreateProcessTask_1': Prozess gab Code 1 zurck. Dieser
stimmt nicht mit dem
...

That means the system cannot find the specified drive.

SQL Server and SQL Server Agent are running as SQL_Admin, I am also
logged in as SQL_Admin when I run the package in Enterprise Manager.

Thank you very much, if someone can give me a hint.
HP"HP Fuchs" <rachid.fuchs@.bluemail.ch> wrote in message
news:f13bcfe2.0308220823.32366179@.posting.google.c om...
> Hallo
> I have a DTS Package which reads data from a Access Database and
> imports it to SQL Server. This package runs without error when it is
> executed trough Enterprise Manager.
> When I create a job with "Schedule Package" and then try to run the
> job, it gives errors like this:
> Ausfhrt als Benutzer: SQL-S002MUR\SQL_Admin. ...db Das System kann
> das angegebene Laufwerk nicht finden.DTSRun: Ldt... DTSRun: Fhrt
> aus... DTSRun OnStart: DTSStep_DTSCreateProcessTask_1 DTSRun
> OnError: DTSStep_DTSCreateProcessTask_1, Fehler = -2147220330
> (80040496) Fehlerzeichenfolge: CreateProcessTask
> 'DTSTask_DTSCreateProcessTask_1': Prozess gab Code 1 zurck. Dieser
> stimmt nicht mit dem
> ...
> That means the system cannot find the specified drive.
> SQL Server and SQL Server Agent are running as SQL_Admin, I am also
> logged in as SQL_Admin when I run the package in Enterprise Manager.
> Thank you very much, if someone can give me a hint.
> HP

This KB article covers this issue in detail:

http://support.microsoft.com/?kbid=269074

Simon

DTS package question

I have created one package and schedule for everday 11.00 pm. Which
processes some data and exports in text file. My problem is I can not export
into separate file. I would like to export into exportfile<yymmdd>.txt. How
can I do this?
one way to do that is to create a global variable that set the output file
name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
script and execute it using xp_cmdshell. To change the file name set your
global variable with the /L option on the DTSRUN command. The /L parameter
of course is generated with the T-SQL script.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I have created one package and schedule for everday 11.00 pm. Which
> processes some data and exports in text file. My problem is I can not
export
> into separate file. I would like to export into exportfile<yymmdd>.txt.
How
> can I do this?
>
|||Thanks Gregory, but I am new to SQL server. Could you please advice me step
by step.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> one way to do that is to create a global variable that set the output file
> name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
> script and execute it using xp_cmdshell. To change the file name set your
> global variable with the /L option on the DTSRUN command. The /L
parameter
> of course is generated with the T-SQL script.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> export
> How
>
|||I changed my mind on what was easiest here. I think you should just use the
"Dynamic Properties Task". So do this:
1) Add a "Dynamic Properties Task" to your DTS package.
2) Click on the Dynamic Properties task you just added, and then click on
the "Add" button.
3) On the "Package Properties" page expand the "Connections" item. Find the
"Text File" connection that you want to change the output file based on the
run date. Then click ont the "Property Name" called "Data Source" (this is
the property the contains your file name). Next click on the "Set" button.
4) On "Add/Edit Assignment" page expand the Source pulldown. Select the
"Query" option. Now type the following query in the the "Query:" text box "
select 'c:\temp\' + convert(char(6),getdate(),12) + '.txt' " (without the
double quotes at the beginning and end.)
5) Click Ok and then OK again.
6) Now save your package and run it. Note you might have to run it twice to
make the dynamic property take.
Good luck.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:%23af2PT0nEHA.3992@.TK2MSFTNGP15.phx.gbl...
> Thanks Gregory, but I am new to SQL server. Could you please advice me
step[vbcol=seagreen]
> by step.
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
file[vbcol=seagreen]
T-SQL[vbcol=seagreen]
your[vbcol=seagreen]
> parameter
> ----
> --
> ----
> --
exportfile<yymmdd>.txt.
>

DTS package question

I have created one package and schedule for everday 11.00 pm. Which
processes some data and exports in text file. My problem is I can not export
into separate file. I would like to export into exportfile<yymmdd>.txt. How
can I do this?one way to do that is to create a global variable that set the output file
name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
script and execute it using xp_cmdshell. To change the file name set your
global variable with the /L option on the DTSRUN command. The /L parameter
of course is generated with the T-SQL script.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I have created one package and schedule for everday 11.00 pm. Which
> processes some data and exports in text file. My problem is I can not
export
> into separate file. I would like to export into exportfile<yymmdd>.txt.
How
> can I do this?
>|||Thanks Gregory, but I am new to SQL server. Could you please advice me step
by step.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> one way to do that is to create a global variable that set the output file
> name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
> script and execute it using xp_cmdshell. To change the file name set your
> global variable with the /L option on the DTSRUN command. The /L
parameter
> of course is generated with the T-SQL script.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > I have created one package and schedule for everday 11.00 pm. Which
> > processes some data and exports in text file. My problem is I can not
> export
> > into separate file. I would like to export into exportfile<yymmdd>.txt.
> How
> > can I do this?
> >
> >
>|||I changed my mind on what was easiest here. I think you should just use the
"Dynamic Properties Task". So do this:
1) Add a "Dynamic Properties Task" to your DTS package.
2) Click on the Dynamic Properties task you just added, and then click on
the "Add" button.
3) On the "Package Properties" page expand the "Connections" item. Find the
"Text File" connection that you want to change the output file based on the
run date. Then click ont the "Property Name" called "Data Source" (this is
the property the contains your file name). Next click on the "Set" button.
4) On "Add/Edit Assignment" page expand the Source pulldown. Select the
"Query" option. Now type the following query in the the "Query:" text box "
select 'c:\temp\' + convert(char(6),getdate(),12) + '.txt' " (without the
double quotes at the beginning and end.)
5) Click Ok and then OK again.
6) Now save your package and run it. Note you might have to run it twice to
make the dynamic property take.
Good luck.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:%23af2PT0nEHA.3992@.TK2MSFTNGP15.phx.gbl...
> Thanks Gregory, but I am new to SQL server. Could you please advice me
step
> by step.
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > one way to do that is to create a global variable that set the output
file
> > name in yoru DTS parameter. Then generate a DTSRUN statement from a
T-SQL
> > script and execute it using xp_cmdshell. To change the file name set
your
> > global variable with the /L option on the DTSRUN command. The /L
> parameter
> > of course is generated with the T-SQL script.
> > --
> >
> ----
> --
> ----
> --
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > I have created one package and schedule for everday 11.00 pm. Which
> > > processes some data and exports in text file. My problem is I can not
> > export
> > > into separate file. I would like to export into
exportfile<yymmdd>.txt.
> > How
> > > can I do this?
> > >
> > >
> >
> >
>

Thursday, March 29, 2012

DTS Scheduling Issue

DTS scheduling and float problem

dts scheduling

dts scheduling

dts scheduling

DTS Scheduled Time

DTS schedule job fails urgent pls help!

DTS schedule and oracle

DTS schedule

Tuesday, March 27, 2012

DTS question

Sunday, March 25, 2012

DTS Problem

DTS Problem

DTS Problem

DTS permissions and ownership

Wednesday, March 21, 2012

dts package?

DTS package to import a csv file on a web server

DTS Package scheduled to run as Job Fails

Monday, March 19, 2012

DTS Package Schedule

DTS package question

DTS package question

DTS Import

Blog Archive

About Me