How to trap xp_cmdshell dtsrun error messages

Hi Here is my store procedure. The below code is not giving error at all when dts fails.
Can any one please help me how to trap xp_cmdshell dtsrun error messages
CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
exec USP_ReadVRUDirectory
set nocount on
DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by filelist
OPEN VRUCFileListCursor
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
WHILE @.@.FETCH_STATUS = 0
BEGIN
truncate table VRUSURVEYComments
set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +' e:\ftproot\vru\VRUComments.txt'
exec master..xp_cmdshell @.cmdstring
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyComments'
SELECT @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
Print @.Error
END
UPDATE VRUSURVEYComments SET [Filename] = @.filename -- where [Filename] is null
update VRUSURVEYComments set cur= 'T'
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyCommentsExport'
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
END
CLOSE VRUCFileListCursor
DEALLOCATE VRUCFileListCursor
Hi
Rather than using xp_cmdshell to rename the file try using something like
the following to change the name of the source file. Using the FSO you can
check errors more easily!
http://www.sqldts.com/default.aspx?200
http://www.sqldts.com/default.aspx?246
You should also check the return value from xp_cmdshell e.g
DECLARE @.cmd sysname, @.var sysname, @.stat int
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > G:\var_out.txt'
EXEC @.stat = master..xp_cmdshell @.cmd
PRINT '@.stat= ' + CONVERT(varchar,@.stat)
John
"Admin" <admin@.emoneylinks.com> wrote in message
news:12AE78DE-091E-4E43-BEAA-639F1EAA6E4C@.microsoft.com...
> Hi Here is my store procedure. The below code is not giving error at all
when dts fails.
> Can any one please help me how to trap xp_cmdshell dtsrun error messages
> CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
> exec USP_ReadVRUDirectory
> set nocount on
> DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
> DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
> SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by
filelist
> OPEN VRUCFileListCursor
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> truncate table VRUSURVEYComments
> set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +'
e:\ftproot\vru\VRUComments.txt'
> exec master..xp_cmdshell @.cmdstring
> exec master..xp_cmdshell 'dtsrun /S
WPCCP005DS /E /N VRUSurveyComments'
>
> SELECT @.Error = @.@.ERROR
> IF @.Error <> 0
> BEGIN
> Print @.Error
> END
>
> UPDATE VRUSURVEYComments SET [Filename] = @.filename --
where [Filename] is null
> update VRUSURVEYComments set cur= 'T'
> exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N
VRUSurveyCommentsExport'
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> END
> CLOSE VRUCFileListCursor
> DEALLOCATE VRUCFileListCursor
>
>

how to transfer gdb format data to SQL server

My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??Assuming that you have an ODBC or OLEDB driver that allows you to use the GDB file, you could use that.

-PatP|||I feel this .GDB is from mainframe or unix if so as suggested use the compatible odbc driver.|||Thank you everybody
I found ODBC driver and everythink's working well (for the time being at least:))

how to transfer DTS from one computer to other

How to transfer DTS from one computer to other?
I have a DTS package runing on one server1. I need to transfer this DTS to some other server2. But these two servers are on the different network. Hence temme the way to transfer it to server2.
Thnks,
Rahulopen that DTS and click save-as, select Structured-Storage-File as location. a disk file will be generated. this can be imported to the new server by right click on Data-Transformation-Services and selecting Open-Package. after opening save by selecting SQL-Server as locationsql

How to transfer data from SQL Server 2000 to Db2 V8 using DTS and Odbc driver

hi all,

I am trying to create a dts package to transfer data from tables in my
sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
db2 oledb driver for the destination this works fine except that rows
are committed on a row by row basis thus performance is slow. I want
to use the odbc driver but when I define it as the destination, i get
errors saying that the maximum number of rows failed has exceeded the
minimum.

Does anybody know what to do in order to get the transfer working
using ibm db2 odbc as the datasource?

Thanks
Lyn"Lyn Duong" <lynd@.tablimited.com.au> wrote in message
news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> hi all,
> I am trying to create a dts package to transfer data from tables in my
> sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> db2 oledb driver for the destination this works fine except that rows
> are committed on a row by row basis thus performance is slow. I want
> to use the odbc driver but when I define it as the destination, i get
> errors saying that the maximum number of rows failed has exceeded the
> minimum.
> Does anybody know what to do in order to get the transfer working
> using ibm db2 odbc as the datasource?
> Thanks
> Lyn

