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_id in sql server....

Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)
Bill_id name
25 abc
18 def
26 ghi
23 adfd

but my result should look like:
Row_Id Bill_id name
1 25 abc
2 18 def
3 26 ghi
4 23 adfd

n this Row_Id should be generated automatically...even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general???
any help will be greatly appreciated.
Regards.create table t(bill_id int, name varchar(10))

insert into t values(25, 'abc')
insert into t values(18, 'def')
insert into t values(26, 'ghi')
insert into t values(23, 'adfg')

select Row_ID=count(*), a1.bill_id, a1.name
from t a1, t a2
where a1.bill_id >= a2.bill_id
group by a1.bill_id, a1.name
order by 1

Row_ID bill_id name
-------
1 18 def
2 23 adfg
3 25 abc
4 26 ghi|||Thanks a lot...it really WORKED...
regards.sql

Row Numbers for a View

I've been given a task that I believe is, basically, impossible, but
I'd like to see if there's a way to do it.

What my boss wants me to do is to create a view, in SQL Server 2000,
that will provide not only a row number field of some sort, but that
will produce sequential ordering for arbitrary selects and orderings.
So, if my data is a table with values from A thru D and my user does
SELECT data FROM vwTable, the result would be:

Row Data
-- --
1 A
2 B
3 C
4 D

But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
get

Row Data
-- --
1 D
2 C
3 B
4 A

And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
would get

Row Data
-- --
1 B
2 C

In SQL 2005, of course, this would be fairly trivial since I could use
the ROW_NUMBER function. In 2000, though, it seems to be utterly
impossible. My boss, however, is convinced that there must be some way
to create a calculated field to do it.

I'll be cursed if I can figure out a way to do so.

Any suggestions would be appreciated.>> In 2000, though, it seems to be utterly impossible. My boss, however, is
>> convinced that there must be some way to create a calculated field to do
>> it.

Paste the following in Google search box:
"dynamically number rows site:support.microsoft.com"

--
Anith|||Where do you want to show the data?
Use Front End application to do this

Madhivanan|||Anith Sen wrote:
> >> In 2000, though, it seems to be utterly impossible. My boss, however, is
> >> convinced that there must be some way to create a calculated field to do
> >> it.
> Paste the following in Google search box:
> "dynamically number rows site:support.microsoft.com"

Thanks, however, while that is a good way to derive row numbers in a
select statement, unfortunately it isn't quite what my boss is asking
me to do. She wants a view that will produce row counts in a
calculated field regardless of the order that the user uses to select
the data.

I would prefer to require the user to generate the row numbers in their
selects, wjhich wouldd allow for the solution you offered.
Unfortunately, that isn't what I've been tasked to do.|||Madhivanan wrote:
> Where do you want to show the data?
> Use Front End application to do this

SQL Reporting Services.|||On 27 Mar 2006 16:32:09 -0800, Andrew Lias wrote:

>I've been given a task that I believe is, basically, impossible, but
>I'd like to see if there's a way to do it.
>What my boss wants me to do is to create a view, in SQL Server 2000,
>that will provide not only a row number field of some sort, but that
>will produce sequential ordering for arbitrary selects and orderings.
>So, if my data is a table with values from A thru D and my user does
>SELECT data FROM vwTable, the result would be:
>Row Data
>-- --
>1 A
>2 B
>3 C
>4 D
>But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
>get
>Row Data
>-- --
>1 D
>2 C
>3 B
>4 A
>And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
>would get
>Row Data
>-- --
>1 B
>2 C
>In SQL 2005, of course, this would be fairly trivial since I could use
>the ROW_NUMBER function. In 2000, though, it seems to be utterly
>impossible. My boss, however, is convinced that there must be some way
>to create a calculated field to do it.
>I'll be cursed if I can figure out a way to do so.
>Any suggestions would be appreciated.

Hi Andrew,

The way you describe it here, it's impossible. That holds true for both
SQL Server 2005 and SQL Server 2000. Even ROW_NUMBER() won't help you.

If you need the row numbers to match the order specifiede on the select
and if you want to skip numbers for rows not included in the select,
you'll have to add row numbering logic on the SELECT statement. If you
add row numbers in the view, the numbers won't change if you exclude
some rows or choose a different order when selecting from the view.

Just to prevent misunderstanding - it is NOT impossible to get the
result sets you require. But it's only possible by extending the SELECT
with some row numbering logic. Either using ROW_NUMBER() if you're using
SQL Server 2005, or by using either a correlated subquery or a self-join
and a GROUP BY if you're using SQL Server 2000.

