Using input columns within update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Using input columns within update statement

Author

Topic

cfair_ssis
Starting Member

6 Posts

Posted - 2010-05-18 : 12:35:49

I am trying to update an access table using an update statement (within an OLE DB Command Transformation - not sure if this is the right way to accomplish this). I would like to use the input columns from my previous Lookup transformation to dynamically populate the Update statement. I'm hoping there is a way to do this. Is it possible to use the input columns passed in from a previous transformation step as values in your queries?

Here is my update query showing where I would like to use the input columns:
UPDATE [Agent Master List] SET [Agent Master List].[UW Tech] = "<Foo>" <---Want to use the Input Column 'UW Tech' to pass this value
WHERE (([Agent Master List]![Agency Code]="<123456>"));<---Want to use the Input Column 'agency_code' to pass this value

Thank you in advance if you can help me with this!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 14:23:47

put like

UPDATE [Agent Master List] SET [Agent Master List].[UW Tech] = ?
WHERE (([Agent Master List]![Agency Code]=?

and in mapping tab map the two place holders to your relevant fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cfair_ssis
Starting Member

6 Posts

Posted - 2010-05-18 : 15:08:12

I tried putting in the question marks in my query and got the following error message: There is more than one data source column with the name "?". The data source column names must be unique.

However, you definitely pointed me in the right direction. In 'Input and Output Properties' -> 'OLE DB Command Input' -> 'External Columns' I created two new columns ac_param and uwt_param. My final SQL command is as follows:

UPDATE [Agent Master List] SET [Agent Master List].[UW Tech] = uwt_param
WHERE (([Agent Master List]![Agency Code]= ac_param))

Getting exactly the behavior I wanted! Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources