Integration Services :: Unable To Get Return Code Executing SSIS Package From Stored Procedure?

Jun 11, 2015

We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all.  After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.

 SELECT @cmd = 'dtexec /FILE "D:Program FilesMicrosoft SQL Server100DTSPackages.....PopulateReport.dtsx"'          
  SELECT @cmd = @cmd + ' /Decrypt T@!0er '          
  SELECT @cmd = @cmd + ' /set package.variables[vAppID].Value;' + CONVERT(VARCHAR(10),@appId)          
  SELECT @cmd = @cmd + ' /set package.variables[vDBName].Value;' + '"' + @db + '"'          
  SELECT @cmd = @cmd + ' /set package.variables[vBuildMFF].Value;' + CONVERT(VARCHAR(10),@BuildMFF)          

View 6 Replies


Integration Services :: Executing Child SSIS Package In Parent SSIS

Oct 9, 2015

I want to achieve the following in (SSIS/SSDT for SQL 2012) - 

I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).

I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.

I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?

When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.

View 8 Replies View Related

Integration Services :: Pass Multiple Parameter Values To SSIS Package In 2012 Through Stored Procedure?

Jul 9, 2015

we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..

Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .

1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??)
2.What are the options to pass multiple parameter values to ssis package through stored procedure.?

View 4 Replies View Related

Executing SSIS From Stored Procedure - Need To Get Return Value Into ASP.NET

Aug 23, 2007

I'm executing an SSIS package using the following stored procedure


DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)

SET @ReturnValue = -1
SET @ServerName = 'myserver'

SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
' /SQL ' + 'BuildSCCDW ' --Location of the package stored in the mdb
--' /CONF "\ConfigFilePath.dtsConfig" ' +
--' /SET Package.Variables[ImportUserID].Value; ' +
--' /U "LoginName" /P "password" '

EXECUTE @ReturnValue = master..xp_cmdshell @Cmd, NO_OUTPUT
RETURN @ReturnValue
--SELECT @ReturnValue [Result]

 I'm then using a tableadapter to execute this from my ASP.NET page using the following code, Protected Sub ExecutePackage()
Dim ExecuteAdapter As New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter()
Dim strResult As String
strResult = ExecuteAdapter.Execute()
lblResult.Text = strResult
End Sub
 If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.
When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @ReturnValue and SELECT @ReturnValue to no avail.
Can someone suggest how I can get the value 0-6 to my code?

View 1 Replies View Related

Integration Services :: SSIS Package - Create Different TXT File For Each Code

Jun 25, 2015

EDate Datetime,
Code varchar(255),
Cdate int,
Price int
drop table Test

[Code] ....

I have this Query and the below output:

EDate Code CDate Price
2015-06-24  RX 20150701 22
2015-06-24  RX 20150701 28
2015-06-24  RX 20150701 43

[Code] ....

Now the task is to create  SSIS package which will create different .txt file for each Code

1) RX20150624.txt
2015-06-24 00:00:00.000 RX 20150701 22
2015-06-24 00:00:00.000 RX 20150701 28
2015-06-24 00:00:00.000 RX 20150701 43

2) NG20150623.txt
2015-06-23 00:00:00.000 NG 20150701 43

3) HO20150624.txt
2015-06-24 00:00:00.000 HO 20150701 43
And so on..

But the requirement is to have a dynamic query where we can have more number of Codes or less number of codes and similarly the package should generate dynamic text files, one .txt file per code. What is the best way to create a package which can meet the above requirement?

View 6 Replies View Related

Stored Procedure To Executing An SSIS Package

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.


George Cooper

View 3 Replies View Related

Executing SSIS Package From Stored Procedure

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

View 1 Replies View Related

Integration Services :: Oracle Procedure Is Not Returning Results When Executing From Script Task On SSIS

Jul 13, 2015

I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.

Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim QSQL As String


View 10 Replies View Related

Problem Executing Ssis Package Through Stored Procedure

Mar 11, 2008

I have an ssis package which reads a file and upload the data into a table.
Im executing this package through Stored procedure through dtexec /F command
If im uploading this file from local machine the package is executing and the data is uploaded to the table.
If it is in network and try to upload it will through an error that can't upload the file..
I have also shared the file on the network.