--
Hugo Kornelis, SQL Server MVP|||Andrew Lias (anrwlias@.gmail.com) writes:
> Thanks, however, while that is a good way to derive row numbers in a
> select statement, unfortunately it isn't quite what my boss is asking
> me to do. She wants a view that will produce row counts in a
> calculated field regardless of the order that the user uses to select
> the data.

Time to get a new boss?

What she is asking for is not possible. You would have to package the
user's SELECT statement somehow, so you can modify to add the row-number
column. As Hugo pointed out, this is the same on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>>SQL Reporting Services

Cant you make use of Recordnumber feature such as the one available in
Crystal reports?

Madhivanan|||Erland Sommarskog wrote:
> Andrew Lias (anrwlias@.gmail.com) writes:
> > Thanks, however, while that is a good way to derive row numbers in a
> > select statement, unfortunately it isn't quite what my boss is asking
> > me to do. She wants a view that will produce row counts in a
> > calculated field regardless of the order that the user uses to select
> > the data.
> Time to get a new boss?
> What she is asking for is not possible. You would have to package the
> user's SELECT statement somehow, so you can modify to add the row-number
> column. As Hugo pointed out, this is the same on SQL 2005.

That's what I thought. I just wanted to be extra sure that there
wasn't some tricky way to do this before I went back to her and said
that it simply could not be done the way that she was asking.|||if you can use a stored procedure instead of a view, you could select
the data INTO a temp table in the "correct order", alter the table to
add an identity column, and return that ordered by identity.
before someone gets excited, there isn't a GUARANTEE this will work
forever in future versions of SQL, but it probably will.|||Doug (drmiller100@.hotmail.com) writes:
> if you can use a stored procedure instead of a view, you could select
> the data INTO a temp table in the "correct order", alter the table to
> add an identity column, and return that ordered by identity.
> before someone gets excited, there isn't a GUARANTEE this will work
> forever in future versions of SQL, but it probably will.

There is no guarantee that it will work any version of SQL Server. In fact
for a result set of any size, I would not expect it to work.

What is guaranteed to work, at least in SQL 2005, is if you have a
table with an IDENTITY table, and perform an INSERT with an ORDER BY.

Note that this does not apply to SELECT INTO with the IDENTITY function
and ORDER BY. In that case, there is *no* guarantee.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your answer is better - have the identity already there.

But, at least we "solved" the problem!!!!|||Hey Andrew

Nothing is impossible, maybe I have read too fast but here is how I would do it.
Sounds like your boss just wants row numbering on your result set.
In reporting services use this expression

=RowNumber("DataSetName")

That would be like using Crystal's RecordNumber

Hope this helps

Row number in the dataflow task

Hello all,

I got a text file with two columns. and I need to generate a integer key automatically with the row number (or any distinct number, I thought row number will be OK). and when I make the data flow task to import this text file into a raw file I need to get the unique rownumber as Id.
How can I make this in the data flow tak?

regards,

This should work for you:

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

|||

Did you try searching the forum? That question has been asked before and several are the ways of doing it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379124&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1533040&SiteID=1

|||

Thank you.....

I am so happy with the script componant......

It works good.....

Thank you again.

Regards

Row id in sql server....

Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)

Bill_id

name

25

abc

18

def

26

ghi

23

adfd

but my result should look like:

Row_Id

Bill_id

name

1

25

abc

2

18

def

3

26

ghi

4

23

adfd

n this Row_Id should be generated automatically....even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general?
any help will be greatly appreciated.
Regards.ok,i found the solution....here it is:

select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1
|||http://www.aspfaq.com/2427
wrote in message

news:4cd06c7a-358b-47eb-943e-e4e74d2d52b8@.discussions.microsoft.com...

> Hi all,

> i have a task that whenever i will write a select query then when the

> result comes then i should be able to write a column that should write

> the ROW-ID of these rows. I mean for example i write a select query that

> gives me the following table:(the query can be like select bill_id,name

> from myTable order by name)

>

> Bill_id

>

> name

>

> 25

>

> abc

>

> 18

>

> def

>

> 26

>

> ghi

>

> 23

>

> adfd

>

>

> but my result should look like:

>

> Row_Id

>

> Bill_id

>

> name

>

> 1

>

> 25

>

> abc

>

> 2

>

> 18

>

> def

>

> 3

>

> 26

>

> ghi

>

> 4

>

> 23

>

> adfd

>

> n this Row_Id should be generated automatically....even if when i will

> write order by name in reverse order then also it should write the

> row_id as 1,2,3,4 same increasing order...n this automatic function i

> have to make...i cant understand..if there exist some procedure or

> function that already does this...or how can i just find the simple

> ROW_ID in general?

> any help will be greatly appreciated.

> Regards.

>

>

Row Count - Variable Name is Case sensitive

