Hi

I have created a import process that creates the ActiveX transformation
based on control tables and column mappings.
My question.
Is there a way to easily COUNT the columns in the import file as some of the
files have variable amounts of columns.

Thanks
Peter

Peter,

You haven't really provided any details as to WHERE you want the count. So
here are three possible solutions:

1. Have an ActiveX script open the file, use Split on a line of text from
the file and then see how many elements are in the array. (This is your
best option if each line can have a different column count)
2. Don't specify any source columns in the Data Pump (will show the
transform coming from the top of the left list) and use the Count of the
DTSSource object. This can be used if each file has a consistent column
count. You can use select/case in a ActiveX Transform in the DataPump to
have one routine for each file type as long as the dentination table is
consistent.
3. Use LogParser 2.2 to count the columns for you. Can be called through
COM interaction in an ActiveX script. Only use if you need to as LogParser
is a very powerful tool that I use on occation to speed things up.

Sounds like you are doing alot of custom coding when you could just have
your DTS Package branch to one of mulitple DataPumps. You can use the
DisableStep of each DataPump to activate or deativate each so that only the
one you want runs. That way, you have X routines separate from each other
to handle every type of incoming file. Have an ActiveX Script task, open
the file as in #1 above and then Activate the DataPump that needs to run and
Disable the DataPumps that don't. Below is an example code:

Option Explicit
Function Main()

Dim oPackage
Dim oDataPump1
Dim oDataPump2

set oPackage = DTSGlobalVariables.Parent
set oDataPump1 = oPackage.Steps("DTSStep_DTSDataPumpTask_1")
set oDataPump2 = oPackage.Steps("DTSStep_DTSDataPumpTask_2")

if (some test to see which to run) then
oDataPump1.DisableStep = False
oDataPump2.DisableStep = True
oDataPump1.ExecutionStatus = DTSStepExecStat_Waiting
else
oDataPump1.DisableStep = True
oDataPump2.DisableStep = False
oDataPump2.ExecutionStatus = DTSStepExecStat_Waiting
end if

Main = DTSTaskExecResult_Success
End Function

This will execute one or the other DataPumps. You simply draw this in the
DTS Designer as an ActiveX Script with two exiting workflows to two
different DataPumps. Only one with execute at a time however. This will
modularize your code and separate it out so you don't have this huge code
that needs to be debugged or modified in the future. If you are having a
problem with a particular file type, modifiy that one only.

Just my 2-cents worth,
Scott

"Peter" <Peter (AT) discussions (DOT) microsoft.com> wrote