Using Variable

Question

Post reply

Using Variable

rew-370421

Hall of Fame

Points: 3640
More actions
February 12, 2010 at 1:19 pm

#156671

I have a job that Imports EXCEL data into a Database Table. I also need to update ONE more Database column when this job runs with the same constant value (e.g., "X").
I've already created a Global Variable and assigned it the value of "X" within my SSIS Package.
Could someone please tell me how I can access that Variable (that contains the letter "X") and use it along with my EXCEL data?
Details, PLEASE!
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

hussain27syed Ten Centuries Points: 1191 More actions · Answer 1

could you explain further what you exactly requires.

from what i understand you want to add an extra column while loading excel data.

as in if excel has column A,B u want to add another column C (which has a constant value) and the final data should have A,B,C

to do this you can try using Derived column component. Just drop your global variable in the expressions

rew-370421 Hall of Fame Points: 3640 More actions · Answer 2

Thanks for responding Hussain27syed.

Here's my situation:

I have an EXCEL spreadsheet with many columns. I am IMPORTing just a few of them into a Database Table. I have ONE remaining COLUMN in the Database TABLE that I must also update with a Constant (hard-coded) value of "X".

Again, that ONE addition Database Table Column that will NOT be populated with data from the EXCEL spreadsheet. That ONE Column in the Database Table will be populated with a hard-coded (constant) value of "X" or "Y" based on the EXCEL spreadsheet being imported. For this particular job I know the value of the Database Table Column will be updated with the value of "X" every time.

Can I create a SSIS package that has a Variable with a value of "X" and when I do my MAPPINGs from EXCEL to the Database Table, also map" that Global VARIABLE to that ONE remaining Column?

I hope that's clear?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

It's simple to do this - add a Derived Column transformation with one new column and set its value to "X".

Now map this new column to your destination column as required.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

Phil Parkin (2/13/2010)
It's simple to do this

...simple for you 😀

sorry, I'm now googling a solution to this error:

"the destination component does not have any availabe inputs for use in creating a path"

I dragged my Variable name into the DERIVED COLUMN and then tried to connect that OUPUT Arrow to my SQL Destination and it gave me the error above.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

I think I know what you've done. Your destination cannot have more than one input.

You need to delete the mapping from source to destination. Then add source to derived column and then derived column to destination. You should not lose all your mappings when you do this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rew-370421 Hall of Fame Points: 3640 More actions · Answer 6

THANK YOU, Phil !

Between your help and Googling to get some details, this finally worked.

It's easy when you know how. 🙂