row counts in execute sql task
Hello all,
I have following sql table.
table name , date, Email sendto .
table 1 01/01/10 czen@hotmail.com
tabel2 05/06/10 deric@hotmail.com
table3 08/21/10
kim@hotmail.com
I have created a package that load data from each table and save it as separate txt files in a folder in local drive. I have used two variables for tablename and Desitnation file name. Package includes two execute slq tasks and for each loop task.
Now I wanna count number of records of each table and insert into a log table.
My code is like this; I wonder where should I include select @row
=@@rowcount and insert statement. I am not getting right num of records when i run this code right now.
Execute sql task is inside the Foreach loop.
SET
NOCOUNT ON
declare
@FileName nvarchar(1000)
declare
@TableName nvarchar(1000)
declare
@sql nvarchar(1000)
declare
@Rows int
set
@FileName =?
set
@TableName = ?
select
@sql = 'bcp "SELECT TOP 10 * FROM '+@TableName+' " queryout Y:\RejFolder\'+@FileName+''+'.xls -c -e -t, -T -S 1XX.18.2XX.1X1'
exec
master..xp_cmdshell @sql
select
@Rows = @@ROWCOUNT
INSERT
INTO [HSN].[dbo].[RejectLog]
([TableName]
,[RejCode]
,[RecordsCount]
,[EmailSendto]
,[RejDate]
,[CreateDt]
,[Sucess])
VALUES
(@TableName
,2
,ISNULL(@Rows,0)
,'PAM@mycompany.com'
,GETDATE()
,GETDATE()
,'y')
GO
shamen
December 21st, 2010 5:30pm
why you did this so hard?!!!!!!
you don't need script for this,
just use DATA FLOW TASK
put an OLEDB Source and connect it to source sql server table,
then put a FLAT FILE Destination and connect it to destination text files
all things are easy and simple to do.
with the data flow task you can use ROW COUNT TRANSFORMATION to get number of rows simply, just put it before destination and fill results in a variable.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 5:35pm
I tried it first. But when DATA FLOW TASK and FLAT FILE CONNECTION are used, it only gives the output text file for the first table(table1). It does not loop thorough all tables.shamen
December 21st, 2010 5:44pm
It will do if you put it in a foreach loop,
you can use table name as destination file name.
let me clarify this more,
you have a sql table which has information about table names and dates and ...
I call this as Mapping table.
first of all you fetch all rows of mapping table into an object type variable, for this step do this actions:
add an object type variable in package scope, let's name it MappingDataVar
use a data flow task,
add an oledb source pointing to Mapping Table
add a RecordSet Destination and set MappingDataVar there, map columns too.
then go back to control flow
add two variables:
a string type package scope variable. name it TableName
a Int type package scope variable , name it RowCount
add a foreach loop container, set enumerator as ado, and set MappingDataVar variable there,
then in variable mapping table , set TableName variable with index 0
then put another data flow inside the foreach loop, in the data flow task, put an oledb source, set data access mode as table name or view name from variable, then set variable as User::TableName
then put a flat file destination , point it to a text file and do mappings,
then right click on flat file connection manager, select properties, in the properties window, find Expression property,click on the ellipsis button in front of it, and in the expression window, set properties as ConnectionString and set expression as this:
"C:\\myFolder\\"+@[User::TableName]+".txt"
put your folder path instead of c:\\myfolder\\ .
also note that you can put row count transform before destination and fill number of rows in data stream in the User::RowCount variable.
http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 6:01pm
In t-sql use:
print @@rowcount or you can print @@rows to help you debug.
I'm fairly certain your line @rows = @@rowcount is reseting @@rowcount to 1.Please 'Mark as Answer' if found helpful - Chris@tier-1-support
December 21st, 2010 6:02pm
HI Reza,
Thanks.
I have tried it. But meta data is not the same in this case. sorry I forgot to tell you this earlier.
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/6192eaba-b941-4f8f-86bd-f32a5e6be76f
Tier..I will try it.
Thanksshamen
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 6:54pm
But meta data is not the same in this case. sorry I forgot to tell you this earlier.
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/6192eaba-b941-4f8f-86bd-f32a5e6be76f
Oh, so you can not use Data Flow task,
go on your own way with scripts, but use this way :
copy all script in a stored procedure, put a select @@rowcount as RowCnt
at last line
and then you can fetch out this value in a variable with configuring ResultSet in general tab as Single row, and in result set tab, set your variable name with result name as RowCnt.
http://www.rad.pasfu.com
December 22nd, 2010 12:11am
But meta data is not the same in this case. sorry I forgot to tell you this earlier.
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/6192eaba-b941-4f8f-86bd-f32a5e6be76f
Oh, so you can not use Data Flow task,
go on your own way with scripts, but use this way :
copy all script in a stored procedure, put a select @@rowcount as RowCnt
at last line
and then you can fetch out this value in a variable with configuring ResultSet in general tab as Single row, and in result set tab, set your variable name with result name as RowCnt.
http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 12:11am
When I run this in SSMS, I do get the expected result;
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
CrtDt NumOfRows
2010-12-22 14:25:58.963 Selected 112 rows in SSIS_TaskA
I guess I have to change the syntax when I use this in Execute sql task?
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS
shamen
December 22nd, 2010 4:43pm
When I run this in SSMS, I do get the expected result;
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
CrtDt NumOfRows
2010-12-22 14:25:58.963 Selected 112 rows in SSIS_TaskA
I guess I have to change the syntax when I use this in Execute sql task?
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS
shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 4:43pm
Since you loop through the tables, why not you use an Execute SQL Task in which you poll the number of records?
Say you execute the sql below, capture the count and return to a variable that can be consumed the way you intended:
DECLARE Row_Count as INT
Set Row_Count = Select Count(*) from Table1 -- here Table1 goes as a parameter perhaps
Return the Row_Count -- done using out parameter mapping.
As an aside the @@ROWCOUNT I suspect is suppressed by the SSIS engine for performance purposes.Arthur My Blog
December 22nd, 2010 5:11pm
Since you loop through the tables, why not you use an Execute SQL Task in which you poll the number of records?
Say you execute the sql below, capture the count and return to a variable that can be consumed the way you intended:
DECLARE Row_Count as INT
Set Row_Count = Select Count(*) from Table1 -- here Table1 goes as a parameter perhaps
Return the Row_Count -- done using out parameter mapping.
As an aside the @@ROWCOUNT I suspect is suppressed by the SSIS engine for performance purposes.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 5:11pm
I added select@@rowcount as RowCnt as the last line of the code.
Another execute sql task was added and put this in sqlstatement box.
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =?
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL('+@RowCnt+',0))+' rows in SSIS_TaskA')
But I am not getting NumOfRow counts.
CrtDt NumOfRows
2010-12-22 12:00:10.023 Selected +@TableNam rows in SSIS_TaskA
2010-12-22 12:00:10.820 Selected +@TableNam rows in SSIS_TaskA
shamen
December 22nd, 2010 5:26pm
I added select@@rowcount as RowCnt as the last line of the code.
Another execute sql task was added and put this in sqlstatement box.
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =?
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL('+@RowCnt+',0))+' rows in SSIS_TaskA')
But I am not getting NumOfRow counts.
CrtDt NumOfRows
2010-12-22 12:00:10.023 Selected +@TableNam rows in SSIS_TaskA
2010-12-22 12:00:10.820 Selected +@TableNam rows in SSIS_TaskA
shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 5:26pm
tell us how did you configured exactly each execute sql task?
I think you did something wrong
http://www.rad.pasfu.com
December 22nd, 2010 5:44pm
In the first EST, I added SELECT @@ROWCOUNT to sql statement as the last line.
General;
ResultSet ; Single row
SQL SourceType;Direct
ByPassPrepare;True
Parameter Mapping; two input parameters
Result Set;
ResultName 0
Variable Name; User :: RowCnts (This variable is string type)
In the second EST;
ResultSet; None
SQL Statement;
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =?
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL('+@RowCnt+',0))+' rows in SSIS_TaskA')
ByPassPrepare;True
Parameter Mapping;
User::RowCnts Input NVARCHAR 0 -1
I am guessing syntax is not correct?
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL('+@RowCnt+',0))+' rows
shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 5:57pm
use this line at the end of statement in first execute sql task:
SELECT @@ROWCOUNT as RowCount
then set result name in result set tab as RowCount instead of 0.
http://www.rad.pasfu.com
December 22nd, 2010 6:01pm
Then I get an error message; :(
[Execute SQL Task]
Error: Executing the query "
SET NOCOUNT ON
declare @FileName nvarchar(1000)
..." failed
with the following
error:
"Incorrect syntax near the keyword 'RowCount'."
.shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 6:09pm
When I run this in SSMS, I do get the expected result;
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
CrtDt NumOfRows
2010-12-22 14:25:58.963 Selected 112 rows in SSIS_TaskA
I guess I have change the syntax when I use this in Execute sql task?
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS
shamen
December 22nd, 2010 6:41pm
When I run this in SSMS, I do get the expected result;
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
CrtDt NumOfRows
2010-12-22 14:25:58.963 Selected 112 rows in SSIS_TaskA
I guess I have change the syntax when I use this in Execute sql task?
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS
shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 6:41pm
Hi Arthur,
Thanks for the suggestion. I am trying it now.
Again I am not sure how to write the code as i am using a table variable.
DECLARE @RowCount as int
DECLARE @TableName varchar(1000)
Set RowCount = 'Select Count(*) from '+TableName+''
Again I will have the same problem when inserting the records to the log table. I ma not sure how to write the highlighted part of the code.
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
Thanks
shamen
December 22nd, 2010 6:41pm
Hi Arthur,
Thanks for the suggestion. I am trying it now.
Again I am not sure how to write the code as i am using a table variable.
DECLARE @RowCount as int
DECLARE @TableName varchar(1000)
Set RowCount = 'Select Count(*) from '+TableName+''
Again I will have the same problem when inserting the records to the log table. I ma not sure how to write the highlighted part of the code.
DECLARE @RowCnt NVARCHAR(1000)
DECLARE @sql NVARCHAR(1000)
SET @RowCnt =112
INSERT INTO MyLogTable (CrtDt,NumOfRows)
VALUES (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@RowCnt,0))+' rows in SSIS_TaskA')
Thanks
shamen
Free Windows Admin Tool Kit Click here and download it now
December 22nd, 2010 6:41pm
Then I get an error message; :(
[Execute SQL Task]
Error:
Executing the query "
SET NOCOUNT ON
declare @FileName nvarchar(1000)
..." failed
with
the following error:
"Incorrect syntax near the keyword 'RowCount'."
.
shamen
tell me what is your sql statement on first execute sql task exactly?http://www.rad.pasfu.com
December 23rd, 2010 1:10am
Hi Shamen,
you need to use dynamic SQL if you want to have the table as a variable.
So it should be like follows:
DECLARE @SQL NVARCHAR(4000)
SET @SQL= 'Set RowCount = Select Count(*) from ' + @TableName
EXEC(@SQL)Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2010 9:43am