Hi all,
At the end of transfer dts task there is always information of how many rows were transferred. Is it possible to get this information into a global variable and pass it out of the package, let's say to the application calling it?
Any suggest appreciated
---------------------
Best regards,
Gosia Ndreu

The amount of rows transferred in a DataPump task can be gleaned from the

..RowsComplete property and also look at the RowsInError property

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Malgorzata Ndreu" <mndreu (AT) maritweb (DOT) com> wrote


Hi all,
At the end of transfer dts task there is always information of how many rows
were transferred. Is it possible to get this information into a global
variable and pass it out of the package, let's say to the application
calling it?
Any suggest appreciated
---------------------
Best regards,
Gosia Ndreu

Thank you very much, maybe you know how it can be done from Data Driven Query task in case i have update query and insert query (separately for each)?

--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I have never tried to capture them for the DDQ but the properties mentioned
are available. The problem being though that they are a property of the
Task and not the individual queries so I would imagine you will see a total
for the task and not seperate totals for each query.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"mndr" <mndreu (AT) maritweb (DOT) com> wrote

Thank you very much, maybe you know how it can be done from Data Driven
Query task in case i have update query and insert query (separately for
each)?

--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Is it maybe possible to put such information into a variable in some kind of loop, and what about reading the variable value out of the package?

--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

There is one more problem with that, in DDQ task i run a conditional update query, in case source records do not satisfy condition i have SkipRow status, but actually at the end i get the complete number of rows from the source, even they wrere not processed (or maybe were in some meaning?)
What can i do to get the number of rows in fact updated not aolny read?
Thanks for reply

--
---------------------
Best regards,
Gosia Ndreu


"mndr" <mndreu (AT) maritweb (DOT) com> wrote

Is it maybe possible to put such information into a variable in some kind of loop, and what about reading the variable value out of the package?

--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

If you skip the row then I presume nothing is actually updated.

The DDQ may not be what you are after.

For INSERTs I use a DataPump task and do the counting there.
For UPDATEs I use an ExecuteSQL task and read the rows affected into a
variable.

Maybe you can do this also.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"mndr" <mndreu (AT) maritweb (DOT) com> wrote

There is one more problem with that, in DDQ task i run a conditional update
query, in case source records do not satisfy condition i have SkipRow
status, but actually at the end i get the complete number of rows from the
source, even they wrere not processed (or maybe were in some meaning?)
What can i do to get the number of rows in fact updated not aolny read?
Thanks for reply

--
---------------------
Best regards,
Gosia Ndreu


"mndr" <mndreu (AT) maritweb (DOT) com> wrote

Is it maybe possible to put such information into a variable in some kind of
loop, and what about reading the variable value out of the package?

--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks a lot, probably yes, i have just found out the new DDQ task and was happy to have very powerfull tool to do everything what i need in one step, but unfortunately my expectations were too high
thanks a lot for your help
--
---------------------
Best regards,
Gosia Ndreu


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You could make life a little easier on yourself and just pop the count information out to a table. This would allow easy access to the info and history if you really wanted it. You also end up with lots of flexibility with very little code

Regards
John Miceli, MCDBA

The only problem with that method is that in a moderately used system that
figure by the time you get to doing the data transfer may be out of date.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


<John Miceli>; "MCDBA" <jmiceli (AT) wrberkley (DOT) com> wrote

history if you really wanted it. You also end up with lots of flexibility
with very little code.