RowCount

Hi,

want to get the number of rows i'm retrieving from a source. This count should be written as " No: of roes retrieved" + varname

I have used OleDbSource, RowCount,Script [ To write in a file ]. Rows is the package level variable name used in rowcount. when i do this way it always writes as 0 in the file.

[code in Script]

Dim sw As New StreamWriter("D:\Vijay1.txt")

s = Variables.Rows

sw.WriteLine(s.ToString)

sw.close

[/Code]

Can anyone help on this

you should store the row count in an ssis variable, then retreive the value from the script...|||

Hi,

I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

Thanks

|||

Can you try as follows:

Dim sw As New StreamWriter("D:\Vijay1.txt")

sw.WriteLine(Dts.Variables("Rows").Value.ToString())

sw.close()

Thanks,
Loonysan

|||

ManjuVijay wrote:

Hi,

I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

Thanks

the code should be:

s = Dts.Variables("Rows").Value

|||

Hi,

I am getting error saying DTS is not declared.

Thanks

|||

Hi,

I want to know whether i'm missing anyother thing.

I beleive I have to set only the variable name in RowCount. Any thing else I have to do?

|||

If i use script task it works properly

why i am not able to do so in script transform component

|||

The Script Task and Script Component are very different beasts. It is wrong to assume that because you can do something in one then you can also do the same in the other.

Its also true to say there are different ways of doing the same thing. For example, the syntax for accessing variables in the script component is different to that for accessig them in the script task.

What exactly are you unable to do?

-Jamie

|||> If i use script task it works properly

>why i am not able to do so in script transform component

The script component is used within a DataFlow task. The DataFlow task "snapshots" a variable value when it begins execution and cannot modify the variable until it has completed.

So, your row count = 0 at the beginning of execution. Your script component accesses the "snapshot" value and writes out 0.

The RowCount component only updates the row count variable, when execution of the data flow has completed. Your script task accesses the value after this and writes out the final rowcount.

Why does SSIS snapshot variable values? Well imagine a conditional split where the data is split on a variable value. If that could change during execution of a data flow, the behaviour of the split would be unpredictable - rows would be directed depending on whether they just happened to reach the split before or after the variable changed.

Donald

sql

ROW_NUMBER() function is not recognized in store procedure.

Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.

CREATE PROCEDURE GetProductsOnCatalogPromotion
(@.DescriptionLength INT,
@.PageNumber INT,
@.ProductsPerPage INT,
@.HowManyProducts INT OUTPUT)
AS
-- declare a new TABLE variable
DECLARE @.Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)
-- populate the table variable with the complete list of products
INSERT INTO @.Products
SELECTROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @.DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1
-- return the total number of products using an OUTPUT variable
SELECT @.HowManyProducts = COUNT(ProductID) FROM @.Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @.Products
WHERERowNumber > (@.PageNumber - 1) * @.ProductsPerPage
ANDRowNumber <= @.PageNumber * @.ProductsPerPage

What version of SQL Server is this running on? ROW_NUMBER() was introduced in 2005, so any version before that won't have it.

Don

|||

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

I want to fetch records for paging purpose by its row numbers. so i tried this store proc. is there any other way to fetch without row numbers?

please reply.

|||

prasad bhanage:

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

Row_number() is a T-SQL enhancement that has been introduced in SQL 2005 only. If your database is in SQL 2000 you can't use any of the features introduced after SQL 2000 even if you run them using SQL 2005 tools. visithttp://msdn2.microsoft.com/en-us/library/ms186734.aspx for more information.

Now, to solve you problem as you've SQL 2000, there is one way of using temporary table for this. Your modified procedure is as below. Please make the logical changes as you wish.

CREATE PROCEDURE GetProductsOnCatalogPromotion(@.DescriptionLengthINT,@.PageNumberINT,@.ProductsPerPageINT,@.HowManyProductsINT OUTPUT)ASSELECT identity (bigint , 1 , 1 )as RowNumber , ProductID ,Name ,SUBSTRING(Description , 1 , @.DescriptionLength ) +'...'AS Description , Price , Image1FileName , Image2FileName , OnDepartmentPromotion , OnCatalogPromotioninto #ProductsFROM ProductWHERE OnCatalogPromotion = 1order by Product.ProductIDSELECT @.HowManyProducts =COUNT ( ProductID )FROM #ProductsSELECT ProductID ,Name ,Description , Price , Image1FileName ,Image2FileName , OnDepartmentPromotion , OnCatalogPromotionFROM #ProductsWHERE RowNumber > ( @.PageNumber - 1 ) * @.ProductsPerPageAND RowNumber <= @.PageNumber * @.ProductsPerPage

Hope this will help.

|||

Hi,

You need to create the database in sqlserver 2005.

Then only it will work other wise u can't use perticular keyword in Sql Server 2000.

It was newly introduced in sqlserver 2005 Only.

_____________________________________________________________

Mark as Answer If u find a solution.

Row numbers on export

HI

I have written a script to export data from customer table to another crm package, on the export they require the first column to be numbered 1 - .... say 1000 or how ever many rows there will be.

Is it possible?

Thanks

Rich

Are you talking about a batch count? Or does every row have to have a number?

If it's batch count then use DTS add a global variable, assign the count, then use the file system object to insert the count into the file after the export

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Okay after reading it again I think you want count for each row

In sql 2005 you can use ROW_NUMBER in 2000 you can use IDENTITY with a temp table

2000 version

SELECT IDENTITY(INT, 1,1) AS Rank ,*
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT * FROM YourTable
ORDER BY SomeColumn

SELECT * FROM #Ranks ORDER BY Rank

2005 version

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn) AS 'rownumber',*
FROM YourTable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

is there not a function i could use in the statement? , i am not going to use dts

thanks

|||

do a select count(*) from (your query here)

union all

your original query

example in pubs on SQL server 2000

select convert(varchar(30),count(*)), '','','','','','','',''
from authors
union all
select au_id, au_lname, au_fname, phone, address, city, state, zip, contract
from authors

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks Denis, i will go the temp table route