DTS from mapped drive problem

Hey guys

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

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

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

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

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

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

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

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

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

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

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

RETURN @.ret
GO

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

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

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

What to say

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

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

dts from command line

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

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

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

DTS from .NET (C#)?

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

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

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

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

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

cool...|||Yeah!

DTS distribution - LoadLibrary failed - the specified module could not be found

Hi,

i have a VB.net (2005) application that just runs a DTS package. I tried to install my application in a CLEAN XP computer (it means without any SQL2000 tools or DLLs, ONLY the .net framework 2.0).

When i try to register the dtpkg.dll and all the other dlls using regsvr32.exe i receive the error

"LoadLibrary("C:\program files\myapp\dtspkg.dll) failed - the specified module could not be found".

in C:\Windows\system there is the sqlunirl.dll

In C:\program files\myapp folder i have all the following DLLs:

sqlresld.dll

dtsffile.dll
dtspkg.dll
dtspump.dll
axscphst.dll
dtsrun.exe
sqlunirl.dll
custtask.dll

and in C:\program files\myapp\resources\1033 folder the RLLs

dtsffile.rll
dtspkg.rll
dtspump.rll

custtask.rll

axscphst.rll

dtsrun.rll

I followed step by step all the dts redistribution instructions as explained in this article

http://www.sqldts.com/225.aspx

and in the redist.txt contained in the SQL2000 CD.....

Anybody has ideas?

Thx

mcrisf

I am having the same issue. I followed the steps on the site exactly but I still get the "LoadLibrary("C:\program files\myapp\dtspkg.dll) failed - the specified module could not be found". error. Anyone?|||

Copy required .rll files in the binn\resources\1033 folder.

Make sure sqlresld.dll is present in the binn folder.

DTS distribution - LoadLibrary failed - the specified module could not be found

Hi,

i have a VB.net (2005) application that just runs a DTS package. I tried to install my application in a CLEAN XP computer (it means without any SQL2000 tools or DLLs, ONLY the .net framework 2.0).

When i try to register the dtpkg.dll and all the other dlls using regsvr32.exe i receive the error

"LoadLibrary("C:\program files\myapp\dtspkg.dll) failed - the specified module could not be found".

in C:\Windows\system there is the sqlunirl.dll

In C:\program files\myapp folder i have all the following DLLs:

sqlresld.dll

dtsffile.dll
dtspkg.dll
dtspump.dll
axscphst.dll
dtsrun.exe
sqlunirl.dll
custtask.dll

and in C:\program files\myapp\resources\1033 folder the RLLs

dtsffile.rll
dtspkg.rll
dtspump.rll

custtask.rll

axscphst.rll

dtsrun.rll

I followed step by step all the dts redistribution instructions as explained in this article

http://www.sqldts.com/225.aspx

and in the redist.txt contained in the SQL2000 CD.....

Anybody has ideas?

Thx

mcrisf

I am having the same issue. I followed the steps on the site exactly but I still get the "LoadLibrary("C:\program files\myapp\dtspkg.dll) failed - the specified module could not be found". error. Anyone?|||

Copy required .rll files in the binn\resources\1033 folder.

Make sure sqlresld.dll is present in the binn folder.

sql

DTS deployment issues

Hello!

I'm trying to deploy an application that runs DTS packages from the local machine.

I've read some articles that it would be enough to install the SQL Client Tools on the local machine to make the DTS work . I did this, the package executes a few steps (it shows message boxes and writes data into the database), then (before executing some validation tasks on the data) it throws an exception 'Execution was canceled by user'. I've set the pbCancel flag and the package runs fine on some computers, but on an empty system that only contains my application and the Sql Client Tools I get this exception.

Do I have to install anything else or do I have to make some changes to DTS package to make it work?

Thank you in advance!

this is the wrong forum; Unless your are using SSIS (SQL SERVER 2005).|||sorry, my mistake

DTS completion time jump

Hi,
I have a DTS package that runs 4 other packages on a nightly basis. This
package usually takes about 2 hours to run, but ocasionally it jumps to
around 5 hours to complete. There have not been any changes to the
parameters or coding. It is the same process that causes this each time
which is a cursor run strored procedure. There have not been any huge jumps
in the number of records in the table or changes to the source data that
would cause the cursor to take longer to run through. This seems to be
intermittent, I can not easily check to see if something else in the network
on that server is happening since it is a customer site. Would you think
that something outside of the package is affecting this since it only happen
s
once in a while (about once a w or so), like I mentioned - backups, serve
r
processes, other jobs etc.? I just don't know where to look if I have not
changed anything within my package.
Thanks,
PatriceThe are many reasons why performance can change over time. One is job
contention, where multiple jobs are running at the same time. The following
will dump a listing of jobs, when they started and ended out of the
msdb.dbo.sysjobhistory system table, and store it in a temporary table:
select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
'1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
INTO #jobsched
FROM (
select t1.[name],
CAST(
start_date + ' ' + LEFT(t1.start_time,2) + ':' +
SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
) AS "job_start",
LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
RIGHT(t1.job_duration,2) AS job_duration
FROM
(
select
j.[name],
LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date",
REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
"start_time",
REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6))
"job_duration"
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
ON h.job_id = j.job_id
WHERE h.step_id = 0
) t1
) t2
SELECT * FROM #jobsched
You could take a look at the output of the above and "eyeball" it for job
conflicts, or you could try (*untested*)
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
RIGHT JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
to get a listing of jobs that are conflicting with the job named 'Job to
analyze for conflicts' .
It's also possible that the additional time for the DTS task to process is
due to locking. You could try adding another stop to the DTS job that dumbs
the output of sp_who2 into a table for further analyses.
Hope that the above helps.
--
"Patrice" wrote:

> Hi,
> I have a DTS package that runs 4 other packages on a nightly basis. This
> package usually takes about 2 hours to run, but ocasionally it jumps to
> around 5 hours to complete. There have not been any changes to the
> parameters or coding. It is the same process that causes this each time
> which is a cursor run strored procedure. There have not been any huge jum
ps
> in the number of records in the table or changes to the source data that
> would cause the cursor to take longer to run through. This seems to be
> intermittent, I can not easily check to see if something else in the netwo
rk
> on that server is happening since it is a customer site. Would you think
> that something outside of the package is affecting this since it only happ
ens
> once in a while (about once a w or so), like I mentioned - backups, ser
ver
> processes, other jobs etc.? I just don't know where to look if I have not
> changed anything within my package.
> Thanks,
> Patrice|||Sorry, that last query should have been
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
INNER JOIN --INNER replaced RIGHT for the JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
"Mark Williams" wrote:
> The are many reasons why performance can change over time. One is job
> contention, where multiple jobs are running at the same time. The followin
g
> will dump a listing of jobs, when they started and ended out of the
> msdb.dbo.sysjobhistory system table, and store it in a temporary table:
>
> select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
> '1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
> INTO #jobsched
> FROM (
> select t1.[name],
> CAST(
> start_date + ' ' + LEFT(t1.start_time,2) + ':' +
> SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
> ) AS "job_start",
> LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
> RIGHT(t1.job_duration,2) AS job_duration
> FROM
> (
> select
> j.[name],
> LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
> char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date
",
> REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
> "start_time",
> REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6
))
> "job_duration"
> from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
> ON h.job_id = j.job_id
> WHERE h.step_id = 0
> ) t1
> ) t2
> SELECT * FROM #jobsched
> You could take a look at the output of the above and "eyeball" it for job
> conflicts, or you could try (*untested*)
> SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
> t2.job_start, t2.job_end
> FROM
> ( SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] = 'Job to analyze for conflicts'
> ) t1
> RIGHT JOIN
> (
> SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] <> 'Job to analyze for conflicts'
> ) t2
> ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
> OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
> to get a listing of jobs that are conflicting with the job named 'Job to
> analyze for conflicts' .
> It's also possible that the additional time for the DTS task to process is
> due to locking. You could try adding another stop to the DTS job that dumb
s
> the output of sp_who2 into a table for further analyses.
> Hope that the above helps.
> --
> "Patrice" wrote:
>

dts ackage failing

hi

i have a dts package that runs fine in sql7.0 ,os win NT ,but the same dts fails in sql2000 Win2000 OS, with the following eror

cursor opeartion couldnot be completed since the set options for the cursor have been changed since the cursor was last declared.

please help.Are you using global cursors ?|||yes|||yes|||make sure u 'close' or 'deallocate' ur cursors after usage otherwise it has sum undesirable effects

DTS a large file

I currently have a DTS that runs a Stored procedure.

The stored procedure does a hit from a few table and returns a large amount of data (100K + rows).

I tried to set up the DTS to send me the file as a .csv, but it is simply to big of a file.

Is there a way I can get this to work, do I have to use winzip or something of that nature?

I simply want the sql hit to run each month and the DTS to run the command to email me the file.

Any idea would be appreciated.What other alternatives do you have to emailing ? What will you do with the file once it has been emailed to you ?|||The file is used in a statistical program by the marketing department.

Thanks
Tom|||Instead of emailing, why don't you just ftp ?|||Originally posted by rnealejr
Instead of emailing, why don't you just ftp ?

We were hoping to have the DTS run every month and send me the file.

The idea was to make this an automated as possible.

That is why I hoping I could just have the DTS email me the .csv.

I am not sure what you mean by FTP, I know what FTP is, but are you saying that I should FTP the file after it has been created?

Thanks
Tom|||One of the tasks available with dts is a "file transfer protocol task". How are you creating the csv using dts ?

DTS 2000

Hi,

I am having a dts 2000 package which is accessing oracle source and loading data into sql server .The package runs fine when ran individually or from designer.

But when it is scheduled into a job, the job is being shown as ran successfully whereas the package is not run or for sure the data is not loaded.

I looked into microsoft articles regarding this problem and found that this job will be run under the profile who started the SQL Server Agent.There is another job which is running properly whose owner is xxx. So i changed the owner of my job into xxx.

But still the same condition is prevailing, job is being shown as ran successfully while the data is not loaded.

Kindly help me on this.

Thanks and Regards

Arobind

Are you executing this DTS package via SSIS? If not, you're in the wrong forum as this is an SSIS forum. The DTS forum can be found here: http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg

If in SSIS, make sure that you set the FailParentOnFailure to true.|||

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

|||

Then your problem is within SQL Server Agent...the Job is being reported as Successfully run but the results indicate it actully does not run....

|||

Arobind Balakrishnan wrote:

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

Again, is this an SSIS package, or an older DTS package? This forum can help you only if it's an SSIS package.|||

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

|||

Arobind Balakrishnan wrote:

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

Nope, I'm not sure that they even had MSDN forums when DTS was released. So they created a USENET newsgroup for DTS, which is the link I provided above. They felt it was important to segregate the two products.

I could be wrong on some of those points though.

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks.
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tell
s
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this.
I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - How to...

I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?One approach:

Write an ActiveX Script to take the place of the Connection to Oracle. In your ActiveX Script, establish the connection to Oracle, pull the data into SQL in a temp table and then use the Data Driven Query Task to process the data from the temp table. The ActiveX script can contain either a lookup to a date/time field which us updated each time the script processes, or you can take the max date/time field from the last job processed as your new starting point.

There are lots of things wrong with this approach, but it is doable.

Another Approach:
In the ActiveX script for the Data Driven Query task, you can do a similar thing with pulling in the last date/time stamp and then use the DTSTransformStat_SkipRow to skip over a record that you don't want to process. This still involves pulling the data across the nework from Oracle to SQL, but you will save some processing time.

Again, there are porbably lots of things wrong with this approach, but it is doable.

Finally, I have not yet discovered a way to manipulate the SQL text in a Data Pump or Data Driven Query object. I believe that it is theoretically possible to do it (by manipulating the DTS package in SQL DMO), but I have never really had the time to fully explore this.

Best of luck to you...

Hugh Scott

Originally posted by brucevde
I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?

DTS - Error

Hi,

I encouter a mystic behaviour:

1. When I am starting my DTS-package (using "other ODBC-Device" for connecting) by hand, it runs always reliable.

2. When I schedule this task, it gets always the following error:

Executed as user: ACB\Dienst-SQL. ...nStart: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnError: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnStart: DTSStep_OMWCustomTasks.OMWTransferErrors_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSSt... Process Exit Code 12. The step failed.
:(

Can anybody explain me the different handling of a scheduled task and a task startet manually ?

Thx in advanceI would guess your dts package is using a user dsn to make one of your connections. When you schedule the job you are not running as the same user when you execute the package interactively and therefore you can not file the dsn.

If this is the case I think changing the dsn to a system dsn will fix the problem.

If you are not doing this then you'll need to give more information about how that specific connect is defined.