Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts

Wednesday, March 7, 2012

DTSX package calling another DTSX package question

I have a dtsx package that is calling another dtsx package, however, if the called upon dtsx package fails with errors or what not, then the calling package does not continue as well. Is there any way to override this such that if the called upon package fails, the downstream actions in that package can stop, but the calling packages downstream actions to continue?

If you are using an Execute Package Task in the first package to execute the second package, you could change the constraint value on the precedence constraint connecting the execute package task to the task(s) after that to "completion".

Hope this helps..


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Bhargavi Srivathsan wrote:

If you are using an Execute Package Task in the first package to execute the second package, you could change the constraint value on the precedence constraint connecting the execute package task to the task(s) after that to "completion".

Hope this helps..


This posting is provided "AS IS" with no warranties, and confers no rights.

And you'll need to either force the execute package task to return a success, or set the MaximumErrorCount to something greater than 1 (one) because when the child package errors out, it's failure will bubble up to the parent and stop it as well.|||however, i also do want to know if my child failed out..so i force success then i would never know..same with increasing maxerrorcount. any other way?

DTSRUN.exe Fails to Initialize

I have been seeing these intermittently in our event logs...
Event Type: Information
Event Source: Application Popup
Event Category: None
Event ID: 26
Date: 1/30/2006
Time: 6:00:30 PM
User: N/A
Computer: PNTD3BSQLCJ1
Description:
Application popup: dtsrun.exe - Application Error : The application failed to
initialize properly (0xc0000142). Click on OK to terminate the application.
... Anyone have an incite to the problem?
Thanks in advance.
I had a similar issue in trying to execute osql commands from an
Asp.net application. As I recall... this was associated with
insufficient memory associated with the Asp.net memory pool.
I'd start with that. If you determine that this is not a memory
issue...
I'd go through the following progression to trouble shoot...
1.)Run the DTS package from EM
2.)Check passwords etc have not changed on the database
3.)run the dts command from the command line...you may get a more
descriptive error here.
HTH
MJKulangara
http://sqladventures.blogspot.com
|||I found the following KB article regarding the problem...
http://support.microsoft.com/kb/824422

DTSRUN.exe Fails to Initialize

I have been seeing these intermittently in our event logs...
Event Type: Information
Event Source: Application Popup
Event Category: None
Event ID: 26
Date: 1/30/2006
Time: 6:00:30 PM
User: N/A
Computer: PNTD3BSQLCJ1
Description:
Application popup: dtsrun.exe - Application Error : The application failed to
initialize properly (0xc0000142). Click on OK to terminate the application.
... Anyone have an incite to the problem?
Thanks in advance.I had a similar issue in trying to execute osql commands from an
Asp.net application. As I recall... this was associated with
insufficient memory associated with the Asp.net memory pool.
I'd start with that. If you determine that this is not a memory
issue...
I'd go through the following progression to trouble shoot...
1.)Run the DTS package from EM
2.)Check passwords etc have not changed on the database
3.)run the dts command from the command line...you may get a more
descriptive error here.
HTH
MJKulangara
http://sqladventures.blogspot.com|||I found the following KB article regarding the problem...
http://support.microsoft.com/kb/824422

DTSRUN.exe Fails to Initialize

I have been seeing these intermittently in our event logs...
Event Type: Information
Event Source: Application Popup
Event Category: None
Event ID: 26
Date: 1/30/2006
Time: 6:00:30 PM
User: N/A
Computer: PNTD3BSQLCJ1
Description:
Application popup: dtsrun.exe - Application Error : The application failed t
o
initialize properly (0xc0000142). Click on OK to terminate the application.
... Anyone have an incite to the problem?
Thanks in advance.I had a similar issue in trying to execute osql commands from an
Asp.net application. As I recall... this was associated with
insufficient memory associated with the Asp.net memory pool.
I'd start with that. If you determine that this is not a memory
issue...
I'd go through the following progression to trouble shoot...
1.)Run the DTS package from EM
2.)Check passwords etc have not changed on the database
3.)run the dts command from the command line...you may get a more
descriptive error here.
HTH
MJKulangara
http://sqladventures.blogspot.com|||I found the following KB article regarding the problem...
http://support.microsoft.com/kb/824422

