Solved

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
SQL Stored Proc - Performance Enhancement 15 52
Row insertion failed. Array 5 46
Shrink multiple databases at once 4 16
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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

791 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