Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! Creating Custom Data Flow Task

I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

So provide a custom IDTSCustomProperty90 in ProvideComponentProperties() and let users type in the variable name is enough(of course, I should also follow what you said)?

I wanted to make it behave the same way as the FileNameVariable property of Raw File Destination, that is: users don't type in the variable name, instead, they select from all available variable names.
|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

How can I get the matching variable and it's value at runtime? And generally in which method should I do that? Thanks.
|||

Here's a sample from a component I developed to raise custom events from a package's control flow; the same basic technique should work for you in data flow as well.

First, create a property that stores the variable name. This will be set by the package developer at design time.

Code Snippet

private string variableName;

///

/// This property gets or sets the name of the variable in which the

/// event text is stored.

///

[Browsable(true)]

[Category("Custom")]

[Description("Gets or sets the name of the variable in which the event text is stored.")]

public string VariableName

{

get

{

return variableName;

}

set

{

if (value.IndexOf("::") == -1)

{

variableName = "User::" + value;

}

else

{

variableName = value;

}

}

}

(The set accessor for the property is simply explicitly adding the User namespace to the specified variable name unless a namespace is already specified. I've yet to add a drop-down so the package developer can pick from a list of available variables.)

Then, you can use the component's VariableDispenser object (which is passed in a parameter to many of the methods you overload when developing your component) to lock and work with the package variable identified by the property. Here's a sample from the Validate method in my component:

Code Snippet

Variables vars = null;

DTSExecResult result;

variableDispenser.LockOneForRead(variableName, ref vars);

if (vars[variableName].DataType != TypeCode.String)

{

// The variable is accessable, but is not the correct data type

componentEvents.FireError(0, SubComponent(variableDispenser),

string.Format("Variable '{0}' is not a String variable", variableName), "", 0);

result = DTSExecResult.Failure;

}

else

{

result = DTSExecResult.Success;

}

vars.Unlock();

return result;

The key thing here is that you're using the variableDispenser to lock the specific variable identified by the component's property, working with it, and then unlocking it when you're done.

(The SubComponent method which is referenced in the code sample above is just a little helper routine that returns a string in a standard format to identify where an error or event is coming from. I need this type of string in many places, so I have a function that can be reused, but it's not really relevant to your question...)

Hope this helps!

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
>