HELP!!! Trying to schedule SSIS package

Hello everybody,

I try to schedule a SSIS package.

When I run manualy, it works without error but when I schedule it, the step fail with error: "Executed as user:... The package execution failed. The step failed."

This package contain just a SQL Task that execute a truncate table (it's for the test...)

I try all solution I found in this forum and others but all not works.

Have you an idea ?

Thanks a lot

Arnaud

Are you doing any logging? The error message will be in there.

You should also take note of this: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||

Ok it's working now.

It's just because the connection failed and the path for the flath file must be \\<server>\<directory>\<file>

Thanks very much for your swiftness

Arnaud

sql

Help! this is so basic for some of you!

HI! i am a college student and i need help with my PL/SQL project. we have to create a package with subprograms (1 private and 1 public).

the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.

the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
--the parameter passed in may not contain any alphas
--may not contain all the same numbers (111-11-1111)
--will accept only the following values as good:
123456789
123-45-6789
123-456789
12345-6789

create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.

If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!|||Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..

DECLARE
SSN VARCHAR(100) := '123-45-6789';
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
ELSE
IF LENGTH(SSN) = 11 AND
SUBSTR(SSN, '-') = 4 AND
SUBSTR(SSN, '-',2) =7 THEN
DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH (SSN) = 10 AND
SUBSTR(SSN, '-') = 4 OR
SUBSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
INCORRECTLY');
END IF;
END IF;
END IF;
END IF;
END;
/

To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!|||Originally posted by oraculous
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!
The TRANSLATE function will be helpful here:

TRANSLATE( ssn, 'x0123456789-', 'x' )

This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.

The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.

You could test and display the invalid chars like this:

v_invalid VARCHAR2(11);
...
v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
if v_invalid is not null then
raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
end if;|||thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!|||Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??

does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND].....???

CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
-- SSN VARCHAR(100) := '123-45-6789';
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
TEMP_SSN VARCHAR2;
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1) <> '-' AND
SUBSTR(SSN,2) <> '-' AND
SUBSTR(SSN,3) <> '-' AND
SUBSTR(SSN,5) <> '-' AND
SUBSTR(SSN,6) <> '-' AND
SUBSTR(SSN,8) <> '-' AND
SUBSTR(SSN,9) <> '-' AND
SUBSTR(SSN,10) <> '-' AND
SUBSTR(SSN,11) <> '-' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH (SSN) = 10 AND
INSTR(SSN, '-') = 4 OR
INSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
IF SSN_INVALID IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
/|||You can use a FOR loop:

FOR i IN 1..11 LOOP
IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
END IF;
END LOOP;

Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.|||oooOOHH thanks man, you are a great help!!sql

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas?
At this stage any suggestions will be greatly appriciated.
TIA
Rea
Rea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas?
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>sql

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.

GunnyHi

Check out

http://www.databasejournal.com/feat...cle.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny

HELP! " the table schema changed after the cursor was declared"

I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

LiliOriginally posted by lili3000
I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

Lili

I ran in to the same problem. As it turned out I was running the application at 1:00 am to process credit card transaction (This application is a batched response from shipper) Anyway. I had inadvertly schedule maintence during this time. The problem was resolved by moving the Maintence time.

I hope this helps.

