Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Variable Expand / Collapse
Author
Message
Posted Friday, February 12, 2010 1:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:20 AM
Points: 205, Visits: 1,348
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
Post #864915
Posted Saturday, February 13, 2010 3:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 13, 2011 3:28 AM
Points: 41, 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
Post #865059
Posted Saturday, February 13, 2010 8:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:20 AM
Points: 205, Visits: 1,348
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?

Post #865094
Posted Saturday, February 13, 2010 12:23 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #865155
Posted Sunday, February 14, 2010 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:20 AM
Points: 205, Visits: 1,348
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.
Post #865264
Posted Sunday, February 14, 2010 8:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #865265
Posted Monday, February 15, 2010 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:20 AM
Points: 205, Visits: 1,348
THANK YOU, Phil !

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

It's easy when you know how.
Post #865566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse