"Phill" <no_reply_pacarter@bellpotter.com.au> wrote in message
news:O5GvmJgFEHA.2472@TK2MSFTNGP10.phx.gbl...
> Just wanted to sound out the group to see if anyone has come across a
> problem that came up recently.
>
> We have 30 something DTS packages running on SQL 2K sp3a, W2k Server sp4
> that copy individual tables from a Progress database via ODBC. The first
> thing that each of these packages do, is run an ExecuteSQL task to get a
row
> count from the Progress table it's copying. The ExecuteSQL task is just
> "SELECT COUNT(field_y) FROM Table_x". This count is then stored in a
global
> variable which is used later to verify the copy was successful.
>
> We used to get the row count from the local SQL table prior to the copy
> because it was much quicker. However, recent process changes have required
> us to perform the record count directly against the Progress database.
>
> Since this change there have been isolated instances where the global
> variable mapping on the parameters dialog has gone missing. The ExecuteSQL
> task runs successfully, but the global variable is set to zero. This
causes
> problems later in the package because it thinks it should be copying zero
> records.
>
> For a single package, this same task works for one execution and failed
the
> next. When I checked the parameter mapping dialog, it had <none> for the
> global variable. In three separate executions of all packages, one package
> has failed twice with this error. A few others have run successfully
> one-time, only to fail on the next run.
>
> Any ideas on tracking this problem down would be most appreciated.
>
>
> --
> Thanks
> Phill Carter
> #562421
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:uYiNwciFEHA.2600@TK2MSFTNGP12.phx.gbl...
> Quick tests you can perform as this would seem to be intermittent.
>
> Can you store the rowcounts in a field and grab that?
> Does it work everytime?
> What happens if you try to grab something else?
> Does that work everytime.
>
> Where I am going is, is it only the COUNT(*) that goes wrong?
> If it is then could it be timing out?
>
> --
>
> ----------------------------
>
> 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
>
>
> "Phill" <no_reply_pacarter@bellpotter.com.au> wrote in message
> news:O5GvmJgFEHA.2472@TK2MSFTNGP10.phx.gbl...
> > Just wanted to sound out the group to see if anyone has come across a
> > problem that came up recently.
> >
> > We have 30 something DTS packages running on SQL 2K sp3a, W2k Server sp4
> > that copy individual tables from a Progress database via ODBC. The first
> > thing that each of these packages do, is run an ExecuteSQL task to get a
> row
> > count from the Progress table it's copying. The ExecuteSQL task is just
> > "SELECT COUNT(field_y) FROM Table_x". This count is then stored in a
> global
> > variable which is used later to verify the copy was successful.
> >
> > We used to get the row count from the local SQL table prior to the copy
> > because it was much quicker. However, recent process changes have
required
> > us to perform the record count directly against the Progress database.
> >
> > Since this change there have been isolated instances where the global
> > variable mapping on the parameters dialog has gone missing. The
ExecuteSQL
> > task runs successfully, but the global variable is set to zero. This
> causes
> > problems later in the package because it thinks it should be copying
zero
> > records.
> >
> > For a single package, this same task works for one execution and failed
> the
> > next. When I checked the parameter mapping dialog, it had <none> for the
> > global variable. In three separate executions of all packages, one
package
> > has failed twice with this error. A few others have run successfully
> > one-time, only to fail on the next run.
> >
> > Any ideas on tracking this problem down would be most appreciated.
> >
> >
> > --
> > Thanks
> > Phill Carter
> > #562421
> >
> >
>
>