Can anyone help

View 9 Replies View Related

Integration Services :: SSIS Package Fails When It Is Executed By Procedure

Mar 31, 2015

I have a very anoying problem with SSIS. I've done new packages into same project, almost identical compared to old ones. They work well in visual studio, but I can't execute them using procedure. Old packages works just fine, but none of the new. Error message is in the end of my story.Visual Studio version is 9.0.30729.1 and SQL Server version is 10.0.4000.0. Is it possible, that these not not updated versions could cause this problem?Package ProtectionLevel is DonSaveSensitive.

Error messages, when package is executed by procedure:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  10:36:48 AM
Error: 2015-03-31 10:36:48.48
   Code: 0xC0016016


View 11 Replies View Related

Invokation Of A Stored Procedure From An Integration Services Package

Jun 9, 2006

Is it possible to execute a stored procedure from an Integration Services package? I see that its possible to enter sql commands that can be run but when a command to execute a stored procedure is entered the system cannot find the stored procedure (eventhough 'use mydbname' preceded it.



View 1 Replies View Related

Integration Services :: Creating A File Using Stored Procedure In SSIS

Jun 24, 2015

I'm trying to create a file using a stored procedure with SSIS.  I've tried to use the Execute SQL Task, but it will not create a file nor output to that file.  I'm using "Full result set", but I don't know how to sent the result to the file.  Is there another Control Flow Item I need to use?The reason why I'm using SSIS and not SQL Server Agent is because the file name must contain a timestamp.

View 3 Replies View Related

Integration Services :: Unable To Load Package As XML Because Of Package Does Not Have Valid XML Format

May 11, 2015

i have created one package in production server called User_Import,It will fetch the info from excel file to the Sql  table, I have executed this package in ssis console successfully,But i have to schedule one job using this package on daily basis for that i have created on sql job using this package, Then it is failing i dont know the exact problem,I have full access to my database and full access to the sql agent to exuete any jobs,I have sharing the error message which am getting in the sql agent level, Please find the error msg:

05/11/2015 15:10:20,User_Imports,Error,1,SFRFIDCSCDB003PSQCM03,User_Imports,AD_User Load,,Executed as user: SFRSA-SFR-SQCM-02. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  15:10:20  Error: 2015-05-11 15:10:20.41     Code: 0xC0011007     Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B}  

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: 2015-05-11 15:10:20.41     Code: 0xC0011002     Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B}

Description: Failed to open package file "C:UserssccmadminDocumentsVisual Studio 2008ProjectsUser_ImportsUser_ImportsUser_Imports.dtsx" due to error 0x80070005 "Access is denied.".  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.  End Error  Could not load package "C:UserssccmadminDocumentsVisual Studio 2008ProjectsUser_ ImportsUser_ ImportsUser_ Imports.dtsx" because of error 0xC0011002.  

Description: Failed to open package file "C:Userssccmadmin DocumentsVisual Studio 2008 Projects

User_ImportsUser_ImportsUser_Imports.dtsx" due to error 0x80070005 "Access is denied.".  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.  Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B}  Started:  15:10:20  Finished: 15:10:20  Elapsed:  0.015 seconds. The package could not be found.  The step failed.,00:00:00,0,0,,,,0

View 4 Replies View Related

Integration Services :: Tell A Package To Stop Executing On Failure

Sep 8, 2015

I am using SQL Server 2012 SP1. I have built an SSIS package that imports flat file data from various files to SQL Server.  I have got it to do everything I want it to do when things are going well, and am now on what I want it to do when it encounters a failure executing specific tasks and containers.  For example, I have a Foreach Loop container that executes a dedicated stored procedure for each csv file in the target folder. If any of the store procedures fail to run for any reason I want to carry out certain actions.

For the most part I think I will be fine using the Event Handlers.  What I can't seem to find is how to tell the package to stop executing on a Failure event after carrying out the actions defined by the relevant Event Handler. Or, perhaps it isn't necessary as that would be the default behaviour on a failure?

