Out of virtual memory

I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline]

Information: The buffer manager detected that the system was low on

virtual memory, but was unable to swap out any buffers. 4 buffers were

considered and 4 were locked. Either not enough memory is available to

the pipeline because not enough is installed, other processes are using

it, or too many buffers are locked

Thank you for your assistance!write a sql command like "select * from destination_table where 1=0",otherwise SSIS will retrieve all data from your destination table.|||Hi icewill,

Not quite sure I understand why I would do that, I do want all the records. Would you please elaborate? We are loading a data warehouse. Thanks.|||Hello there,

I got such problem a while ago. By reducing "DefaultBufferMaxRow" to certain amount (My package was set to 5000) and keep "DefaultBufferSize" as small as possible (default is 10MB, Max is 100MB), your package should run succefully. Please keep in mind that Test and messure could take little bit of time for reaching the best performance.

Good luck|||Hi

Steglitz,

I have tried numerous settings but none of

them successful. I have tried DefaultBufferMaxRow at the following

values 2000, 5000, 7500, 10000 along with DefaultBufferSize set to the

default, 25Mb, 50Mb and 75Mb.

When running

performance monitor the only difference I noticed was the number of

buffers went up as the values were increased. Here

is an article I found explaining buffer

sizing:

Understanding How the Data

Flow Task Sizes the Buffers

The

data flow engine begins the task of sizing its buffers by calculating

the estimated size of a single row of data. Then it multiplies the

estimated size of a row by the value of

DefaultBufferMaxRows to obtain a preliminary working

value for the buffer size.

If the result is more than

the value of DefaultBufferSize, the engine reduces

the number of rows.

|||Here is another issue to add on top of the previous... Once the package runs Sql Server is left holding all the memory to the system. We watched it both through Performance Monitor and through task manager and after an hour it had not released the memory... 1.4 Gb and holding. The only way we could get it to reduce was to restart the Sql Server service. Is there anyone else out there that seems to be having any of these issues?|||

Bagles,

Actually i dont have any idea about why you need data from destination table, i met the problem that you mentioned before, and at last i found if you select a table as your destination directly or write sql like "select * from destination_table", SSIS will retrieve all data from the database, if there is great number of records in your destination table,that will induce your SSIS out of memory, that is not what i want to see, my mainly purpose is to get the fields to map for, so i modified the sql like "select * from destination_table where 1=0",and the problem has been solved perfectly.

GL~

|||Hi Icewill,

Using a query like that to obtain the metadata makes sense, and I suppose this is probably a silly question, but are you then using an expression to inject the actual sql statement you want at runtime?

I am currently using the DataReader source component which does not allow that, should I be using the OLEDB Source instead? Overall is the DataReader component a bad choice?

Thank you all in advance!|||There are a variety of reasons your server could run out of virtual memory, with both SSIS and SQL2k5 running it, so a couple of questions first.

1. What OS? is it 32 bit or 64 bit?
2. What is the recovery model of your target database?
3. What destination type are you using? SQL Server destination or OLE DB Destination.
4. If you're using an OLE DB destination, what settings are being used, including DataAccessMode, and fast-load settings if applicable?
5. What is the volume of data your moving through the pipeline?

As for icewill's notion that SSIS via SQL is retriving all the data from the destination table, which may be relived by adding "1=0", that assertion is patently untrue and can be observed not to be the case using profiler.

We have partioned tables with billions of rows as target tables and in no case was all the data selected merely by chosing the table as a target, either during design or run-time.|||Greetings Jaegd,

1) We are using Windows Server 2003 Standard Edition with SP1, 32bit.
2) The recovery model is Simple
3) We are using the OLEDB destination with the data access mode of Table Name variable with Fast load.
4) For any package that is running into problems it occurs if there are more than several hundred thousand rows. I currently don't have a definitive threshold but usually if we are moving more than 400,000 rows we run into a problem.

So far here is what we have discovered through several days of testing;

1) It doesn't appear that SSIS is spooling (writing the stream to disk) as it should. The default setting is to the user's temp directory, we have currently set spooling to go to our E: drive due to a lack of space on our C: drive. We have been testing for a solid week and performance monitor has not reported 1 spooled buffer to date. We currently are profiling all of the SqlServer:SSIS Pipeline variables available in Performance monitor as well as the following general categories; SQLServer:Memory Manager, Processor, Physical Disk (all 3 drives), Memory (Available MBytes), and the .NET Data Provider for SqlServer.

2) We finally found the event handler for the logging of BufferResizing and from that we have gathered that SSIS will try to fill the buffer with the MAX amount of memory first (100Mb) and not the default buffer size of 10Mb. Please correct me if I am wrong but that is what we are witnessing.

One thing that would be great to see in the future is the amount of bytes Sql Server is calculating per row and the amount of bytes it is putting in the stream. This would allow people to gain a better understanding of how to use the setting DefaultBufferMaxRows, rather than just guessing at numbers, which is essentially what we have been doing.

3) Finally the biggest adjustment we have made, and which seems to be working, is to limit the amount of memory that Sql Server itself will use. As I stated in a previous post, Sql Server was using all the memory it could get and then not releasing it, which was killing the capabilities of Integration Services. We changed the setting in Sql Server on this particular box to allow Sql Server only half of the available memory which seems to have fixed the problem. Sql Server now will give back the memory that it has used over that setting which frees up enough for SSIS to run properly.

I would still love to hear from you and others that have experienced this problem or have advice on how to avoid it. The above is just what we have witnessed through our own trials and tribulations.

Thank you for your assistance and experience!|||I have taken an alternative route (which may or may not be viable in your implementation). That route is always running SSIS on separate hardware than SQL2k5 (there are licensing issues here naturally). Note, I'm not recommending SSIS/SQL separation for the general case, by the way, since separation adds substantial unnecessary network roundtrips, as well as eliminates the use of the high speed SQL Server destination.

However, SQL/SSIS separation does allow SQL Server "domination" over the memory/CPU on its hardware. Furthermore, and most importantly, separation allows the construction of an SSIS farm, that is, processing over N number of SSIS only machines.

As for x64 vs x86, generally speaking, running x64 SSIS gives me about a 30% boost (from a wall-clock perspective) over running x86 SSIS on the exact same job, an advantage which becomes greater at larger volume levels.

I know this doesn't speak to the spooling issue, or the optimization of the DefaultBufferMaxRows, since virtual memory has become an non-issue since moving to a hardware separation approach.|||Hi jaegd,

I really appreciate your input! Unfortunately I don't have any control over the hardware that is being used; however I do find it beneficial to know how others are using the product and found your use of it a nugget to hold onto for the future! Thanks again!|||

I have been thinking about creating a SSIS farm have you seen any documention on how to set this up and best practises?

I also installed x64 bit version of SSIS, any ideas how I can confirm this?

cheers

Marcus

Out of virtual memory

I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.

I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.

Getting Error:
[DTS.Pipeline]

Information: The buffer manager detected that the system was low on

virtual memory, but was unable to swap out any buffers. 4 buffers were

considered and 4 were locked. Either not enough memory is available to

the pipeline because not enough is installed, other processes are using

it, or too many buffers are locked

Thank you for your assistance!write a sql command like "select * from destination_table where 1=0",otherwise SSIS will retrieve all data from your destination table.|||Hi icewill,

Not quite sure I understand why I would do that, I do want all the records. Would you please elaborate? We are loading a data warehouse. Thanks.|||Hello there,

I got such problem a while ago. By reducing "DefaultBufferMaxRow" to certain amount (My package was set to 5000) and keep "DefaultBufferSize" as small as possible (default is 10MB, Max is 100MB), your package should run succefully. Please keep in mind that Test and messure could take little bit of time for reaching the best performance.

Good luck|||Hi

Steglitz,

I have tried numerous settings but none of

them successful. I have tried DefaultBufferMaxRow at the following

values 2000, 5000, 7500, 10000 along with DefaultBufferSize set to the

default, 25Mb, 50Mb and 75Mb.

When running

performance monitor the only difference I noticed was the number of

buffers went up as the values were increased. Here

is an article I found explaining buffer

sizing:

Understanding How the Data

Flow Task Sizes the Buffers

The

data flow engine begins the task of sizing its buffers by calculating

the estimated size of a single row of data. Then it multiplies the

estimated size of a row by the value of

DefaultBufferMaxRows to obtain a preliminary working

value for the buffer size.

If the result is more than

the value of DefaultBufferSize, the engine reduces

the number of rows.

|||Here is another issue to add on top of the previous... Once the package runs Sql Server is left holding all the memory to the system. We watched it both through Performance Monitor and through task manager and after an hour it had not released the memory... 1.4 Gb and holding. The only way we could get it to reduce was to restart the Sql Server service. Is there anyone else out there that seems to be having any of these issues?|||

Bagles,

Actually i dont have any idea about why you need data from destination table, i met the problem that you mentioned before, and at last i found if you select a table as your destination directly or write sql like "select * from destination_table", SSIS will retrieve all data from the database, if there is great number of records in your destination table,that will induce your SSIS out of memory, that is not what i want to see, my mainly purpose is to get the fields to map for, so i modified the sql like "select * from destination_table where 1=0",and the problem has been solved perfectly.

GL~

|||Hi Icewill,

