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

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

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

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

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

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

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

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

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

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

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

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

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

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

tell us how did you configured exactly each execute sql task? I think you did something wrong http://www.rad.pasfu.com

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

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

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

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

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

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

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

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

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