Terry

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>
|||On Jan 18, 7:13Xam, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. XHowever, the package runs on the sever when launched via a
> job. XPerhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages, it
> runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I see
> that no records were transferred even though it says "successfully" (which,
> obviously, isn't)
> So, my question is, what could be causing that a DTS package running from a
> Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
I came across a similar problem when I was moving data from sever A to
server B. I was able to fix this by adding a lag between when the data
was available in server A and when it got pulled by the job into
server B. This may or may not solve your problem but its worth a try.
|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.
|||On Jan 18, 2:54 pm, "segis bata" <segisb...@.hotmail.com> wrote:[vbcol=seagreen]
> Mark,
> can you give me a couple of examples on how to do that lag? thanks!
> SB-R
> "Mark T" <vwttra...@.hotmail.com> wrote in message
> news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
>
>
>
Something like this in a different step will work: WAITFOR DELAY
'0:03'
|||I believe that when you manually run a dts package the permissions and
rights of your remote desktop login are being used but when the dts job is
executed by a job it uses whatever the permissions of the sqlagent are
(which may be the system account of the server the contains the dts
package).
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>
|||On Jan 18, 7:13Xam, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. XHowever, the package runs on the sever when launched via a
> job. XPerhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages, it
> runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I see
> that no records were transferred even though it says "successfully" (which,
> obviously, isn't)
> So, my question is, what could be causing that a DTS package running from a
> Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
I came across a similar problem when I was moving data from sever A to
server B. I was able to fix this by adding a lag between when the data
was available in server A and when it got pulled by the job into
server B. This may or may not solve your problem but its worth a try.
|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.
|||On Jan 18, 2:54 pm, "segis bata" <segisb...@.hotmail.com> wrote:[vbcol=seagreen]
> Mark,
> can you give me a couple of examples on how to do that lag? thanks!
> SB-R
> "Mark T" <vwttra...@.hotmail.com> wrote in message
> news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
>
>
>
Something like this in a different step will work: WAITFOR DELAY
'0:03'

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data transformations and data transfers from our server to remote
>> servers. If I execute the DTS from the Data Transformation Services -->
>> Local Packages, it runs smoothly without any problems. But, when I
>> schedule that same DTS package and have SQL (Server 2000) run it as a
>> task on a daily manner it runs "successfully" (according to SQL) but when
>> I see the job history I see that no records were transferred even though
>> it says "successfully" (which, obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
>|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
> > Hello all,
> > I have this situation, I have a DTS package consisting of a series of
> > data
> > transformations and data transfers from our server to remote servers. If
> > I
> > execute the DTS from the Data Transformation Services --> Local
> > Packages,
> > it runs smoothly without any problems. But, when I schedule that same
> > DTS
> > package and have SQL (Server 2000) run it as a task on a daily manner it
> > runs "successfully" (according to SQL) but when I see the job history I
> > see that no records were transferred even though it says "successfully"
> > (which, obviously, isn't)
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> > Thanks in advance for all your help,
> > SB-R- Hide quoted text -
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data
>> transformations and data transfers from our server to remote servers. If
>> I
>> execute the DTS from the Data Transformation Services --> Local Packages,
>> it
>> runs smoothly without any problems. But, when I schedule that same DTS
>> package and have SQL (Server 2000) run it as a task on a daily manner it
>> runs "successfully" (according to SQL) but when I see the job history I
>> see
>> that no records were transferred even though it says "successfully"
>> (which,
>> obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a
>> Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need
>> parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.|||I believe that when you manually run a dts package the permissions and
rights of your remote desktop login are being used but when the dts job is
executed by a job it uses whatever the permissions of the sqlagent are
(which may be the system account of the server the contains the dts
package).
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>

Help!

I have a SQL Server 7 running on NT 4.0 (not by choice). Last night at about 10:00 pm a scheduled DTS package failed. It has been running hourly for months without any problems. Suddenly I'm getting an error "Server could not load DCOM."

Has anyone else experienced this, and if so, how did you fix it?

THANKS!!!check security context of the account the server runs under.

HELP!

Hi everyone.
I got som textfiles that I have to load in to the server,
I've done that with DTS package. When I run the package
manually everything works fine but when I schedule it the
float numbers becommes integers.
Manually 2.2 in the textfile becomes 2,2
in the schedule 2.2 in the textfile becomes 22...
Anyone that know whats the problem is?
/Anett
Hi,
In the Data Transformation Task, click the Transformation and change the
format of the column to Float and save your package.
Thanks
Hari
SQL Server MVP
..
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
> Hi everyone.
> I got som textfiles that I have to load in to the server,
> I've done that with DTS package. When I run the package
> manually everything works fine but when I schedule it the
> float numbers becommes integers.
> Manually 2.2 in the textfile becomes 2,2
> in the schedule 2.2 in the textfile becomes 22...
> Anyone that know whats the problem is?
> /Anett
|||The format of the column is already float. Any other ideas?
/Anett

>--Original Message--
>Hi,
>In the Data Transformation Task, click the Transformation
and change the
>format of the column to Float and save your package.
>Thanks
>Hari
>SQL Server MVP
>..
>"Anett" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
server,[vbcol=seagreen]
the
>
>.
>

HELP!

Hi everyone.
I got som textfiles that I have to load in to the server,
I've done that with DTS package. When I run the package
manually everything works fine but when I schedule it the
float numbers becommes integers.
Manually 2.2 in the textfile becomes 2,2
in the schedule 2.2 in the textfile becomes 22...
Anyone that know whats the problem is'
/AnettHi,
In the Data Transformation Task, click the Transformation and change the
format of the column to Float and save your package.
Thanks
Hari
SQL Server MVP
.
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
> Hi everyone.
> I got som textfiles that I have to load in to the server,
> I've done that with DTS package. When I run the package
> manually everything works fine but when I schedule it the
> float numbers becommes integers.
> Manually 2.2 in the textfile becomes 2,2
> in the schedule 2.2 in the textfile becomes 22...
> Anyone that know whats the problem is'
> /Anett|||The format of the column is already float. Any other ideas?
/Anett

>--Original Message--
>Hi,
>In the Data Transformation Task, click the Transformation
and change the
>format of the column to Float and save your package.
>Thanks
>Hari
>SQL Server MVP
>..
>"Anett" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
server,[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>

HELP!

Hi everyone.
I got som textfiles that I have to load in to the server,
I've done that with DTS package. When I run the package
manually everything works fine but when I schedule it the
float numbers becommes integers.
Manually 2.2 in the textfile becomes 2,2
in the schedule 2.2 in the textfile becomes 22...
Anyone that know whats the problem is'
/AnettHi,
In the Data Transformation Task, click the Transformation and change the
format of the column to Float and save your package.
Thanks
Hari
SQL Server MVP
.
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
> Hi everyone.
> I got som textfiles that I have to load in to the server,
> I've done that with DTS package. When I run the package
> manually everything works fine but when I schedule it the
> float numbers becommes integers.
> Manually 2.2 in the textfile becomes 2,2
> in the schedule 2.2 in the textfile becomes 22...
> Anyone that know whats the problem is'
> /Anett|||The format of the column is already float. Any other ideas?
/Anett
>--Original Message--
>Hi,
>In the Data Transformation Task, click the Transformation
and change the
>format of the column to Float and save your package.
>Thanks
>Hari
>SQL Server MVP
>..
>"Anett" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10bc01c541b4$810abb10$a401280a@.phx.gbl...
>> Hi everyone.
>> I got som textfiles that I have to load in to the
server,
>> I've done that with DTS package. When I run the package
>> manually everything works fine but when I schedule it
the
>> float numbers becommes integers.
>> Manually 2.2 in the textfile becomes 2,2
>> in the schedule 2.2 in the textfile becomes 22...
>> Anyone that know whats the problem is'
>> /Anett
>
>.
>

Help with using the right approach

Greetings my friends

I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package.

The package does the following :

1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table.

2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above)

3) Update the actual DIM table with the new data help in the staging table.

For this task I want to learn the use of the Lookup component which I think is appropriate.

My questions are as follows :

If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!

I am totally confused... I don't even know where to start with this.

Your help would be appreciated.

Thanks SQL friends.

dreameR.78 wrote:

If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!

This should explain it: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Hi Jamie,

Firstly, thanks for the link. Although I haven't implemented a solution yet, I think I know how to go on about solving my problem. But I have one more question regarding the last paragraph from your article.

You say :

"So the only thing you have to do after that is find a way of changing @.[VariableContainingFilterValue]. That's not within the scope of this post because there are many ways that you could do this. I'll leave it to your imagination!"

I am thinking of adding a SQL Task component to set the value of my initial variable. In other words, create a SQL Task containing :

"select max(PRICE_ID) from Price_DIM"

and hold that value in a variable which I can then use to replace @.[VariableContainingFilterValue].

I am not sure if this the most elegant way to do this. Can you suggest alternatives?

Thanks for your help in advance.

|||

dreamer,

In your situation, that is absolutely the correct thing to do! You'll have to give max(PRICE_ID) an alias. Like this:

"select max(PRICE_ID) as MAX_PRICE_ID from Price_DIM"

because you need to reference that alias when you pass the value into your SSIS variable.

-Jamie

|||

Hi Jamie,

Thank you once again.

I have created two variables, one to hold my maximum PRICE_ID and one to hold the SQL String which I plan to use in my data source component. The problem is, when I define the expression (as suggested in your blog) I get an error saying that the expression for variable SourceSQL failed evaluation. There was an error in the expression.

I don't understand as I believe I put the right expression which is :

"Select * from Offer_Price_Dividend where OFFER_PRICE_ID >" + @.[User::MAX_OFFER_PRICE_ID]

One thing I haven't done is cast my MAX_OFFER_PRICE_ID as a string. I will try that and let you know.

Thanks.

|||

dreameR.78 wrote:

Hi Jamie,

Thank you once again.

I have created two variables, one to hold my maximum PRICE_ID and one to hold the SQL String which I plan to use in my data source component. The problem is, when I define the expression (as suggested in your blog) I get an error saying that the expression for variable SourceSQL failed evaluation. There was an error in the expression.

I don't understand as I believe I put the right expression which is :

"Select * from Offer_Price_Dividend where OFFER_PRICE_ID >" + @.[User::MAX_OFFER_PRICE_ID]

One thing I haven't done is cast my MAX_OFFER_PRICE_ID as a string. I will try that and let you know.

Thanks.

You've worked it out on your own :)

Yes, it needs to be casted as a string.

-Jamie