View 2 Replies View Related

Integration Services :: Using SSIS To Call A Stored Procedure On AS400 Iseries DB2?

Jun 4, 2015

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.

View 6 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008

I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.

CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output


Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

Integration Services :: Master Package Is Executing Upto Certain Point Only

Jul 31, 2015

I have a sql agent job which runs the Masterpackage, Master package has aroung 35 child packages calling in it. Issue with this Master package is it running upto 30 child packages and I get success message on  job. It is not executing packages beyond 30 what could be the problem. I disabled the all the 30 child packages and ran it again it ran those other 5 child packges without any problem.

View 2 Replies View Related

Integration Services :: Commit N Number Of Records (SSIS Versus Stored Procedure)

May 8, 2015

I have a stored proc that is returning the results I need for output to .txt file.

Is there a way in SSIS to commit 50K (or whatever number) row batches at a time or should I just handle this in the stored proc?

select * into #TempTable
from SomeTable
[WHILE LOOP] --throttle commit batches of 50K rowcount
select *
from #TempTable
drop table #TempTable

But If I'm doing this in SSIS, I can't drop the #temp table otherwise I have nothing to output right?

View 6 Replies View Related

Integration Services :: Executing SSIS Hangs

Apr 29, 2015

I created a SSIS package which loads CSV into database. The package is called in a C# console application which is set as scheduled task in a server. The problem I having is that the package hangs during validation stage: "Truncation may occur due to inserting data from data flow column "Reading Type" with a length of 50 to database column "ReadingType" with a length of 2."There is no problem loading same data from development machine.

View 5 Replies View Related

Integration Services :: Executing SSIS By Clients

Aug 12, 2015

I've developed SSIS package and able to Launch and Execute dtsx package using Web Service/Console application. If I run it from my local it has no issues. If I ask my client to run this web service it is throwing error "Unable to connect to remote server".

If I ask my client to directly run the package itself they get following error "AcquireConnection method call to the connection manager failed"...

Even after my client having SQL Server, Integrations Service and BIDS installed on his machine he gets the above error. How to set this up.  SQL Sever Agent is not an option for us. 

View 2 Replies View Related

Integration Services :: Call Stored Procedure Result In Message Box In SSIS Script Task

Sep 4, 2015

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);

above code , no message box.

View 2 Replies View Related

Executing An SSIS Package Containing Analysis Services From ASP.NET

Jul 17, 2007

I'm trying to execute an SSIS package from an ASP.NET web page. Using some code from I have managed to get this working when executing a package that contains a stored procedure, however it does not work when there is an Analysis Services cube to build.

Here is my code for the webservice

Code Snippet

Public Class SCCBuildDW

Inherits System.Web.Services.WebService

' LaunchPackage Method Parameters:

' 1. sourceType: file, sql, dts

' 2. sourceLocation: file system folder, (none), logical folder

' 3. packageName: for file system, ".dtsx" extension is appended


Public Function LaunchPackage( _

ByVal sourceType As String, _

ByVal sourceLocation As String, _

ByVal packageName As String) As Integer 'DTSExecResult

Dim packagePath As String

Dim myPackage As Package

Dim integrationServices As New Application

' Combine path and file name.

packagePath = Path.Combine(sourceLocation, packageName)

Select Case sourceType

Case "file"

' Package is stored as a file.

' Add extension if not present.

If String.IsNullOrEmpty(Path.GetExtension(packagePath)) Then

packagePath = String.Concat(packagePath, ".dtsx")

End If

If File.Exists(packagePath) Then

myPackage = integrationServices.LoadPackage(packagePath, Nothing)


Throw New ApplicationException( _

"Invalid file location: " & packagePath)

End If

Case "sql"

' Package is stored in MSDB.

' Combine logical path and package name.

If integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty) Then

myPackage = integrationServices.LoadFromSqlServer( _

packageName, "(local)", String.Empty, String.Empty, Nothing)


Throw New ApplicationException( _

"Invalid package name or location: " & packagePath)

End If

Case "dts"

' Package is managed by SSIS Package Store.

' Default logical paths are File System and MSDB.

