Stored Procedure DTS Package Partial Failure
Jul 22, 2005
I am running a DTS Package from a stored procedure using xpcmdshell. The DTS Package begins with a SQL Task to delete records from 2 tables (this works fine), but the data transfer task for importing records from a SQL Anywhere 5.0 database gives me the error 'Unable to connect to database server: Unable to start database engine'. the weird thing is that from Enterprise Manager I can execute the DTS Package and it works fine. What am I missing here?????
thanks
dzap1
View 10 Replies
ADVERTISEMENT
Jan 16, 2007
I have a package that is failing because of a truncation error. Now, by default (and I leave this for ALL my packages) if one row fails processing the entire package should fail and nothing gets loaded into db. But instead I am actually getting a partial db load.
I have confirmed the "Rows per btach" value (blank) and the "Maximum insert commit size" value (0) for the OLE DB Destination Editor so I have no idea what is going on. Are there any other properties I should be checking?
Thanks.
Jason
View 6 Replies
View Related
Jan 9, 2006
Hi,
I have some c# code which calls a SP which is erroring Basically I pass in a XML string which can be upto 5 MB is size (not sure about overflow issues here), which then calls a SP which inserts the data into a SQL table.
The c# code is as follows:
-------C#----------------------
SqlConnection conn = new SqlConnection(DBConn);
using(StreamReader sr = new StreamReader(xmlLocationString))
{
try
{
string @xmlInput = sr.ReadToEnd();
SqlCommand cmd = new SqlCommand();
cmd.Connection=conn;
cmd.CommandText = "[AddArgentinaTrades]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@xmlInput", SqlDbType.Text, 5120000));
cmd.Parameters["@xmlInput"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
}
catch(SqlException SqlExp)
{
Console.WriteLine(SqlExp.Message);
}
finally
{
conn.Close();
sr.Close();
}
}
------------------Stored Proc-----------------------
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddArgentinaTrades' AND Type ='P')DROP PROCEDURE AddArgentinaTradesGO
CREATE PROCEDURE AddArgentinaTrades@xmlInput as textAS
Declare @idoc int
EXEC master.dbo.sp_xml_preparedocument @idoc OUTPUT, @xmlInput
INSERT INTO MarketRiskdev.dbo.Import_ArgentinaSELECT un_cid, tnum, snum, cid, entityid, ctype, why, comp, oc, bs, ae, cp, trd_date, set_date, mat_date, val_date, trader, famt, price, coupon, next_coupon, last_coupon, cpnfreq, cpnrate, cpntype, daycounttype, exch_notion,contract_spot, base_cur, year_basis, buy_currency, buy_amount, sell_currency, sell_amount, [timestamp] FROM OPENXML(@idoc, 'ArgentinaInputFile/Data',2)WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50), why varchar(50), comp varchar(50), oc varchar(50), bs varchar(50), ae varchar(50), cp varchar(50), trd_date datetime, set_date datetime, mat_date datetime, val_date datetime, trader varchar(50), famt float(8), price float(8), coupon float(8), next_coupon datetime, last_coupon datetime, cpnfreq int, cpnrate float, cpntype int, daycounttype smallint, exch_notion smallint, contract_spot float(8), base_cur varchar(50), year_basis int, buy_currency varchar(50), buy_currency varchar(50), buy_amount float(8), sell_currency varchar(50), sell_amount float(8), [timestamp] varchar(50))
EXEC master.dbo.sp_xml_removedocument @idoc
GO
Error Msg:
A severe error occurred on the current command. The results, if any, should bediscarded.
Can anyone help here as I have no idea. I have tried reducing the size of XML to 5KB and still get the same error????
View 1 Replies
View Related
Apr 8, 1999
All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.
Thank you,
Scott Kolek
Development Manager
SKM Software
http://www.skm-software.com
View 3 Replies
View Related
May 29, 2006
Hi,
I figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using
Declare
Begin
SomeStoredProc(?,?,?);
End;
with an OLE DB connection using the Oracle Provider for OLE DB.
The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.
I couldn't get it to work as a function with a return value. :(
Is there another way to execute an Oracle stored procedure that I missed?
Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?
Thanks
John Colaizzi
View 4 Replies
View Related
Mar 11, 2008
I have an SSIS package that executes a stored procedure. In that stored procedure is a try/catch block. If the try isn't successful, it goes to the catch block which does a rollback. So when I execute the SSIS package, it tells me that the stored procedure was ran successfully because there essentianlly were no errors and everything ran fine, but in reality, everytime it goes into the catch block and does a rollback, I want the SSIS package to fail as well. How would I send back a failure to the SSIS package from the stored procedure?
View 13 Replies
View Related
May 19, 2008
Hi all,
I am wondering if you guys have any experience with failing Stored Procedures running inside a SSIS package with the following error:
=====================================================
Message
Executed as user: GAALPSVR034FSYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:51:22 AM Error: 2008-05-19 09:06:55.15 Code: 0x00000000 Source: usp_SLIM_Site_PreProcess Description: TCP Provider: The specified network name is no longer available. End Error Error: 2008-05-19 09:06:55.18 Code: 0xC002F210 Source: usp_SLIM_Site_PreProcess Execute SQL Task Description: Executing the query "Exec usp_SLIM_Site_PreProcess" failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:51:22 AM Finished: 9:06:55 AM Elapsed: 932.203 seconds. The package execution failed. The step failed.
======================================================
Sometime it fails when gets called through a job and sometimes even when a package is open in a design mode. The actual Stored Procedure NEVER fails if called in SQL Server Management Studio.
Any ideas or suggestions will be very helpful and appreciated
Thanks for your help!
Jacob
View 3 Replies
View Related
Mar 6, 2008
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
View 5 Replies
View Related
Feb 2, 2007
Hi,
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
View 1 Replies
View Related
Jul 12, 2006
I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?
Please help me, thanks a lot
View 7 Replies
View Related
Aug 10, 2007
I created a dts package and I can execute it.
I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.
I have sql integration services and mssql 2005 services running under a domain account.
I have saved the package as a FILE System stored package.
I just can't find a reason why it won't execute from stored procedure.....
Ron
View 4 Replies
View Related
Apr 23, 2007
Hello All,
I have a SSIS Package with a "Execute Package Task" to call a child package. I am trying to have the master/parent package complete its execution regardless the outcome (failure or success) of the child package. The overall structure of the master package is:
1. Perform Pre-load tasks (stored procedure).
2. Execute Package Task (call child package)
3. Perform Post-load Tasks (stored procedure)
I have try everything and cannot get the results that I want... I have tried the combination of "failparentonfailure", "forceexecutionvalue", etc. The master package stops at childs failure. I would like to resume to completion
Any ideas out there?
Rohan
View 10 Replies
View Related
Jan 15, 2001
I have a DTS Package to populate data from excel sheet to direct simple table. I like to call this DTS from User interface. thanks in advance
View 2 Replies
View Related
Aug 30, 2000
I have a DTS package written to import data from a txt file into a SQL Server 7.0 table
but before importing the data i need to delete the existing data from the table...
for this i need to use the stored procedure to delete data first...
how do i run the dts package from the stored procedure..does anyone know the command for this...
regards,
reshma
View 2 Replies
View Related
Jun 27, 2001
When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.
When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.
I'm trying to automate this package so it will run after a field in a table has been updated.
Any suggestions are more than welcome.
PS. Here's the code I've been using (that doesn't seem to work):
--- Declare Variables
Declare @hr int
Declare @oPkg int
--- Create Package Object
Exec @hr = sp_OACreate 'DTS.Package', @oPkg OUT
If @hr <> 0
Begin
Print '*** Create Package Object Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256
Exec @hr = sp_OAMEthod @oPkg,'LoadFromSqlServer("JENNSERVER", "", "", 256, , , , "RunMS")',null
If @hr <> 0
Begin
Print '*** Load Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Execute Package
Exec @hr = sp_OAMethod @oPkg, 'Execute'
If @hr <> 0
Begin
Print '*** Execute Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print '*** Destroy Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
View 2 Replies
View Related
Jan 18, 2007
I need to automate the following function. I know I can do this in .NET but I need to have a DTS package setup so it executes every evening.
I have a view that produces a list of sales errors. I want to grab the SaleID from each record and insert a record into a "Current Queue" table.
Here is another example of what I'm trying to do...
SELECT SaleID FROM vw_SalesErrors
- with the results of the SQL statement above
INSERT INTO tbl_QueueRecords
(SaleID, QueueID)
VALUES
(@SaleID, 14)
I'm assuming there is a way to automate this into a single stored procedure or at least a DTS pacakge.
Thanks for all of your help.
View 2 Replies
View Related
Jan 6, 2004
Hi everybody,
I would like to ask that how can I execute a package from a stored procedure.
Laci
View 2 Replies
View Related
Sep 22, 2006
Hello,I have a stored procedure that processes an individual file from adirectory and archives it in a subdirectory.Now, the problem is, when iexecute it , it will only process one file. What i want to do is to checkto see if there are any files in the folder, and if there are , processthem all, and once done, go to the next part in a DTS package, if there areno files, simply go to the next part in the DTS package. I tried an activexscript that would get the filecount in the folder, and if there were morethan 0 files in the folder, then DTS-sUCCESS and on "success" workflow , itwould run the stored procedure, and thus it woould process one file, then"on completion" the workflow connected it back to the activeX script(thuslooping), which would count the files again. Now if there were 0 files, itwould report DTS_FAILIURE, and I had it set up ,"on failiure" to go to thenext step in the package, but it wouldn't run.Someone mind showing me a ray of light?
View 1 Replies
View Related
Jul 13, 2007
Hi,
I wanted to know if i can a DTS package using a stored procedure and if yes how should i do it.
Regards,
Karen
View 1 Replies
View Related
Jul 14, 2000
I have created a DTS package. I want to know how to call/run this package from a stored procedure.
Please let me know how to call this DTS from a Stored Procedure.
Thanks.
-Rajesh
View 1 Replies
View Related
Feb 22, 2007
Hello friends!
I have one query regarding execution of SSIS package through Stored Procedure.
I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters
when i searched on line i got this solution
Declare @FilePath varchar(2000)
Declare @Filename varchar(1000)
Declare @cmd varchar(2000)
set @FilePath = 'C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Datain'
set @Filename = 'DataExtract.dtsx'
select @cmd = 'DTExec /F "' + @FilePath + @Filename + '"'
print @cmd
exec master..xp_cmdshell @cmd
but when i execute it i got error like
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2007-02-22 11:31:37.32
Code: 0xC0011002
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin
g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????
Please help me out
Thanks
View 20 Replies
View Related
Mar 28, 2008
I'm trying to create a stored procedure which will run 2 SSIS packages before it runs some other SQL code. I read [url=http://msdn2.microsoft.com/en-us/library/ms162810.aspx]this[/url] article. I'm trying to use the package from the file system.
Here is the my code:
CREATE PROCEDURE usp_participant_limits_report
AS
dtexec /f "C:....Activity_Limits.dtsx"
GO
The error message says it doesn't like the "/". Anyone?
View 1 Replies
View Related
Apr 4, 2006
The DTS package runs fine through Enterprise manager successfully.However, when scheduled through a job that runs the dts through thefollowing code:DECLARE @findfile intExec @findfile = master.dbo.xp_cmdShell 'dir\ServerNamefolderfilename.xls', no_outputIF (@findfile=0)BEGINExec master.dbo.xp_cmdshell 'dtsrun -E -ServerInstance-N"DataImport"'ENDThe servername specified in the above statement in a different serverthan the server that the package resides on.This is the error that I get when I try to run the same code usingquery analyzer:DTSRun: Loading...DTSRun: Executing...DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147024893(80070003)Error string: The system cannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 1100Error Detail Records:Error: -2147024893 (80070003); Provider Error: 0 (0)Error string: The system cannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 1100Error: -2147024893 (80070003); Provider Error: 0 (0)Error string: Cannot open a log file of specified name. The systemcannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 4700DTSRun OnFinish: DTSStep_DTSDataPumpTask_1Error: -2147220440 (80040428); Provider Error: 0 (0)Error string: Package failed because Step'DTSStep_DTSDataPumpTask_1' failed.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 700NULLThe job is owned by the SQLService account(Windows account) that hasSystem Admin rights and also is part of the Domain Admin User group.The Domain Admin User group has full rights on the file that the DTS istrying to access.Any help in trying to figure out why the schedule job cannot find thefile path would be appreciated.ThanksKR
View 1 Replies
View Related
Jul 20, 2005
Hello,I'm currently working on debugging a very large DTS package that wascreated by someone else for the purpose of importing data into mycompany's database. The data is mainly user/contact-related data forour customer base.We ran into problems when one import, of about 40,000 rows, tookupwards of six hours to complete. Many of the stored procedures usedby this package were written using XML. I've re-written many of themusing native SQL to see if that improves the performance, but I'mgetting some errors that I haven't been able to diagnose.Instead of asking about my specific errors, I'd like to know moregenerally what ways are there to debug DTS packages and storedprocedures? I'm aware of, and experienced with SQL Profiler but it'snot giving me the info I need. I need the ability to see exactly whatvalues are being passed to every call to a stored procedure fromwithin the DTS package or another stored procedure.I've used it very successfully to debug .asp, .aspx, .vb and the like,but right now I'm running it while running this huge stored procedurethat is called by the DTS package and does the lion's share of thework, including multiple updates and inserts into about 10 tables.The problem is, I see the calls to the "sub-procedures" from the mainone, but I can't see the values of any of the input or outputparameters. Instead ofInsert_Contact 'John', 'Q', 'Smith', '333-333-3333'......etc.I seeInsert_Contact @FirstName, @Initial, @LastName, @PhoneNumber......etc.My trace includes Stored Procedure events:RPC: CompletedRPC: StartingSP: StartingSP: StmtCompletedSP: StmtStartingand TSQL:Exec Prepared SQLPrepare SQLSQL: BatchCompletedSQL: StmtStartingI figured with these I would've covered the bases but I don't see anyof the parameters, which is critical for my debugging, as some of themare not being properly set.Any ideas or help would be greatly appreciated!TIA,Mike
View 4 Replies
View Related
May 24, 2006
In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.
Thanks,
George Cooper
View 3 Replies
View Related
Mar 8, 2006
Hey guys,
I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure.
I thought sp_OA family of extended procedure would be helpfull. After following steps:
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
EXEC @hr1 = sp_OAMethod @oPKG, 'LoadFromSQLServer("foo", ,, 256, , , , "foo1")', NULL
EXEC @hr1 = sp_OAMethod @oPKG, 'exec'
EXEC @hr1 = sp_OADestroy @oPKG
it tells me command execute successfully. But no package actually gets executed and I can see no results
Thanks
View 1 Replies
View Related
May 21, 2008
hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance
View 1 Replies
View Related
Jul 22, 2005
how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.
View 23 Replies
View Related
Jul 5, 2006
I have a site that will run a DTS package from a stored procedure when a user pushes a button. I know the stored procedure works and exectures. I need the .net code on how to execute this particular type of SQL statement.
Any help would be AWESOME.
THX
This is the stored procedure stored in EM:
CREATE PROCEDURE spExportData AS
Begin TransactionSet nocount onEXEC master..xp_cmdshell 'dtsrun /Ssql-06-dba /NAdultExport -E'CommitGO
The code below is what I am currently using and is not working.Protected Sub btnUpdateList_Clicked(Sender As Object, E As System.EventArgs) Dim strProc As String = "spExportData" Dim MyConnClass as New SQLConns Dim sqlConn as SqlConnection Dim myCommand As New SqlCommand sqlConn = MyConnClass.OpenConn("ConnAdultEd") MyCommand.CommandType = CommandType.StoredProcedure MyCommand.CommandText = strProc MyCommand.Connection = sqlConn MyCommand.ExecuteNonQuery() MyConnClass.CloseConn(sqlConn) statuslbl.text = "File updated" End Sub
View 1 Replies
View Related
May 18, 2015
I have used BCP to perform this, but I now need an SSIS package. Is this possible to use an SSIS package to automate the task?
View 1 Replies
View Related
Jun 5, 2014
I have a really big stored proc that needs to be rolled out to various databases as part of db installs I run through SSIS.
The Stored proc is too long to run using Execute SQL Task. Is there another way that just running the create script manually.
View 9 Replies
View Related
Jun 25, 2014
We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:
mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.
View 6 Replies
View Related
Apr 6, 2015
I've created this stored procedure to run two DTS packages which pull in data from two Excel files. I'd like to supress the Results tab since the results from the DTS packages are fed to it. I'd like to keep the Messages tab visible.
CREATE PROCEDURE [dbo].[ImportHelpDeskTickets]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
[Code] ....
For those of you like me who will spend 8 hours trying to figure out how to get this working... Double up on the quotes as shown above. Also, be certain that NT SERVICEMSSQLSERVER has been granted appropriate rights to the folders with the the saved packages and the Excel files. You may also need to download the Access Database Engine.
Keywords: run dts package from stored procedure xp_cmdshell
'C:Program' is not recognized as an internal or external command, operable program or batch file.'
View 3 Replies
View Related