Using a query like that to obtain the metadata makes sense, and I suppose this is probably a silly question, but are you then using an expression to inject the actual sql statement you want at runtime?

I am currently using the DataReader source component which does not allow that, should I be using the OLEDB Source instead? Overall is the DataReader component a bad choice?

Thank you all in advance!|||There are a variety of reasons your server could run out of virtual memory, with both SSIS and SQL2k5 running it, so a couple of questions first.

1. What OS? is it 32 bit or 64 bit?
2. What is the recovery model of your target database?
3. What destination type are you using? SQL Server destination or OLE DB Destination.
4. If you're using an OLE DB destination, what settings are being used, including DataAccessMode, and fast-load settings if applicable?
5. What is the volume of data your moving through the pipeline?

As for icewill's notion that SSIS via SQL is retriving all the data from the destination table, which may be relived by adding "1=0", that assertion is patently untrue and can be observed not to be the case using profiler.

We have partioned tables with billions of rows as target tables and in no case was all the data selected merely by chosing the table as a target, either during design or run-time.|||Greetings Jaegd,

1) We are using Windows Server 2003 Standard Edition with SP1, 32bit.
2) The recovery model is Simple
3) We are using the OLEDB destination with the data access mode of Table Name variable with Fast load.
4) For any package that is running into problems it occurs if there are more than several hundred thousand rows. I currently don't have a definitive threshold but usually if we are moving more than 400,000 rows we run into a problem.

So far here is what we have discovered through several days of testing;

1) It doesn't appear that SSIS is spooling (writing the stream to disk) as it should. The default setting is to the user's temp directory, we have currently set spooling to go to our E: drive due to a lack of space on our C: drive. We have been testing for a solid week and performance monitor has not reported 1 spooled buffer to date. We currently are profiling all of the SqlServer:SSIS Pipeline variables available in Performance monitor as well as the following general categories; SQLServer:Memory Manager, Processor, Physical Disk (all 3 drives), Memory (Available MBytes), and the .NET Data Provider for SqlServer.

2) We finally found the event handler for the logging of BufferResizing and from that we have gathered that SSIS will try to fill the buffer with the MAX amount of memory first (100Mb) and not the default buffer size of 10Mb. Please correct me if I am wrong but that is what we are witnessing.

One thing that would be great to see in the future is the amount of bytes Sql Server is calculating per row and the amount of bytes it is putting in the stream. This would allow people to gain a better understanding of how to use the setting DefaultBufferMaxRows, rather than just guessing at numbers, which is essentially what we have been doing.

3) Finally the biggest adjustment we have made, and which seems to be working, is to limit the amount of memory that Sql Server itself will use. As I stated in a previous post, Sql Server was using all the memory it could get and then not releasing it, which was killing the capabilities of Integration Services. We changed the setting in Sql Server on this particular box to allow Sql Server only half of the available memory which seems to have fixed the problem. Sql Server now will give back the memory that it has used over that setting which frees up enough for SSIS to run properly.

I would still love to hear from you and others that have experienced this problem or have advice on how to avoid it. The above is just what we have witnessed through our own trials and tribulations.

Thank you for your assistance and experience!|||I have taken an alternative route (which may or may not be viable in your implementation). That route is always running SSIS on separate hardware than SQL2k5 (there are licensing issues here naturally). Note, I'm not recommending SSIS/SQL separation for the general case, by the way, since separation adds substantial unnecessary network roundtrips, as well as eliminates the use of the high speed SQL Server destination.

However, SQL/SSIS separation does allow SQL Server "domination" over the memory/CPU on its hardware. Furthermore, and most importantly, separation allows the construction of an SSIS farm, that is, processing over N number of SSIS only machines.

As for x64 vs x86, generally speaking, running x64 SSIS gives me about a 30% boost (from a wall-clock perspective) over running x86 SSIS on the exact same job, an advantage which becomes greater at larger volume levels.

I know this doesn't speak to the spooling issue, or the optimization of the DefaultBufferMaxRows, since virtual memory has become an non-issue since moving to a hardware separation approach.|||Hi jaegd,

I really appreciate your input! Unfortunately I don't have any control over the hardware that is being used; however I do find it beneficial to know how others are using the product and found your use of it a nugget to hold onto for the future! Thanks again!|||

I have been thinking about creating a SSIS farm have you seen any documention on how to set this up and best practises?

I also installed x64 bit version of SSIS, any ideas how I can confirm this?

cheers

Marcus

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

Out of Memory

Hi,

Is there a maximum threshold for the objects that I can place in 1 package? I'm encountering an intermittent out of memory in IS. And if I am able to run the project, it hangs the processing of the objects.

any idea how i can resolve this?

cherriesh

