I'm assuming this is a simple question, I can't find a solution though.
Hopefully this makes sense

1) I have an excel file with the following columns:
First Name
Last Name
Email

2) I have a table with the following columns:
ID (primary key)
ExcelID (int not null)
FirstName varchar
LastName varchar
Email varchar

ExcelID is a number that is used to determine what file the user information
came from... it isn't a part of the excel file. Basically I want to run a DTS
that inserts the excel file columns AND sets ExcelID to a value that I'll be
entering.

I can import the data from excel just fine. How do I add an option to set a
column (in the database) just to a VALUE not a reference to a column in the
spreadsheet?

Ashton

You need to create a ActiceX Script in the Transformation tab.

A Panchanathan

ashton wrote:

Hello ashton,

You need to assign the value of the Excel connection's DataSource property
to a global variable.

Now you have that, in your transformations section of the Data Pump task
you highlight the destination column and add an Active X transform. Check
there are no source columns.

In the script you do this

DTSDestination("MyColumn") = DTSGlobalVariables("My Varaible").Value


When you look at this transform in the designer you will see it originate
from thin air above the Source Columns box and end at your destination column.


Allan