Solved

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

Posted on 2004-10-18
4
399 Views
Last Modified: 2008-02-01
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
Comment
Question by:vijaya_lakshmi
4 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12344690
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
 

Author Comment

by:vijaya_lakshmi
ID: 12344842
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now