Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

How to identify whether a source column exists or not in DTS transformation task?

Hi,

  I Need to transform data from Excel sheet to a SQL Table. I created a DTS package for doing this. Basically the excel file is uploaded from a web page and I call the DTS package to dump the data in that excel file to SQL. Every thing is working fine but we find a problem in that.

My DTS Package has a source excel file and Destination SQL Table and I use DTS Transformation task to dump data from Excel to SQL.  The Active script I used in Transformation task looks like below -

Function Main()
      DTSDestination("IID") = DTSSource("IndicatorID")
      DTSDestination("CID") = DTSSource("StateID")
      DTSDestination("year") = DTSSource("Year")
      DTSDestination("groupID") = DTSSource("GroupID")
      DTSDestination("dValue") = DTSSource("DataPoint")
      Main = DTSTransformStat_OK
End Function

But what happened is some of the excel files we are uploading have  the source column "Data Point" observe the space between Data and point and some of them have "datapoint". Is there a simple solution to read any column name "DataPoint" or "Data Point" and assign it to the destination column some thing similar to the one I am showing below which is not working:((

Function Main()
      DTSDestination("IID") = DTSSource("IndicatorID")
      DTSDestination("CID") = DTSSource("StateID")
      DTSDestination("year") = DTSSource("Year")
      DTSDestination("groupID") = DTSSource("GroupID")
      if exists(DTSSource("DataPoint"))  then
            DTSDestination("dValue") = DTSSource("DataPoint")
      elseif exists(DTSSource("Data Point"))  then
            DTSDestination("dValue") = DTSSource("Data Point")
      End If
      Main = DTSTransformStat_OK
End Function.

And also one more issue with same SourceColumn.

I have a source column called SrcID which is optional column . Some of the Excel files have it and some of them not.

So if it exists then I have to use the code

       DTSDestination("SrcID") = DTSSource("SrcID")

else nothing.

Could any body tell me what is the easier way to accomplish this in DTS transformations.

thank you,
-Vijaya.
0
vijaya_lakshmi
Asked:
vijaya_lakshmi
1 Solution
 
arbertCommented:
There really isn't a whole lot you can do (until DTS2005).  Have you thought about using OPENQUERY from an ExecuteSQL task to directly read the excel files (you could insert into a temp table, or directly work with it using standard SQL)?

Brett
0
 
vijaya_lakshmiAuthor Commented:
No I didn't think of any other ways of working it out. Since it is already an existing Package I am thinking of some simple ways. I never tried using ExecuteSQL (I dont have much hands on experience with all the DTS tasks). I will try that.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now