I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
I have ssis package which is credated by VS-2010.
I want execute this SSIS package from the stored procedure (SQL server 2005).
I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:
PROCEDURE Interface_Begin
(p_from_dttm OUT varchar2,
p_error_code OUT number,
p_error_text OUT varchar2,
p_proc_name OUT varchar2);
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
I would like to call a oracle stored procedure which looks like this
Create procedure in oracle sql plus
CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
FROM Groups
ORDER BY GroupName;
END;
END;
/
Execute procedure in oracle sql plus
set autoprint on;
var x refcursor;
groupspackage.usp_selectgroups(:x);
end;
/
This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.
I have developed an SSIS Package which uses an ODBC connection to an AS400 iseries stored procedure. I use an Execute SQL Task. The query is Call Doctrack.PubFeed(?,?,?,?). The procedure takes 2 input parameters and 2 output parameters (3rd and 4th parameters) The data types of the output parameters are an integer and varchar.  As part of the procedure data is inserted into a table on the iseries.Â
When I run the package using breakpoints to view the values of the variables I see that the stored procedure returns values for the output parameters and the execute SQL task is a success and proceeds to the next task in the package. The whole package ends successfully.However, when the table on the iseries is checked nothing has been inserted into it. To test further, I manually run the procedure on the iseries using the same parameters. The run is successful. And when the table is checked, there are in fact new rows inserted.
What can possibly be the issue since I am not getting any errors when I run the package? Oh I should add that prior to the execute Sql Task, there is a data flow task which moves data from a SQL Server database to a table on the iseries (successfully) using the same ODBC connection. The execute sql tasks uses that information for the Stored procedure.
I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.
and I want to use SSIS -OLEDB connection.
Â
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
cm = Dts.Connections["OLE_TEST_"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn);
sqlComm.ExecuteNonQuery();
above code , no message box.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies View RelatedHi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies View RelatedGreetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you
I am having few queries related to follow.
1. Is it possible to call SSIS package from procedure .
2. Also want to supply parameters to procedure.
3. Can multiple users execute that procedure simultaneously.
4. If yes then will it cause any issue if it is run simultaneously by 10 users.
Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
Dear Masters;
How can I call a stored procedure with VB code?
Thanks
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
View 2 Replies View RelatedCan DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies View RelatedI'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):
-- Decalre variables
DECLARE @object INT
DECLARE @hr INT
DECLARE @property VARCHAR(255)
DECLARE @return VARCHAR(255)
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
-- Create object
EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT
-- Call method
EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT
-- Destroy object
EXEC @hr = sp_OADestroy @object
Hello;
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Here's my code;
Function LieuBen()
MyCalendar = CurrTSCalendar
Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")
End Function
Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String
Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
End Function
Hello all,
does anyone know if it's possible to call a stored procedure from a view.
Thnx,
Patrick
How to call a sql function in stored procedure using Sqlserver 2008?
View 4 Replies View RelatedI have created a DTS package which stores file data into Database table.
I want to pass file name dynamically to stored procedure from which DTS retrieve data and store it in table.
Hi.....
I have problem and I need your help
I stored a procedure in the Projects Folder in my computer
and I want to return the procedure result in a column inside table
How I can do that?????????
thank you