Hi All,

I'd like to have a global variable which is reset on every row in my data
pump task.I tried to reset my global variable in the first first field of my
column mapping in Data pump task ,but to my surprise there is no order for
columns to be transfered in each record.

Any suggessions?

Thanks

Could you elaborate?

Reset with what?

I am not sure what you are trying to do.


"RayAll" <RayAll (AT) microsft (DOT) com> wrote

I have four field in each row:

Field1 Field2 Field3 Field4

I'd like to set my global variable to 1 in Filed1 and if in the other fields
a validation error ocuured I set it to 0 and in the rest because it's 0 I
don't check the validation,but this mechanism should be reset in every row.I
tried it but Data pump task (which I'm using to pump data from a CSV file
into a Table) ,transforms fields in each row ,with no special order (I
thought fields are transformed from let to right) that's why I came up with
this idea.

Makes sence?

Thanks for your help

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

What type of validation errors?

You don't need a GV at all as far as I can see

Say I want to see if Col2 on the Source == 2 and if it is I set
Destination Col3 to 1 and if not I set it to 0


DTSDestination("Col2") = DTSSource("Col2")

If DTSSource("Col2") = 2 THEN
DTSDestination("Col1") = 1
ELSE
DTSDestination("Col1") = 0
END IF


If the Source is SQL Server then you do not even need to do it here at
all you can do it in the SourceSQLStatement

SELECT
Col2,
CASE WHEN Col2 = 2 THEN 1 ELSE 0 END as Validation
FROM
TABLE

Make sense?

Allan


"ALI-R" <Ray (AT) Alirezaei (DOT) com> wrote

Yes ,you are right ,but I don't want other fields in the same row overwrite
the column I have associated with having error status per row.

I'd like to write to that field(error status) only once ,because I'm
inserting each field's validation error to a seperate table and I want this
field only shows that wether there is an error happened in this row or not
,for details I would use the other table.there might be 10 errors per row
,but I want to update this field only once.

Dose that make sence?do you have any better suggessions?

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

This validation will occur on each Row*Row. You may want to experiment
with doing this up front of the DataPump task. All your valid Qs
recently have been based around the Lookup. Remember this operates on
EVERY row so IMHO does not scale well on large datasets particularly


Allan

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Allan,

I have 33 fields in each record and I have to validate each field anyways
and log every single validation error in a seperate table(**NO DOUBT ABOUT
IT**).in my senario the only thing which is added is to check if the
status_code is not set before and if not set it to 1(which mean critical
error).I'd like to use this field for further processing because output of
this step would be input to another DTS package which dose the business
rules on the records which are ok (I mean there is no validation error
occured on them).I know there might be performance issue regarding this
scenario.But this proecess is supposed to be launched at midnigh and for
client what is important is not the performance of the system ,but the
ability to tell them what was wrong (in very detail) with their CSV file

Thanks and sorry for asking lots of questions.Now I have a very bright
underesanding of what I m going to accomplish.

I so appreciate your help.

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

Allan,

I have 33 fields in each record and I have to validate each field anyways
and log every single validation error in a seperate table(**NO DOUBT ABOUT
IT**).in my senario the only thing which is added is to check if the
status_code is not set before and if not set it to 1(which mean critical
error).I'd like to use this field for further processing because output of
this step would be input to another DTS package which dose the business
rules on the records which are ok (I mean there is no validation error
occured on them).I know there might be performance issue regarding this
scenario.But this proecess is supposed to be launched at midnigh and for
client what is important is not the performance of the system ,but the
ability to tell them what was wrong (in very detail) with their CSV file

Thanks and sorry for asking lots of questions.Now I have a very bright
underesanding of what I m going to accomplish.

I so appreciate your help.

Ray

I really think you should take this outside of dts. Normally when
people handles things like this from my experience and need to validate
data, they write some code, depending on your programming language of
choice, to read through the file, record by record, do the validation,
and then save the result to 1 or more other files, but probably just
one in your case. That way, you're not trying to do too complicated of
things inside dts, you have full control over what happens when you
encounter a problem, and you can clean everything up to just let dts
get your data in the database fast, which in my opinion is really what
it's ideal for.

Yes,you are right up to a point .But my case is a little bit different with
just simply parsing the CSV file and making a Dataset.

I have 16 Lookup Tables that need to be accessible through the
Transformation. You are not saying that I shouldn't use DTS for
Transformation at ll,do you?
I need to do validation + Business rules on each record which is very
difficult to be done through ADO for insatcne ,I am doing the business rules
simply by using a Trigger or a Trnasactional stored procedure and I don't
transfer any of lookup tables or tables are used for business rules to the
client ,everything is checked on the server.Don't you think that it has a
big perfomance advantage to load a table with 2 thousand records to Dataset
(or whatever) to check the business rules? On the other hand ,if you decide
to do everything outside DTS when your validation is done ,your business
rules is done ,I need another mechanism to split records into different
Tables.Is there a better mechanism tham using lookups in a Data pump task to
do so? I do agree that I don't have that much flexibility working with DTS
but it saves me from thousands lines of codes and the time.

Anyways thanks for your hint.it was the idea that I came up with at the
beginning but ,my businedd rules are very complicated and transformation is
also very tricky ,so I decided to use DTS and so far ,I'm pretty satisfied
with tthe overall progress (except some little issues line whay I posted
below) which I think could be solved by some tricks which I am not aware of.

Thanks for your help,

Ray
"shriop" <shriop (AT) hotmail (DOT) com> wrote