I am working on a DTS job to do some file manipulation and renaming and
so on in preparation for picking up a file from an FTP source.

Based on a suggestion in this forum (thank you Allen Mitchell), I do a
count in a sql task:

select count(*) as amount from mytablename where filedate = ? with the ?
the globalvariable I have already set as the input filename.

Later on, after I do my file manipulation, I want to write out to a sql
table the value in this golbal variable called filedate. However, I
only want to do this if the global variable counter = 0 (i.e. a new
input file into this job).

If it's > 0, then I just want to continue with the job, and re-use the
same input text file into the sql job.

The statement in sql that I want to write is:

insert logfile
values (?) where (??) = 0

in the above, the parameter (?) is equal to the text filename. The
variable (??) is a second parameter, which is the counter I set with the
earlier sql task.

When I try to code something like that, everything works fine up thru
"insert logfile values(?)" where I can set the parameter. If I try to
add the second one, it'll give me an error "Parameter Information cannot
be derived from SQL statements"

How do you do the second prarameter to be in the where statement of the sql?

Or, since I know the value of the global variable (if =1 don't do
anything, if =0 then I want to write out the filename in my table) prior
to the sql step?

BC

This statement

insert logfile
values (?) where (??) = 0

Would never work

You can only use the WHERE when you are using a SELECT statement to do the
entering NOT with the VALUES construct

Ok So what you need to do as far as I can see is this

If the global variable holding the counter is 0 then you want to enter a row
into the table.

You need workflow. In An active Script task you have two tasks leading from
it. You then test the value of the global variable in the Active Script
task. If it is 0 then you enable and disable the appropriate steps likewise
if its > 0

Here is an example

Multiple Paths in Workflow
(http://www.sqldts.com/default.aspx?218)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Blasting Cap" <goober (AT) christian (DOT) net> wrote

But this would work just fine (and would avoid the workflow hassle)

Insert into logfile
Select ? where (?) = 0

With Parm 1 being the select value and parm2 being in the where clause.

Bill


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

Are you saying that you have this working?

AFAIK it shouldn't.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Bill Swartz" <wsaharem (AT) yahoo (DOT) com> wrote

Allan Mitchell wrote:

Active Script task. If it is 0 then you enable and disable the
appropriate steps likewise if its > 0

DTSStep_DTSActiveScriptTask_1

come from?

I'm not following how that dts name above resolves to A in the example.

I see what it's doing, but not sure if I understand how it knows where
to go, based on the names within SetupPath and A,B,C and X,Y,Z.

BC

Please see my reply to your other post about the same thing

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Blasting Cap" <goober (AT) christian (DOT) net> wrote