I guess the error message is actually "The number of failing rows exceeds
the maximum
specified"? That is a very general error, but there should be a more
detailed error message immediately after it in the log, which may give a
clue to the issue - can you post the full error message?

Simon|||Hi,

Yes, I logged the errors in an exception file and in an error file
I suspect that the odbc driver is not parsing the record properly

The exception file contains
DATA TRANSFORMATION SERVICES: Data Pump Exception Log

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Step Name: DTSStep_DTSDataPumpTask_1
Execution Started: 8/07/2004 9:43:51 AM
Error at Destination for Row number 1. Errors encountered so far in
this task: 1. |1|609||||||||IS

The error file contains
The execution of the following DTS Package succeeded:

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Execution Lineage: {6AE6BF2C-8187-4A9F-AA5F-2565B873DC51}
Executed On: ROULETTE
Executed By: lynd
Execution Started: 8/07/2004 9:43:50 AM
Execution Completed: 8/07/2004 9:43:51 AM
Total Execution Time: 0.25 seconds

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data
Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. ( (80004005): )
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 8/07/2004 9:43:50 AM
Step Execution Completed: 8/07/2004 9:43:51 AM
Total Step Execution Time: 0.22 seconds
Progress count in Step: 0
************************************************** **************************************************

Any ideas ? Thanks!
"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40ec34b4$1_1@.news.bluewin.ch>...
> "Lyn Duong" <lynd@.tablimited.com.au> wrote in message
> news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> > hi all,
> > I am trying to create a dts package to transfer data from tables in my
> > sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> > db2 oledb driver for the destination this works fine except that rows
> > are committed on a row by row basis thus performance is slow. I want
> > to use the odbc driver but when I define it as the destination, i get
> > errors saying that the maximum number of rows failed has exceeded the
> > minimum.
> > Does anybody know what to do in order to get the transfer working
> > using ibm db2 odbc as the datasource?
> > Thanks
> > Lyn
> I guess the error message is actually "The number of failing rows exceeds
> the maximum
> specified"? That is a very general error, but there should be a more
> detailed error message immediately after it in the log, which may give a
> clue to the issue - can you post the full error message?
> Simonsql

How to Transfer data from AS400 to SQL 2005

Hi All,

I have to transfer AS400 files to Sql 2005 tables .For this I am using the DTS wizard. I am able to connect to AS400 Database and in the Wizard when I give the sql statement Select * from table1 the statement is parsed. In the next step however I am geting DTS unhandled exception with no error message.

"An error occured which the SQL Server Integration services wizard was not prepared to handle.
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)".

I am not able to proceed further. Pls let me know what I should do?

I am using IBMDB2 UDBFOR ISEREIS IBMDA400 OLE DB PROVIDER to connect to AS400 from the DTS wizard and IBM Client Access Tool to connect to AS400.

How can I transfer Data from AS400 files to Sql 2005 tables using the DTS wizard?

Regds,

Anu

What is the DTS wizard with respect to SSIS?|||

if you do a search you should find a few topics related to people trying to use the native OLE DB drivers to access AS/400 data. Most of us (me included) seem to have given up trying to solve the problem and are just using the IBM-provided ODBC driver instead.

Of course, this could mean your packages need some alteration since SSIS has some differences depending on the type of connection being used (e.g. Datareader vs. OLEDB sources).

How to take back up of DTS packages ?

