Hi All,
I'm importing a text file to a table. I only want some columns info from the
text file so my transformation looks like this.
DTSDestination("AddressCountry") = DTSSource("Home Country")
DTSDestination("AddressZip") = DTSSource("Home Postal Code")
DTSDestination("AddressState") =DTSSource("Home State")
DTSDestination("AddressCity") = DTSSource("Home City")
If (DTSSource("Home Street 3") <> " ") Then
DTSDestination("AddressAddLine3") = DTSSource("Home Street 3")
End If
If (DTSSource("Home Street 2") <> " ") Then
DTSDestination("AddressAddLine2") = DTSSource("Home Street 2")
End If
DTSDestination("AddressAddLine1") = DTSSource("Home Street")
DTSDestination("AddressLastName") = DTSSource("Last Name")
DTSDestination("AddressMiddleName") = DTSSource("Middle Name")
DTSDestination("AddressFirstName") = DTSSource("First Name")

This is an schedule pkg. Sometimes some columns doesn't exist in the text
file like DTSSource("Home Street 3").
How can I skip that column in my transformation properties?
Tks in advance
JFB

JFB:

I assume you are saying that in some cases the HOME STREET 3 column is
completly missing from the file to where there is now one less column in the
file. If this is the case, you will have to have the DTS task leave the
header row as a record and use it to see what fields are present and not
present and set up Global Vars for use while the file is processing. See
DTSTransformStat_SkipInsert so that your headers don't post as a record in
your table. You can also use this technique if the columns don't always
come to you in the same order. You will have to access your columns by
position instead of name as well. Unfortunately, this may not always work
(I've encountered problems with files where one record has 5 columns and the
next has 6, or you design your task with 5 fields and a file comes in with 6
etc) and you may have to revert to using a single field per row and splice
your data up using Split function and do everything using ActiveX Script.
Test it for your situation and choose the best option for you. I've used
both for different situations. The second option requires more work, but
does work for more situations.

Scott

"JFB" <jfb (AT) newSQL (DOT) com> wrote

mmm... Sounds little complicated...
I got this solution: Read the file check for the first line where are all
the field names and if is missing some fields I put the list missing at the
end of the line (,home street 2, home street 3) --> resave the file and
execute the dts pkg.
Everything works without issue.
Rgds and tks for you reply and help
JFB


"Wm. Scott Miller" <Scott.Miller (AT) spamkillerwvinsurance (DOT) gov> wrote

JFB:

Sounds like a good solution if you are only doing this once. However,
thinking along the lines of automation (you did say "scheduled" in your
original message), evaluate the frequency of this same issue occuring in the
future. How often will it occur? How often can it occur? If the answer to
both is not "slim to none" then you should program for it. Otherwise you
are just plugging a hole now that will spring a leak later and you may not
be there later to guide the "new guy" on how to fix it with your "easy
solution."

The exceptions to the rules will always kill you as a programmer until you
start thinking of them and making sure they can't happen. Bet the
programmers at ComAir wish they had thought of the extreme cases now because
that line of code that crashed the whole system could have been prevented
has a programmer asked "What if?". :-)

Scott

"JFB" <jfb (AT) newSQL (DOT) com> wrote

I know what you mean and tks for your recommendations.
I always do a documentation inside and outside any package, also I share all
this info with another coworker.
This package is already scheduled and looks like doesn't have any problems.
Tks again
JFB

"Wm. Scott Miller" <Scott.Miller (AT) spamkillerwvinsurance (DOT) gov> wrote