DTS Packages are not working as job

Hello,

I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).

dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.

I don't use a file as a data source or for something else in the package.

Server is SQL 2000 with SP3 and operating system is win2k.

I hope there is someone who has an idea...

elifElif:

What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?

RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.

If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?

It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:

I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.

I may be offbase but that is what he found.

Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,

I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.

bye

elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:
> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> > Hi,
> >
> > Please let me know if there is any system stored procedure which can be
> > used
> > to delete the older versions of dts packages in SQL Server 2000 as in my
> > environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> > eat
> > up the disk space.
> >
> > Any other user SP(if no system SP is thr) if provided will also do.
> >
> > Thanks
> > Manu Jaidka
>
>

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and ea
t
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu JaidkaHello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it free
d
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely becaus
e
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>sqlsql

DTS package version deletion

Hi,
Please let me know if there is any system stored procedure which can be used
to delete the older versions of dts packages in SQL Server 2000 as in my
environment due to heavy use of dts packages msdb grows beyond 5-7 GB and eat
up the disk space.
Any other user SP(if no system SP is thr) if provided will also do.
Thanks
Manu Jaidka
Hello Manu,
This may not be because of DTS packages, this shlould be definetely because
SQL Server stores all the backup and Restore history information
in MSDB Database. Take a look into below URL, which talks about all the
system tables stores history and the methodology to archive those tables
using
sp_delete_backuphistory (Transact-SQL) .
http://msdn2.microsoft.com/en-us/library/ms188653.aspx
Thanks
Hari
"manu" <manu@.discussions.microsoft.com> wrote in message
news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
> Hi,
> Please let me know if there is any system stored procedure which can be
> used
> to delete the older versions of dts packages in SQL Server 2000 as in my
> environment due to heavy use of dts packages msdb grows beyond 5-7 GB and
> eat
> up the disk space.
> Any other user SP(if no system SP is thr) if provided will also do.
> Thanks
> Manu Jaidka
|||Hi Hari,
Actually I suspected the same at the first go but after using this
SP(sp_delete_backuphistory) and deleting records older than 2 months it freed
up very little space in msdb database then I started looking at other side
and came to know that dts packages versions dated 2004 were still there in
msdb database.
After manual deletion of them and retaining only last two months version
history I managed to bring down msdb from 5 Gb to 200 MB.
I am in dire need of one SP which can do the same.
Thanks
Manu Jaidka
"Hari Prasad" wrote:

> Hello Manu,
> This may not be because of DTS packages, this shlould be definetely because
> SQL Server stores all the backup and Restore history information
> in MSDB Database. Take a look into below URL, which talks about all the
> system tables stores history and the methodology to archive those tables
> using
> sp_delete_backuphistory (Transact-SQL) .
> http://msdn2.microsoft.com/en-us/library/ms188653.aspx
> Thanks
> Hari
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:7B924DA7-08B3-4232-BDC5-722C6FA73D11@.microsoft.com...
>
>

dts package to check dynamic file name

Hi all,

I have created a DTS package in SQL Server that uses file system obj to list the .csv files in a folder then runs and imports it into database. The problem that I run into is - the names of the .csv files will be dynamic based on the date and at times the hour.

The question is how to check ( I believe using Active X) for a new file each day and even each hour?

A sample of what my file names are:

CVG_IDO_1_20070703.csv

CVG_IDO_2_20070703.csv

CVG_IDO_3_20070703.csv

CVG_IDO_1_20070704.csv

CVG_IDO_2_20070704.csv

CVG_IDO_3_20070704.csv

Thank you!

A bit hard to say...what constitutes a new file? Are you trying to process all files in the directory? Are those all considered "new files"?

The following article on sqldts.com may help you out, depending on what your needs are with this:

Looping, Importing, and Archiving - http://www.sqldts.com/246.aspx

-Sue

|||

i believe this is time triggered. the last part of the filename is obvoiusly the date

prior to that is a number followed by an underscore.

if this number designate the latest file then you can easily script it.

well that is if your only going to pull out the latest file.

dts migration

Hi,

How to migrate scheduled jobs from 2000 to 2005. Is this possible by doing bcp out and bcp in system tables which stores sql agent jobs from msdb database ?

Regards

Nit

This question is not related to SSIS. I'd try another forum if I were you. https://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

-Jamie