If integrationServices.ExistsOnDtsServer(packagePath, ".") Then

myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", Nothing)


Throw New ApplicationException( _

"Invalid package name or location: " & packagePath)

End If

Case Else

Throw New ApplicationException( _

"Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")

End Select

Return myPackage.Execute()

End Function

End Class

This is the code from my page

Code Snippet

Protected Sub btnBuildDW_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBuildDW.Click


End Sub

Protected Sub ExecutePackage()

Dim launchPackageService As New SCCBuildDW.SCCBuildDW

Dim packageResult As Integer


packageResult = launchPackageService.LaunchPackage("file", "c:ssis", "PackageTest")

Catch ex As Exception

' The type of exception returned by a Web service is:

' System.Web.Services.Protocols.SoapException

lblResult.Text = "The following exception occurred: " & ex.Message

End Try

lblResult.Text = CType(packageResult, PackageExecutionResult).ToString

' Console.ReadKey()

End Sub

Private Enum PackageExecutionResult





End Enum

I'm using windows authentication and impersonating an identity. The identity has access to the analysis services role but the package still fails to run.

Can anyone offer any help or advice on this?


View 7 Replies View Related

Unable To Get Return Value From Stored Procedure

Jan 2, 2007

Hi everyone

I am trying something that should be so simple but I cant get it to work. I am calling a stored procedure to lock a table and update a counter. I have tried to follow the exact code use in the MSDN examples but it doesnt work and always returns VT_EMPTY. It also returns a closed recordset so attempting to close it fails. The database is updated correctly and there are no exceptions just no return value.

Here is the stored procedure

 UPDATE tblModelID SET ModelID = @i

Here is the ADO written in C++.

TDatabase DB;






return FALSE;


_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandPtr spCMD;


spCMD->ActiveConnection = DB.m_Cnn;

spCMD->CommandText = "GETMODELID" ;

spCMD->CommandType = adCmdStoredProc;


_RecordsetPtr spRS;

_variant_t vRa;

spRS = spCMD->Execute( &vRa, &vtEmpty2, adCmdStoredProc );

_variant_t rtn(DEF_PARAM(spCMD,0L));


return (int) rtn;


View 3 Replies View Related

Integration Services :: XML Code Is Coming When Open Dtsx Package?

May 13, 2015

When I double click on dtsx package , i am getting XML code. I am unable to get the Graphical View. Recently I have installed Visual 2013.

View 6 Replies View Related

Integration Services :: Unable To Run DTSX Package Locally When There Is A Sort Task

Apr 21, 2015

I have created a very simple package. It has a OLE DB Source, a Sort and a OLE DB Destination.

When I run it in the Integration Designer in Visual Studio, it works fine.

But when I like to execute the package in another C# Project, I get this error:

"To run a SSIS package outside of SQL Server Data Tools you must install Sort of Integration Services or higher."

When I remove the Sort Task, it works.

Here is my C# code:

MyEventListener eventListener = new MyEventListener();
Microsoft.SqlServer.Dts.Runtime.Package _Package;
Microsoft.SqlServer.Dts.Runtime.Application _Application;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult _DTSExecResult;
_Application = new Microsoft.SqlServer.Dts.Runtime.Application();
_Package = _Application.LoadPackage(@"...Package.dtsx", eventListener, true);
_DTSExecResult = _Package.Execute(null, null, eventListener, null, null);

View 7 Replies View Related

Integration Services :: How To Write ETL Package Addressing Different Collations Or Code Pages

Jul 13, 2015

Default code page in ETL package is 1252 which will not work if the collation is different e.g in Japanese_CI_AS, it is 932.

My question is how to write a generic ETL package so that it can cater any collation or any code page.

View 5 Replies View Related

Integration Services :: Unable To Connect To A Webpage From SSIS

Nov 3, 2015

I am trying to connect to a Web page from SSIS and I've tried both http connection manager and a Script Task but none of them seems to be working.

I get the following errors:

Through Script Component: Exception has been thrown by the target of an invocation.

Through http connection manager: The remote server returned an error: (503) Server Unavailable.

