jmoneilthe2
asked on
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"
DTSDestination("FILLDATE")
DTSDestination("BillDate")
DTSDestination("RX") = DTSSource("RX")
DTSDestination("PHARMACEUT
DTSDestination("DrugCompan
DTSDestination("DOCTOR") = DTSSource("DOCTOR")
DTSDestination("QTYISSUED"
DTSDestination("PRICE") = DTSSource("PRICE")
DTSDestination("OVER1") = DTSSource("OVER1")
DTSDestination("OVER2") = DTSSource("OVER2")
DTSDestination("Over3") = DTSSource("Over3")
DTSDestination("ProviderID
DTSDestination("NDC1") = DTSSource("NDC1")
Main = DTSTransformStat_OK
End Function
'*************************
' Visual Basic Transformation Script
'*************************
' Copy each source column to the destination column
Function Main()
DTSDestination("ID") = x
DTSDestination("SSN") = DTSSource("SSN")
DTSDestination("PaySource"
DTSDestination("FILLDATE")
DTSDestination("BillDate")
DTSDestination("RX") = DTSSource("RX")
DTSDestination("PHARMACEUT
DTSDestination("DrugCompan
DTSDestination("DOCTOR") = DTSSource("DOCTOR")
DTSDestination("QTYISSUED"
DTSDestination("PRICE") = DTSSource("PRICE")
DTSDestination("OVER1") = DTSSource("OVER1")
DTSDestination("OVER2") = DTSSource("OVER2")
DTSDestination("Over3") = DTSSource("Over3")
DTSDestination("ProviderID
DTSDestination("NDC1") = DTSSource("NDC1")
Main = DTSTransformStat_OK
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pique_tech
Can you explain the copycolumn? I haven't heard of it.
Can you explain the copycolumn? I haven't heard of it.
Are you working in SQL 2000, in the DTS package designer? I ask so I can provide the most specific information.
ASKER
Yes
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.
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.
ASKER
Thanks pique_tech,
This helped out a lot.
John
This helped out a lot.
John
If you do try to insert into an identity field you will get an error.
Duane