SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Variable


Using Variable

Author
Message
rew-370421
rew-370421
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1382
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
hussain27syed
hussain27syed
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 112
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
rew-370421
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1382
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
Phil Parkin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25528 Visits: 20667
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rew-370421
rew-370421
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1382
Phil Parkin (2/13/2010)
It's simple to do this


...simple for you :-D

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
Phil Parkin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25528 Visits: 20667
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rew-370421
rew-370421
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1382
THANK YOU, Phil !

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

It's easy when you know how. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search