I am fetching data from xls file. Based on the number of rows fetched from the file I need to perform certain operation on each row of the data. Please suggest how to get the row count for the data fetched from the file.

Can you use the row count component?

I need this count in my script where I am updating id column of the row. I have tried to use the variable in script, but I think it does not allowed to use it outside the PostExecute.

I see to ways: 1.Use an execute sql task with a query like: Code SnippetSelect count(*) as MyCount from [tab1$] Placing the value of the count in a SSIS variable; then you use that variable to drive the execution flow using expressions in the precedence constraints that reference that variable. Notice that the query above will count row headers as well. 2. Use an additional data flow with a count transformation to pre-process the excel file. then drive the execution flow as described above. Notice thatthis data flow taskwill not need to have a destination component.

U just need to use the Row Count transformation. Add a variable (scope whole package) eg RowCount Int32. Then in the data flow between source and destination u use the RowCount which stores the number of rows in a variable. Set in the component properties tab the variable name to User::RowCount. After the data flow task,in control flow,you can drag the connection to the next steps depending on the value of @RowCount. Click on the green arrow and set "Evaluation Operation" to Expression and Expression to eg @RowCount>=0

i am working present on ssrs but now i have the requirement on ssis i am using row count transformation i am getting different outputs so help me .... for example i searched in google for help of row count transformation i got one example http://www.daveturpin.com/2009/10/writing-query-row-count-to-flat-file-in-ssis-part-i/ in this also there is a error so go through this link and rly meveera

If you want the rowcount in the same data flow you will have to come up with a query that does it. Because the result of the rowcount transformation can't be used in the same dataflow. Something like (not sure excel query supports this) SELECT [Column 0] , [Column 1] , counter.total FROM ExcelWorksheet1 INNER JOIN ( SELECT COUNT(*) as total FROM ExcelWorksheet1 ) as counter on 1 = 1 Or you do it with two dataflows like in this example. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com