Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kgayda
Starting Member
7 Posts |
 Posted - 2002-01-03 : 13:32:05
|
| I am running a sub package from SQL Server 7 for a project that I am working on. I need to find out how many records were inserted from the import file and write to a log. I cannot seem to find a way to determine how many records were added to the table from the import file. I suspect this is possible since when you execute the DTS it give you a status of how many records were processed as the transform is progressing. I need to log this information so I can resolve any descrepancies between how many records are supposed to be in the source file vs. how many were actually inserted. In lieu of doing Select Count queries is there a DTS object model property to do this and if so how do I script it? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-03 : 15:38:05
|
| If you go to the Options tab of the Transform step, you have several options for logging exceptions. SQL 2000 allows you to log both source and/or destination error rows. You could put these exceptions into the log file, then run a quick script to count how many are there. Subtract the exception rows from the total rows and it will give you the number successfully imported.Another way of doing it is to run a quick SELECT Count(*) command before the import, assign it to a global variable, then do the same after the import, and compare the two. |
 |
|
|
kgayda
Starting Member
7 Posts |
Posted - 2002-01-03 : 16:43:13
|
| Unfortunately I'm bound by my environment to use ver 7.0. The select Count(*) is the workaround I am already using but I am dealing with very large datasets and wanted to avoid doing any unnecessary queries for performance reasons. I also wanted to create a log format of my own choosing that is easy for analysts to peruse. I was hoping that there was an exposed property that I could interrogate after the transform ran and get my info there. It would be the most efficient method. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-03 : 16:50:37
|
| The only other thing I can think of is to rewrite your tranformations to log or count every successful row, and/or count every failed row. While this is very powerful and flexible, it will send performance into the toilet.Take a look at this site:http://www.sqldts.comThey might have something. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-03 : 16:56:30
|
| I don't know how quickly SQL7 will update statistics after a DTS import, but you could experiment with getting a recordcount from sysindexes table. SELECT o.name, i.rows FROM sysindexes i, sysobjects o WHERE i.id = o.id AND indid<2 AND o.xtype ='U' and o.name = 'mytable'And only when you find a discrepancy between the result returned by this query and the number you expected to see, you would need to run SELECT COUNT(*).Edited by - izaltsman on 01/03/2002 16:58:11 |
|
|
|
kgayda
Starting Member
7 Posts |
Posted - 2002-01-03 : 17:29:39
|
| I can get the info via queries but I really wanted to know if the Task or Package or some other object of the DTS object model has a property that gives that information. I looked at Microsoft's site and did not find what I needed. There was no property that indicated the successful rows processed. I am only somewhat familiar with the DTS object model so I may not be looking in the right place. |
|
|
|
|
|
|
|
|
Getting count fo records inserted by DTS Transform