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?????
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?
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;
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????
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.
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?
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?
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!
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?
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.
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.....
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
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...
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?
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
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???????
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?
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
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
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.
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, '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
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
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.
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.'
