rowcount from a bulk insert task in SSIS

I would like to get the rowcount from a bulk insert task to validate that all the data is being inserted correctly. So far the only way that I can see this being done is through a trigger on the target tables. I would like to have this information inside the DTS package. Can anyone help me?

Thanks

How about using two Exec SQL Tasks with SELECT COUN(*) FROM MyTable, to get the before and after counts. Depends on if you expect other people to be adding records around the same time, but then that would be an issue with triggers possibly. Not much of a bulk insert of you have triggers firing as you have to reduce the "load speed" to get them to fire.|||

DarrenSQLIS wrote:

How about using two Exec SQL Tasks with SELECT COUN(*) FROM MyTable, to get the before and after counts. Depends on if you expect other people to be adding records around the same time, but then that would be an issue with triggers possibly. Not much of a bulk insert of you have triggers firing as you have to reduce the "load speed" to get them to fire.

This is exactly how Joy Mundy proposed to do auditing in her " Ralph Kimball Group SSIS webcast" presentation listed on the front page of this thread. Finish your inserts and then in a separate task grab your counts.

Row Not Inserting At Bottom

Greetings, SQLanoids,

When I insert a row of data into a table via a stored procedure, the row SOMETIMES gets inserted at the bottom, and sometimes gets inserted at another random location (top, midding, etc). I am using Microsoft SQL Server.

Dea anybody know the cause of this? Or how to ensure that the row gets inserted at the bottom?

I am a novice SQL Server user, but this seems like a simple problem that the more advanced users solved long ago. Thanks in advance!This behavior you are seeing is by design. SQL Server decides where to put the data and we have no control over that. Nor, really, is it important.

If you need the data to come out of the database in a particular order, you need to use an ORDER BY statement. If it is important for you to know the order in which records were inserted into the database, I suggest using a DateAdded column with a Default Value of GETDATE() so that SQL Server will automatically populate this for you. You might also consider using a column flagged as an IDENTITY for this purpose.

Terri|||Thank you for the response. I tried your advice and it worked well. However, I noticed only one problem.

When it inserts the date, using a Default Value of GETDATE(), and using data type smalldatetime, the field gets inserted as "1/17/2005 2:23:00 AM".

However, when I set the data type to vnarchar, the field gets inserted as "Jan 17 2005 2:23AM"

I prefer the second method, as it spells out the first 3 letters of the month. The problem, however, is that the ORDER BY statement does not accurately pull the records when stored as nvarchar. It is unable to distinguish between AM and PM and pulls the records in order of numeric chronology only.

Do you know how I can fix this, and still store the items as "Jan..."?

Thanks|||Use a datetime data type to store datetime data. Use output formatting to display the data in the desired format (either by using CAST or CONVERT, or in the ASP.NET front end).

Terri

Row not found at Subscriber

When we inserted a row at the Publisher -- this is error we got:
The row was not found at the Subscriber when applying the replicated command.
Since this was a new row, why would I get this error.
Unrelated question: we do not proprogate changes from the Subscriber to the
Publisher. However, there is one field that is allowed to be updated at the
subscriber. All other data is read-only.
What effect will this have on replication. That same field is replicated.
Logic tells me the updated subsriber field will be overwritten by the next
replication.
WR
When you updated the row at the subscriber, did you change its primary key?
If you did, this is probably why you are getting this error. Replication
uses the PK to determine which row on the subscriber to modify. You can
enable logging as per this kb article to determine which row replication is
failing on.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
news:1BBA4901-21F7-4023-AFAA-811F3F78BB31@.microsoft.com...
> When we inserted a row at the Publisher -- this is error we got:
> The row was not found at the Subscriber when applying the replicated
command.
> Since this was a new row, why would I get this error.
> Unrelated question: we do not proprogate changes from the Subscriber to
the
> Publisher. However, there is one field that is allowed to be updated at
the
> subscriber. All other data is read-only.
> What effect will this have on replication. That same field is replicated.
> Logic tells me the updated subsriber field will be overwritten by the next
> replication.
> --
> WR
|||I will enable logging; but the primary key cannot be updated (it is an
identity field at the publisher database).
Adminstrators of a local Windows .Net application can setup users who are
authorized to view data on the web in their Windows application. Data that
is viewed on the web is Pushed to a database running on a Server on the
internet. When a web user is initially set up in the local windows
application, the password for that user is initially set.
When the web user logs in for the first time he must change his password.
That is the only write activity allowed on the web (the subscriber). My
first question related to the fact, if that User was later updated at that
local application, wouldn't that overwrite any password update at the
subscriber site. If this is the case, can I allow updating the publisher for
only that column or record?
Concerning the other errror: I do recall deleting the same contact via EM
at both the Windows site and the Web site. Could this have caused this
problem? If so, how can I repair it?
wrl
"Hilary Cotter" wrote:

> When you updated the row at the subscriber, did you change its primary key?
> If you did, this is probably why you are getting this error. Replication
> uses the PK to determine which row on the subscriber to modify. You can
> enable logging as per this kb article to determine which row replication is
> failing on.
> http://support.microsoft.com/default...b;en-us;312292
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
> news:1BBA4901-21F7-4023-AFAA-811F3F78BB31@.microsoft.com...
> command.
> the
> the
>
>

Row insert timestamp?

Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!Track it yourself

USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) PRIMARY KEY
, Col2 Char(1)
, Col3 datetime DEFAULT getdate()
)
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!|||Originally posted by blm14_cu
But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!

Did you try my code?

And lazy is good...|||Here, Here's lazy...

uncomment the EXEC to exeute the statements...

But

BE CAREFUL

You can't easily get rid of a column once you add it....|||ooops..forgot the code

Use Northwind
GO

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99|||Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.|||Originally posted by blindman
Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.

Yo blind dude...

Want to post how you get rid of a column?|||I use ALTER TABLE DROP COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself. You can do it in Enterprise Mangler too.

-PatP|||Being one of the Lazy DBAs, I do it directly from Enterprise Mangler or have EM generate a script that I can edit.|||What a scrub...there's my DB2 Roots showing..

Thanks guys

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+TABLE_NAME+'_myDateCol_Default]'
FROM INFORMATION_SCHEMA.TABLES
GO

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP COLUMN [myDateCol]'
FROM INFORMATION_SCHEMA.TABLES
GO

Oh and the original code is wrong

Use Northwind
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime CONSTRAINT '
+'['+TABLE_NAME+'_myDateCol_Default] DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT FROM myCursor99 INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

sheesh...what a scrub....|||Oh dear, Brett...

I guess, on the bright side, your work just got a lot easier.|||Funny thing is, I've never had a need to drop a column....

Anyone?

Usually it's part of massive reengineering effort...

Row ID trigger

Anyone knows how to make a good trigger to create a row id on a table every time a new row is inserted? This is to maintain row id sequence by not setting the row id field as an auto identity which may be in broken sequence after a while.
Thanks.just use something like "select max(rowID)+1" in the update trigger. Although you'll have to careful of your isolation level.|||I tried what you said in INSERT trigger and it did'nt work.
Why?

Thanks.|||What's to know about isolation level?
Thanks.|||If you have a low isolation level then, potentially, two inserts would get the same result for select max()

What "doesn't work"?|||How can the low isolation level be taken care of?

Sorry, I tested the trigger in enterprise manager and at first didn't see the rowid appear until I refreshed the table, which made me believe the trigger wasn't working.

Thanks.|||"which may be in broken sequence after a while. "

Why do you think so, IMHO, it cannot be broken. If you don't like identity you can use
so-called "table with current id's for all tables" and update it through sps or udfs|||You need to have a quick read of the isolation levels. Basically the lower the level the less database locks you have - although that means you have more concurrency. The higher the level the more locks you have so the "safer" code like select max() becomes - but your concurrency drops. You can influence the isolation level using commands such as "SET ISOLATION LEVEL" or by using query hints. If you're using the select max() technique then I'd suggest you look at the UPDLOCK hint.|||Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
Thanks.|||>> Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.

How are you trying to use a UDF? They're just a way of batching SQL, they still need to be called...like a stored procedure. So you'd still need to put them in a trigger.

>> how can they get out of sequence
Easily! Insert, rollback, Insert. Where can I buy a MCSDBA for $2 please?