Sunday, February 26, 2012

DTSRun Fails while executing from COM

Hi,
i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute.

Function ExeDTSRun(DTSName, mKey As String)
Dim mDTS, spServerName, spUid, spPwd As String

spUid = "sa"
spPwd = "test"
spServerName = "server"


mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0"""
Shell mDTS, vbNormalFocus
ExeDTSRun = "Successfully Run DTS : " & DTSName
End Function

Regards
AdiDo you get an error?|||First guess: Does the account running the IIS thread have permission (NT permission) to execute DTSRUN.EXE. Also, if the DTS package is accessing resources other than on the local machine, you will need to grant permission for that as well.

Sunday, February 19, 2012

DTS Works, job fails!(data source Foxpro and destination SQL Server 2000

The DTS works perfectly when I run it manually. However, when I run it
as a job it fails. Before you ask if i'm running it under different
security context. I have already made sure of that. I was logged into
the server through remote viewer, when I created and ran the package,
as well as scheduling the job. So the accounts they're running under
are consistent. They're the same accounts as the SQL Agent is running
under and it's the sys admin account.

The data source is a Fox pro database with a pull of two tables. I am
using Microsoft OLE DB Foxpro driver as my source connection and OLE DB
Connection for SQL Server as my destination. I am doing a simple table
to table transformation. The path of my connection is a mapped Drive:
E:\Main. There are other packages and jobs within my job queue that are
pointing to the same database and they seem to run fine using the above
mapped drive. The ONLY difference between this package and other
packages are that, they're few months old and this one was created last
night. I have also enabled logging on this package and here is the
below error when the job fails:

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft OLE DB Provider for Visual FoxPro
Step Error Description:Invalid path or file name.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 9/23/2006 11:39:17 AM
Step Execution Completed: 9/23/2006 11:39:17 AM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0No one can help me out with this? Or make any suggestions at the least?

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

Friday, February 17, 2012

dts vs. job

I have a DTS package that runs fine on a SQL 7 sp3 box. When I try and run
it as a job it fails with this error:
DTSRun: Loading...
Error: -2147287038 (80030002); Provider Error: 0 (0)
Error string: The system cannot find the file specified.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 713
Any ideas?Check out the following article:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Rand
This posting is provided "as is" with no warranties and confers no rights.|||jj,
The problem is probably the context that DTS is executing under. One thing
to remember is that DTS is a client process, not a server process.
When run interactively, where is it running? If you are running it from your
machine, then access to the package would be obtained using your domain
rights. If you login to the SQL7 box, then the login that you use must have
the proper domain rights to get to the file.
Now, when you run it as a SQL Agent job, it will run either as the SQL
Server service account (e.g. MyDomain/MySQLServer) or if the job is not
owned by a sysadmin it runs as the SQL Agent proxy account (e.g.
MyDomain/MySQLAgentProxy). See:
xp_sql_agent_proxy
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
xp_aa-sz_8sdm.asp
SQL Server Agent Permissions Checks
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_automate_054p.asp
Which ever account is _actually_ in use must have the necessary rights to
access the package.
Russell Fields
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLsAD$eBEHA.744@.TK2MSFTNGP10.phx.gbl...
> I have a DTS package that runs fine on a SQL 7 sp3 box. When I try and run
> it as a job it fails with this error:
> DTSRun: Loading...
> Error: -2147287038 (80030002); Provider Error: 0 (0)
> Error string: The system cannot find the file specified.
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts.hlp
> Help context: 713
> Any ideas?
>

DTS Transform issue

I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.

Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?

If there is could you be specific in how to setup the DTS transformation script.

TIA
Jeffmoving thread to SQL Server forum (the SQL forum is for the SQL language itself)|||Import everything to a raw table first.

Then you can make all the validation you want before inserting the data into your system.|||I'd probably just suck the data from the external source into a working table that had pure Unicode (NVARCHAR) character columns. Once it was there, you can "sanitize" it any way you need to using Transact-SQL.

Another option that saves on disk and keeps the package conceptually "atomic" would be to handle the exceptions within the DTS package itself. Instead of using a default "flow" transformation within the DTS column mapping, you could use script to do whatever validation suited your needs.

-PatP|||HHmmm, well it seems like you both are saying the same thing.
"raw table" and "working table" are they the same thing?
All fileds are nvarchar correct?
and use a copy column to column transform correct?

JR

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

Wednesday, February 15, 2012

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

DTS Tranform Data Task On Failure

How do I set an On Failure when a Tranform Data Task fails? I'm pulling in a text file and if the set the On Failure for the text file connection it says "Defining precedences between the selected items is not valid", and if I set the On Failure for the database connetion is doesn't execute when the tranform data fails. Any ideas on how capture this error?You set the failure on the tranformation not on the connections. The transformation will fail becuase it can't connect, but the conneciton itself will not cause an "on failure" event.|||I've tried that as well. I click on the Transform Data Task, then click on my error module and select Workflow>On Failure and it tells me I have fewer than 2 items have been selected. Is there another way to set the on failure for a transform data task?|||What version of SQL is this. I just did a quick refresher on SQL 2k to make sure I wasn't talking rubbish. I selected the transformation, then selected my activex script and right clicked Workflow, Workflow Properties. I then clicked New and added the precedence...

DTS to transfer data from different database failed

My SQL server is SQL 2000. The DTS package uses SQL anthentication to
transfer data from database A to database B. It fails. The error message
shows Windows NT user or group 'A\Guest' not found.
In DTS package, on Copy SQL Server Objects Task Properties screen, if I
don't select Create destination objects, copy data works. However the
destination tables do not have keys adn indexes.
How can I fix it? Any thoughts? Thanks.Hi
I am not sure why it will allow you to do this assuming that you use the
same users to authenticate when using copy objects and copy tables!
You may want to try scripting the tables including the indexes/pk/fks and
create them before populating them with data. If you have FKs then you will
need to load the data in the correct order if you don't want to
disable/enable the constraint.
John
"Sean" wrote:

> My SQL server is SQL 2000. The DTS package uses SQL anthentication to
> transfer data from database A to database B. It fails. The error message
> shows Windows NT user or group 'A\Guest' not found.
> In DTS package, on Copy SQL Server Objects Task Properties screen, if I
> don't select Create destination objects, copy data works. However the
> destination tables do not have keys adn indexes.
> How can I fix it? Any thoughts? Thanks.
>
>|||Please correct me if I am wrong. When I use SQL authentication for
destination database in DTS package when using copy objects and copy tables,
I thought it should be allowed. Otherwise, how do I make it work? Is there
anything missing?
Thanks.
"John Bell" wrote:
> Hi
> I am not sure why it will allow you to do this assuming that you use the
> same users to authenticate when using copy objects and copy tables!
> You may want to try scripting the tables including the indexes/pk/fks and
> create them before populating them with data. If you have FKs then you wil
l
> need to load the data in the correct order if you don't want to
> disable/enable the constraint.
> John
> "Sean" wrote:
>|||Hi
If you have permissions to create the table then you should be able to use
either method regardless of whether you use SQL Authentication or Windows
Authentication, therefore I am not sure why the copy tables worked and the
copy objects did not (assuming that you did not create the tables first). Yo
u
may want to use SQL Profile to see what is being executed on each of the
servers.
By pre-creating the table you will only need to insert the new data.
John
"Sean" wrote:
> Please correct me if I am wrong. When I use SQL authentication for
> destination database in DTS package when using copy objects and copy table
s,
> I thought it should be allowed. Otherwise, how do I make it work? Is there
> anything missing?
> Thanks.
> "John Bell" wrote:
>|||I agree with you. I assigned public and db_owner role for the SQL account on
database B. I can use that SQL account to create SQL object on database B
manually. The thing I am not sure is, when I run DTS package, why SQL serve
r
chooses 'A\Guest' account on database B.
By the way, how can I use SQL Profile to see what is being executed on each
of the servers?
Thanks.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>|||John,
I figured out why the DTS package did not work by using SQL Profile. I have
a user 'A\Guest' on datatase A. That account could not be created on databas
e
B. After I removed that account, the DTS package works.
Thanks for your help.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>