Hi,
Is there any way to take backup of DTS packages ?
Thanks,I saved my DTS packages in SQL Server. So, if I go to DTS node I can see all
my packages.
If I restore msdb, will DTS packages reflect in my SQL server like it used
to be ?
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> They are stored in MSDB. Backup MSDB.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Is there any way to take backup of DTS packages ?
> >
> > Thanks,
> >
> >
>|||Your DTS Package saved in the msdb database, you need a
backup msdb database
>--Original Message--
>Hi,
>Is there any way to take backup of DTS packages ?
>Thanks,
>
>.
>|||Also checkout this utility
Backing Up Packages
(http://www.sqldts.com/default.aspx?202)
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4p6dnWJ9t6iPkemiRVn-uA@.giganews.com...
> Yes. MSDB also contains your SQL Agent jobs, schedules, etc so these will
be
> restored as well.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:OdUHJT%23gDHA.1668@.TK2MSFTNGP12.phx.gbl...
> > I saved my DTS packages in SQL Server. So, if I go to DTS node I can see
> all
> > my packages.
> > If I restore msdb, will DTS packages reflect in my SQL server like it
> used
> > to be ?
> >
> > Thanks
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> > > They are stored in MSDB. Backup MSDB.
> > >
> > > --
> > > David Portas
> > > --
> > > Please reply only to the newsgroup
> > > --
> > >
> > > "Vgs" <generalofmine@.yahoo.com> wrote in message
> > > news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > >
> > > > Is there any way to take backup of DTS packages ?
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>

How to stop running DTS package if my view return zero record?

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How? Can someone please help? Thanks

Kelvin,

This is an SSIS forum (not DTS).

Sorry.

|||Not sure, and I second Rafael's comment, but can you put in a row counter against the view and test it's result?|||

I did, but I don't know how to put it into a package.?

Please Help......

|||

Kelvin Y wrote:

I did, but I don't know how to put it into a package.?

Please Help......

If you are using SSIS, and not DTS, you can use an Execute SQL task in the control flow to query the view and capture the row count. (select count(*) from view) Then in the control flow you can test the variable that was previously populated with the Execute SQL task for a condition of != 0, and if so, continue processing.|||

OK. here's another idea....slightly different from Phil's. Give it a shot. (using SSIS again)

The SQL task has a property "ForceExecutionResult" that you can configure using exprssions. Get the count from the view exactly like Phil explained above but instead of using a conditional split, use the value to set the exprssion to Success or Failure.

You wont be able to log an approiate error message though.

P.S: For some reason the use of the word "e x p r e s s i o n" is causing my post to be truncated. So, had to use "exprssion"

|||

Thanks everyone, but I can't find the "ForceExecutionResult" property.

Can you tell me where is it?

Thanks

|||

If you dont have experience with exprssions, I'd suggest using Phil's approach.

But to answer your question, you can find the property under the "misc" category about 6th in the list.

|||Thanks everyone, it works now. Thanks for helping me out.|||

Kelvin Y wrote:

Thanks everyone, it works now. Thanks for helping me out.

Please mark this thread as answered.

How to Stop Logging

Hi,

I am doing some resource hungry tasks (some extraction and loading
through DTS), for which each time the SQL Server Log files gets filled
up!

Is there any way to stop the logging (like as during restore)?

Thanks in advance.

-surajits_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0401290212.6defeb00@.posting.google.com>...
> Hi,
> I am doing some resource hungry tasks (some extraction and loading
> through DTS), for which each time the SQL Server Log files gets filled
> up!
> Is there any way to stop the logging (like as during restore)?
> Thanks in advance.
> -surajit

You can't disable logging, but you could set the database recovery
model to simple during the load. You might also be able to load in
batches, instead of in a single transaction, which should require less
log space. The DTS Transform Data Task allows you to specify a batch
size, for example.

Simon

How to stop execution of DTS package (during loop)

I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.

ActiveX Script 1

Option Explicit

Function Main()

Dim vDate, vCounter, vBranchCode, vPath

vDate="011207"

vCounter=DTSGlobalVariables("gVarCounter").Value

IF vCounter<=3 THEN

IF vCounter=1 THEN
vBranchCode="ALB"
vPath= "D:\PROJECTS\HRIS\ALB\"
ELSEIF vCounter=2 THEN
vBranchCode="MOA"
vPath= "D:\PROJECTS\HRIS\MOA\"
ELSEIF vCounter=3 THEN
vBranchCode="PSQ"
vPath= "D:\PROJECTS\HRIS\PSQ\"
END IF

DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"

Main = DTSTaskExecResult_Success

ELSE
<This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
DTSGlobalVariables("gVarCounter").Value=1
<DTS Process should stop execution...how is this?>
END IF

End Function

After excel to sql dts
ActiveX Script2

Function Main()

IF gVarCounter<=3 then
DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF

End Function

Thanks a lot.there are many ways to stop a DTS. u can simply write

Main = DTSTaskExecResult_Failure

in the ELSE part and link the next step with "on success" workflow. or u can even write a blank ActiveX step and redirect the flow to that step in the ELSE part with "...DTSStepExecStat_Waiting" as u r already doing|||sorry, i pressed the save button twice... and the same thing got posted twice... there sould be an option to delete a post....|||hi upalsen,

thanks a lot. it's now working. great!
god bless.|||hi LimaCharlie,

thanks for your acknowledgement. many here do not acknowledge the solution they have accepted. an acknowledgement is (1) a recognition for support (2) helps in closing the post (3) helps a third user who is viewing the post at a later date (or coming from search engine) to understand what the final solution could be.