Starting DTS Package From The Stored Proc
Aug 16, 2000Do you know, if there are any ways to start DTS package from the stored procedure?
Thank you,
Anastasia
ADVERTISEMENT
Stored Proc To Run A DTS Package...???
Jan 26, 2005Good day all! :cool:
I have a DTS package that consists of 2 tasks, one empties a table(1) to receive data from a transformation task(2)...
I found 'code' for a stored procedure to run a DTS package.. problem is that it is only running the first task... I need it to run the whole she-bang!
Anyone know how to do this?
Here is the stored proc:
/************************************************** **************************************/
/* Filename: spExecutePKG.sql*/
/* Description:Execute DTS Packages via sp_OAxxxx*/
/* Created:Darren Green 200006013*/
/************************************************** **************************************/
if exists (select * from sysobjects where id = object_id(N'[dbo].[spExecutePKG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spExecutePKG]
GO
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
Stored Proc From A Dts Package
Jan 19, 2004how can a go about running a stored proc when my dts package is finished running?
View 2 Replies View RelatedExecuting A Stored Proc From DTS Package
Oct 1, 2005My current task in my company is...
I got a Spreadsheet from which i have to get the data into a "Staging Table" and from there the data has to go into 10 different Tables in my database..
I had created a DTS package using ActiveX script and written a Stored Proc to get the records from Staging table to Database tables...every thing is working fine... but...
they asked me to send "TapeType' and "DealName" into two fields and "current datetime" into one more field of the stagingtable fields which are not present inthe Spreadsheet....
so in the Activex script i hard coded... destination"TapeType"= "BidTape" and destination"DealName"= "ABCD" and its working...
and for currentdate i had defined a DTS Task and in that i had written a SQL stmts..
update table....
set currentdate = getdate()
where TapeType = 'BidTape' ..... which is also working....but the problem is
if i got one more spread sheet with same "TapeType" i.e. BidTape but with different "DealName" i.e. "XYZ".... every thing is working out but....the Currentdate field is getting updated every where with as one date..???
can u suggest me a better solution here.....
and after this Iam planning to create one more Task to Execute the Proc..... but I have to pass 2parameters i.e. "Dealname" and "TapeType"... they want every thing to be get done with single mouse click...
can u suggest me here how to pass these 2parameters in the DTS package,..
I will be waiting for any one your valuable suggestions....
HELP!! HOW TO CALL A DTS PACKAGE FROM A STORED PROC
Jul 20, 2005I'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny
View 1 Replies View RelatedIs It Possible To Execute Package Through Stored Proc
Jul 7, 2006is there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas
Unable To Run SSIS Package Using Stored Proc
Apr 16, 2007Hi all,
I have created an SSIS package with protection level - EncryptSensitiveWithUserKey. It is running alright from Visual Studio Environment but i need to call the package from an application, so i created a stored proc to call it, but getting the following error while running the stored proc:
Error: 2007-04-16 17:55:05.78
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
any solution?
HOW TO EXECUTE THE PACKAGE IN STORED PROC PLZZZZZZZZZZZZZZZZZZZZZZZZZ
Aug 6, 2007My Package Exist in
C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code
Regards
koti
Looping A Package Based On A Return Value From A Stored Proc?
Oct 24, 2007Hi,
I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.
I know that in DTS you could use the return value of "execution status" to do this.
Is there a way in SSIS to loop based on a return value?
Thanks much
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Stored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedSQL 2012 :: DTSX Package - Starting And Stopping A Job
Feb 26, 2015I have a job that runs a dtsx package (containing an infinite loop). What I want to do is set up a schedule to start the job at 8am and stop it at 5pm. The scheduling GUI doesn't give this kind of option (caveat: that I can see).
View 3 Replies View RelatedDelay Starting SSIS Package Via SQL Agent
Feb 20, 2008Hi
All my SSIS packages consistently take 1 minute to start when they are scheduled via the SQL Agent.
For example, a package runs in 20 seconds in the BI Studio. I transfer it to the SQL server and store it in MSDB. I then run it manually from SSMS - Integration Services and it takes 20 seconds. I then schedule it via a job in the SQL Agent and it takes 1 minute 20 seconds.
I can see from some simple logging that there is consistently a delay of 1 minute between the job starting and the package starting.
I have also switched every occurrence possible of DelayValidation to TRUE in all my packages and tasks. All this did was reduce the package run time from 27 seconds to 20 - the 1 minute delay still exists.
This happens on all my packages on all my servers. Any ideas.....?
Thanks
Delay In Package Starting When Running From SQL Agent
Jul 19, 2006Hi,
I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.
The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.
When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.
I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.
Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.
Thanks,
Sam
Analysis :: Errors In SSAS Dimension Prevent From Starting / Debugging SSIS Package
Jul 24, 2015I have an SSIS and SSAS project in the same solution. I need to debug the SSIS package regardless if there is an error or two in the SSAS project. Is there a way to ignore the SSAS project while I debug the SSIS package?
View 2 Replies View RelatedCalling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I Had Set My Windows Service's Startup Type As Automatic But It Is Not Starting Up Automatically When The System Is Starting.
May 18, 2007Hello,
I had created a windows service using C#. I set its Startup Type as Automatic but it is not getting started automatically when my System Starts.
Starting Out With : Views, Stored Procedures, Functions, Synonyms
Mar 22, 2006Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
View 1 Replies View RelatedDifference In Starting Rep Service Vs. Stopping And Starting The Rep Job?
Feb 1, 2007When our rep distribution services time-out (several times a day) some dba's just restart the service while others restart the rep job which starts the service. Are there any differences between these two methods? Does restarting the service inherit any changes made to the job's attributes?
Thanks!
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedExec Proc From DTS Package
Mar 6, 2006Hi
I'm trying to execute a Proc from DTS Package and it is rasing an error 'Invalid Pointer'.
This Proc is used to get a result set and I want to transfer this result set into a Excel File and that Excel File is to be sent to my client by Email.
Every thing was fine....but can some one suggest why I'am getting this error 'Invalid Pointer'.
:confused:
Exec DTS Package From Store Proc In SQL 2K
Nov 8, 2000What command do you use to run a DTS package from a stored procedure....
The XP Copy is not working???
Thanks,
~Lee
How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
SQL Agent Is In Starting Status - But Not Starting
Mar 6, 2002We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .
MDDB database is accessable & in Log file its recovered fully
Thankx in Advance
Sha
Calling A Stored Procedure From Within A Stored Proc
Dec 18, 2007Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
Under Which Filegroup Are Stored Proc. Stored?
Aug 23, 2007When you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
Mar 10, 2005I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
Sql And Stored Proc
Jul 19, 2007Im trying to perform an update with a stored procedure thats all working but
an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key
I understand whats going on but how do you update some ones details if its protected this way.
?