Importing Data to an exsisting table

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Duane LawrenceCommented:
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.

jmoneilthe2Author Commented:
Can you explain the copycolumn? I haven't heard of it.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Are you working in SQL 2000, in the DTS package designer?  I ask so I can provide the most specific information.
jmoneilthe2Author Commented:
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.  
jmoneilthe2Author Commented:
Thanks pique_tech,
This helped out a lot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.