 | |
Logging record counts in SSIS
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
RE: Logging record counts in SSIS -
10-07-2008
, 03:20 PM
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#12
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#13
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#14
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#15
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#16
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
#17
| |||
| |||
|
|
Hello: May have something to do witht he fact that you are using Event Handlers instead of Control Flow. Just out of curiosity, why are you using Post-Exec event handlers and not putting your Execute SQL right inside the Control Flow? From what I understand, if you have, say 20 files and each processes about 100 records, then you want to, after all is said and done, record the fact that an aggregate of 2,000 rows were processed. Is this correct? If so, I would do this: Create another Package scoped variable called RowCountAggregate. Then add a Script Task inside the For Each loop, and have it execute after the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite Variables to RowCountAggregate. Inside the Script itself, have one line: Dts.Variables("RowCountAggregate").Value = Cint(Dts.Variables("RowCOuntAggregate").Value + Cint(Dts.Variables("RowCount").Value This will make the RowCountAggregate a 'running sum' of the rows processed. Now, AFTER the loop, add an Execute SQL task the fires the insert statement, with a mapping of this variable to one of the SP input parameters. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "GPage" wrote: Hello, I have an SSIS package that loops through files in a folder using a for each file loop control object. It then runs a data flow task for each file to load the flat file into table. I want to get an aggregate count of how many rows total were added to the table and put that in an audit table. I have created a variable RowCount and put a Row Count transformation in between the file source and database target in the data transfer task. I have assigned the RowCount variable to the VariableName property of the Row Count Transformation. I have also added a post execute event handler at both the package level and the Data Transfer Task level which runs an execute sql task that uses an expression to create the insert statement to the audit table. The expression casts the RowCount variable as string and adds it to the insert statement. When I run this package it runs successfully, and several rows are added to the audit table, I assume several rows because postexecute at the package level runs once per task completion? The real problem though is that all the row counts in the audit table show 0 which is what the variable is initialized to, so it looks like the RowCount transformation is either not working, or the variable is being re-initialized somewhere. The variable is scoped at the package level. I'd like some help in figuring out why the variable is not storing the rowcounts like it should. Thanks |
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.