Here is one of the VB code I've tried:

Public Sub Main()
Dim MyWebClient As New System.Net.WebClient()
Dim proxyObject As New System.Net.WebProxy("BANANA:8080", True)
MyWebClient.Proxy = proxyObject

[Code] ....

And, here is one of the C# codes I've tried:

public void Main()
Variables varCollection = null;
Dts.VariableDispenser.GetVariables(ref varCollection);

[Code] ...

I could open the same web page mentioned in the code manually in all my browsers (IE, Firefox, Chrome).

I am using SQL Server 2014.

View 18 Replies View Related

Integration Services :: SSIS View Code After Encrypting

Sep 15, 2015

Before we release an SSIS package into our test environment and then eventually into our production environment, we  set the package protection level to "EncryptAllWithPassword"

The "View Code" feature is a nice tool to find things that might be buried in the package somewhere (e.g an complex expression to a variable).

PROBLEM : After one sets the package protection level to "EncryptAllWithPassword", one cannot see the xml source code any longer. It's like compiling and saving cs to the bin. Is their a way to view the code again??

View 2 Replies View Related

Integration Services :: SSIS 2005 - Unable To Access MSDB

Apr 22, 2015

I have a little problem with my IS Server.I am using IS 9.0.1399 (SSIS 2005).I have one instance for databases.I am able to connect to Integration Services just with the Servername (no Instance Name) and I see the folders in there.When I try to open the MSDB Folder (under "Stored Packages") I get the following exeption:

"Login timeout expired/An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.SQL Network Interfaces: Connection string is not valid [87]."

View 3 Replies View Related

Unable To Retrieve The @@Error Code In Stored Procedure

Aug 25, 2006

Hi All,

In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.

CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
select '1'
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'


Thanks & Regards


View 4 Replies View Related

Capture Return Value From Stored Procedure, Use Same In Code Behind Page

Apr 18, 2007

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted.  In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber         nvarchar(25), @ID  nvarchar(50), @LName  varchar(50), @FName  varchar(50)
 DECLARE @myERROR int    -- local @@ERROR      , @myRowCount int  --local @@rowcountBEGIN  -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info   WHERE ID = @ID)   BEGIN    RETURN 1END ELSEBEGIN TRAN    INSERT INTO Info(IDNumber, ID, LName,             FName) VALUES (@IDNumber, @ID, @LName,             @FName)             SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
                               COMMIT TRAN RETURN 0 HANDLE_ERROR:  ROLLBACK TRAN  RETURN @myERROR      ENDGO page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"            SelectCommand="SELECT * FROM TABLE_One"                        UpdateCommand="UPDATE TABLE_One WHERE ID = @ID"                         InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">                        <SelectParameters>                <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />            </SelectParameters>             </asp:SqlDataSource>

View 2 Replies View Related

How Do I Return The Identity From Stored Procedure To Code Behind Page?

Jun 2, 2006

how do i return the identity from stored procedure to code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35),   @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?

View 2 Replies View Related

Integration Services :: How To Execute Custom Class Library Code Through SSIS

Jun 11, 2015

I have a requirement in which i have to create a custom .net class library for Ex:-I retrieve password(s) from a thrid party component. Below is what i am doing.

(1) Created a custom class library which reads a custom .xml file from a drive Ex:- "D:MyAppMYAppCofig.xml" and sets to my properties of my custom class library and inside it i created an instance of third party component's class and passed these values. Since i need to use this .net custom class library both in web and ssis/database side i am using this custom .xml file.

(2) After validating passed data (properties set in custom .net class library) the thrid party component instance object created in my custom .net class libraty returs a password to me own custom .net class libray.

(3) This password I use in my web app for connecting to database. This code is working fine.

(4) My question is how to execute a custom .net class library code through ssis and to use the my same custom .net class library and pass the password to my SSIS component / taks so that that code block also uses the returned password to connect and do any needed tasks? In other words how to use custom .net class library from SSIS.

My Environment is as follows:-
SQL Server is : 2008 R2
VS.NET 2013

View 5 Replies View Related

Copyrights 2005-15, All rights reserved