I defined a variable called "NoOfRecords" for a Data Flow Task and within the Data Flow Task I am outputting records from Flat File to RowCount before doing anything else. I typed Variable name as "NoofRecords" in Advanced Editor for Row Count and it didnt like it. It gave the following error.

Error at Load Customers[Row Count [138]]: The variable "NoofRecords" specified by VariableName property is not a valid variable. Need a valid variable name to write to

As soon as I changed the VariableName to NoOfRecords, it worked.

It looks like a case sensitive issue.

Hi Sutha,

Variable names are indeed case sensitive. This is By Design.

Thanks,
Mark

|||Thanks Mark for clarifying it.

Rounding datetime values down

Hi,
I have a task that needs to filter large datasets by date, ignoring the
time. However, we do need to store times alongside the dates for other
uses.
The way that had been implemented was to convert to a varchar and then back
again e.g.
convert(datetime, convert(varchar(10), col_name, 103), 103)
However, this results in an unacceptable performance hit.
Is there any better way of achieving this, or am I stuck with having an
extra column which can be populated at the same time (e.g. by a
insert/update trigger)?
John McLuskyJust query the DATETIME column as a range. For example, for today's date:
SELECT ...
FROM YourTable
WHERE col_name >= '20050608'
AND col_name < '20050609' ;
David Portas
SQL Server MVP
--|||David Portas wrote:
> Just query the DATETIME column as a range. For example, for today's
> date:
> SELECT ...
> FROM YourTable
> WHERE col_name >= '20050608'
> AND col_name < '20050609' ;
Hi David,
Thanks for the suggestion. It may be possible to implement inside the
stored procedures that we're using at the moment and I'll look into that
tomorrow when I'm back in the office.
However, if there's a possibility of converting the dates directly (so they
can be used in a view, for example) that would be the best solution.
At the moment, the new column looks like the easiest option!
John.|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050131'
select c1, ..., cn
from dbo.t1
where c2 >= @.sd and c2 < dateadd(day, 1, @.ed)
AMB
"JM" wrote:

> Hi,
> I have a task that needs to filter large datasets by date, ignoring the
> time. However, we do need to store times alongside the dates for other
> uses.
> The way that had been implemented was to convert to a varchar and then bac
k
> again e.g.
> convert(datetime, convert(varchar(10), col_name, 103), 103)
> However, this results in an unacceptable performance hit.
> Is there any better way of achieving this, or am I stuck with having an
> extra column which can be populated at the same time (e.g. by a
> insert/update trigger)?
> John McLusky
>
>|||> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
You already have that solution. One possible improvement is to cast to INT
and then back to DATETIME. However, the range query method is better because
it can make full use of an index on the column and avoids unnecessary type
conversions. This can make a BIG difference to performance. Of course you
could consider an indexed view or indexed computed column but that seems
redundant to me if your data can be accessed via an SP.
David Portas
SQL Server MVP
--|||I don't understand. You first post asked for code suggestions how to do this
in a efficient way.
Then you imply that you will have difficulties to implement that. Can you ch
ange your code or not?
If you can, do it the right way.
I can imagine adding a computed column to the table and index that column. W
ould I do that? No. You
would still have to adapt your code for the dirty solution, so better to do
it right up front. :-)
Some elaborations on the subject (showing some options, explaining why IMO D
avid's suggestion is the
ay to go):
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JM" <john@.demon.invalid> wrote in message news:3gontsFdkcgoU1@.individual.net...red">
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the sto
red procedures that
> we're using at the moment and I'll look into that tomorrow when I'm back i
n the office.
> However, if there's a possibility of converting the dates directly (so the
y can be used in a view,
> for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||The easiest way is rarely the best way.
In this case though, the easiest is the best.
Try David's solution.
Maybe you didn't give enough information on what you are trying to do for
the contributers here to find the best solution.
"JM" <john@.demon.invalid> wrote in message
news:3gontsFdkcgoU1@.individual.net...
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the
> stored procedures that we're using at the moment and I'll look into that
> tomorrow when I'm back in the office.
> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||Hi all,
I agree, the (truly) best solution would appear to be David's, and I will
see what I can do with our SPs tomorrow.
When I said 'best' before, I really meant easiest from an implementation
point of view - messy, but easy.
Thanks for all the suggestions. I guess I'm surprised that there isn't a
simple 'round to midnight' function that can be used!
John.
Raymond D'Anjou wrote:
> The easiest way is rarely the best way.
> In this case though, the easiest is the best.
> Try David's solution.
> Maybe you didn't give enough information on what you are trying to do
> for the contributers here to find the best solution.
> "JM" <john@.demon.invalid> wrote in message
> news:3gontsFdkcgoU1@.individual.net...