get cnt Rows Affected by Package Execution - SMO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

get cnt Rows Affected by Package Execution - SMO

Author

Topic

sachin.kale
Starting Member

6 Posts

Posted - 2008-12-10 : 02:34:29

I am running my package using 'LoadFromSqlServer()' in SMO. I am successfully able to run it but I want number of rows which were affected using that package in a Database. So Is there any way for this.

my code is as follows.

Code Snippet

Package pkgIn;

Application app;
DTSExecResult pkgResults;

pkgIn = app.LoadFromSqlServer(PackPath ,ServerName , UserName , Passward , null);
pkgResults = pkgIn.Execute();

Please help me on this as I have wasted too much of time on this and I got no help for this..
Thanx in adavance..

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 10:43:58

Create Stored Procedure with an output, catch this in DMO.
E.G.
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sTest] @output int output

AS
BEGIN
UPDATE [MyDatabase].[dbo].[A]
SET
[testmoney] = '10'
WHERE [testmoney] ='5'
set @output = @@rowcount
END

---------------------------------
--Call Procedure and read output:

Dim QueryResult

Dim oServer
Dim oDatabase
Dim sResults
Dim strSQL

strSQL = "declare @output int exec [MyDatabase].[dbo].[sTest] @output output select @output as arbitraryColName"

Set oServer = CreateObject("SQLDmo.SqlServer")
Set oDBFileData = CreateObject("SQLDMO.DBFile")
Set oLogFile = CreateObject("SQLDMO.LogFile")

oServer.LoginSecure = True

oServer.Connect

Set QueryResult = oServer.ExecuteWithResults(strSQL)

Msgbox( QueryResult.GetColumnString(1, 1))

oServer.DisConnect

Set oServer = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources