Solved

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

Posted on 2004-10-18
4
398 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

20 Experts available now in Live!

Get 1:1 Help Now