Solved

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

Posted on 2004-10-18
4
406 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display SQL maintenance plan SQL Code 3 47
SQL Simple Query Taking a Very Long Time 11 36
*** Windows Server 2012 Websites Set Up *** 17 29
Query Task 8 16
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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