Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Importing Data to an exsisting table

Posted on 2004-11-04
Medium Priority
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
  • 3
  • 3
LVL 12

Accepted Solution

pique_tech earned 2000 total points
ID: 12496886
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.

Expert Comment

by:Duane Lawrence
ID: 12496904
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

ID: 12497042
Can you explain the copycolumn? I haven't heard of it.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

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

Author Comment

ID: 12497162
LVL 12

Expert Comment

ID: 12497308
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

ID: 12497710
Thanks pique_tech,
This helped out a lot.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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
Suggested Courses

577 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