Importing Data to an exsisting table

Posted on 2004-11-04
Last Modified: 2008-02-01
below is an ActiveX script I am trying to create. What I want to do is import data from an excel spread sheet to my table in my SQL Server. It is a simple import with each field having the same Desintation and Source name. Since the ID field is an Identity, I need to populate it with a number because the ID field in the excel spread sheet is blank. I thought of running some type of loop to populate the ID (x as an integer) field and at the same time import the other data. Can this be done? Help John

'  Visual Basic Transformation Script

'  Copy each source column to the destination column
Function Main()

      DTSDestination("ID") = x
      DTSDestination("SSN") = DTSSource("SSN")
      DTSDestination("PaySource") = DTSSource("PaySource")
      DTSDestination("FILLDATE") = DTSSource("FILLDATE")
      DTSDestination("BillDate") = DTSSource("BillDate")
      DTSDestination("RX") = DTSSource("RX")
      DTSDestination("PHARMACEUTIC") = DTSSource("PHARMACEUTIC")
      DTSDestination("DrugCompany") = DTSSource("DrugCompany")
      DTSDestination("DOCTOR") = DTSSource("DOCTOR")
      DTSDestination("QTYISSUED") = DTSSource("QTYISSUED")
      DTSDestination("PRICE") = DTSSource("PRICE")
      DTSDestination("OVER1") = DTSSource("OVER1")
      DTSDestination("OVER2") = DTSSource("OVER2")
      DTSDestination("Over3") = DTSSource("Over3")
      DTSDestination("ProviderID") = DTSSource("ProviderID")
      DTSDestination("NDC1") = DTSSource("NDC1")
      Main = DTSTransformStat_OK
End Function
Question by:jmoneilthe2
    LVL 12

    Accepted Solution

    1st question:  why are you doing this with an ActiveX transform rather than a copycolumn transform?  In your case, the copy column would be much faster and much easier to maintain.

    The issue with the autonumber would be handled conceptually as follows:  you have the ID field in your SQL table as an incrementing identity column.  You DON'T map anything to it in the copycolumn transform.  SQL takes care of populating it for you--that's the point of incrementing.

    Post here if you want more detail on the copycolumn approach, or you can probably look in BOL.
    LVL 6

    Expert Comment

    by:Duane Lawrence
    You said the id field is an identity, then you don't need to populate it because MS SQL server will automatically increment the number and put it in when a row is inserted.

    If you do try to insert into an identity field you will get an error.


    Author Comment

    Can you explain the copycolumn? I haven't heard of it.
    LVL 12

    Expert Comment

    Are you working in SQL 2000, in the DTS package designer?  I ask so I can provide the most specific information.

    Author Comment

    LVL 12

    Expert Comment

    I'll assume you have all the basics covered:  one connection item for the Excel sheet, one connection for the SQL server.  You need a Transform Data task between them.  After you've place that, double-click it to open the Transform Data Task Properties panel.  

    On the first tab, Source, you select the Excel connection as your source, the Copy Table button, and the name of the worksheet for the table.  (I know that's about right, but if it's not exactly right and you can't figure out what I mean, I'll try to be a little clearer.)

    On the second tab, Destination, you specify the SQL database connection and the name of the SQL table into which to put the results.

    On the third tab, Transformations, you should see two panels and a lot of horizontal lines between the fields.  Here's where you change the ActiveX script transform to the copy column.
    1.  Click Delete All
    2.  Click Add New, then Copy Column, then OK.  This opens the Transformation Options panel.
    3.  On the second tab, Source Columns, select all the fields from the left column into the right column (you can do this by clicking >>).
    4.  On the third tab, Destination Columns, select all the fields from the left column into the right column EXCEPT THE ID COLUMN
    5.  Click OK.  This should take you back to the Transform Data Task Properties panel, and the join between the two tables should look like a bunch of little arrows to each field on each table joined together then connect by a single line (I think that will make sense when you see it).  

    At this point, click OK.  You don't have to do anything on the fourth (Lookups) or fifth (Options) tab.

    Try executing your DTS package.  

    Author Comment

    Thanks pique_tech,
    This helped out a lot.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    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

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now