Not sure if there is a maximum (maybe one of the MSFT SSIS team could comment), but I've never encountered one. How large is the package? I've found it is better to break really large packages into smaller ones (< 1.5 mb in size).|||ic... mine has reached 10 mb...

Other ways to execute Integration package?

Hi all,
I am just wondering is there any other ways to execute an integration service package other than using sql server agent, dos command and manually execute the package in BIDS? I am thinking of running the package on web, is this possible?
What I am trying to do is let user to run the integration service when they need to, so instead using SQL Server Management Studio to run the task being set in SQL Server Agent, I am thinking of something where user can log into a website (like the report server) and then run the integration, so that the data for the report server's reports are being update. Please help out if any know a solution to it. Thanks in advance.

Daren
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=110020&SiteID=1

Otain error details from a DTS package through T-SQL. Was(xp_sendmail on script

SQL Server 2000

Good day all,

I've been asked to create a DTS package that will execute 2 other DTS packages (yeah, I know... personally I want to write a sp for it *shrug*) and send an e-mail out reporting a failure and failure details.

So I have a couple of questions for you;

How do I pick up the error number (and description?) from a failed DTS and pass it to my sendmail task?
Is it possible to send anything other than plain text e-mails?
Can I change the e-mail priority to "high"?

I appreciate any help you can offer me :)USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
, @.errorNumber int = 0
AS

IF @.errorNumber <> 0 BEGIN
DECLARE @.message varchar(2000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
+ Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
+ Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

EXEC xp_sendmail
@.recipients = '<enter your e-mail address>'
, @.subject = @.subject
, @.message = @.message
END
ELSE BEGIN
PRINT 'No Error'
END
GO

SELECT 1/0

EXEC sp_dtsFailureEmail '<enter dtsName>', @.@.Error

DROP PROCEDURE sp_dtsFailureEmail

I'm getting close, but I still don't know how to pass the error number from a DTS to... anywhere!!|||EXEC xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.@.Error

Returns no error number when the DTS fails... So this suggests that this cannot be done!
Any ideas?|||As you know I know stuff all about DTS but you are not getting much help here.

1) Should you change the title of the thread? The problem does not really have anything to do with xp_sendmail.
2) Have you tried:

Declare @.error_return AS INT
EXEC @.error_return = xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.error_return ??
3) Have you considered setting up a global variable, trapping the error in the DTS script and setting the global. Then test the global when the DTS script has finished.

HTH|||1) Wilco... my q2&3 were sp_sendmail though..

2) I'm just working on that! It returns a bit value declaring whether there was an error or not.

3) Never used global variables in DTS before - will look up if I run into a dead end with point 2.

Here's my current working code.

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
AS

DECLARE @.message varchar(8000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

-- SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
-- + Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
-- + Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

-- EXEC xp_sendmail
-- @.recipients = '<enter your email>'
-- , @.subject = @.subject
-- , @.message = @.message
-- END
GO

DECLARE @.err bit
DECLARE @.dts varchar(64)
DECLARE @.cmd varchar(1024)

SET @.dts = 'test'
SET @.cmd = 'DTSRun /S "SQL-LIVE-IT038" /N "' + @.dts + '" /W "0" /E'

EXEC @.err = xp_cmdshell @.cmd
IF @.err <> 0 BEGIN
EXEC sp_dtsFailureEmail @.dts
END

DROP PROCEDURE sp_dtsFailureEmail

Returns:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147217900 (80040E14); Provider Error: 8134 (1FC6)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
NULL

***DTS FAILURE*** test ***DTS FAILURE***

DTS Package: test
Failure Date: Sep 5 2007 12:16PM

This raises another question: Is it possible to retrieve the output information from xp_cmdshell?|||This raises another question: Is it possible to retrieve the output information from xp_cmdshell?You mean stuff printed to the window? No.

How come? Are you hoping to get the details?

As said I don't know DTS but can you get the details within the package? If so then you can write all this to a table and check that.|||You mean stuff printed to the window?

Yep.

How come? Are you hoping to get the details?

Yep.

Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)|||GeorgeV on DTS: Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)

Strong candidate for my new sig ;)

That, for me, is DTS through and through.|||Yeeeeappp...
I'd love to rewrite the whole script in T-SQL; but can't...

I don't know how to do absolutely everything the DTS does
It's classed as a "major change" and I really cba going through all that just to do it.

A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.|||A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.To be fair unless you write bullet proof dynamic sql or have some sort of config file T-SQL will fair no better. I imagine Pat would start muttering something about "contracts" upon reading this. You change the contract between two systems and you can't really expect things to run smoothly.|||Actually, to resolve this an interim DTS package was created which produces a "clean" file. DTS packages remember the mapping.

The 3rd party product cost a total of $99 - we got what we paid for :p