OLE DB command with Sqlcommand as a Select Statement...

Question

Hello Experts,

I have a OLE DB command with following select in Sqlcommand

SELECT ISNULL(UOM_RID, 'NULL') PRC_UNIT FROM T_UOM where UOM_COD = ?

above select is like a lookup to fetch a price unit based on the currency code, for example above expression will evaluate to, as follows...

SELECT ISNULL(UOM_RID, 'NULL') PRC_UNIT FROM T_UOM where UOM_COD = 'CAD', where CAD is coming from transformation just above to this with other columns too...it is basically every row coming from source db, has to look up in another database to find PRC_UNIT and all columns from source and PRC_UNIT will go to new database table...question is...

I am not sure how to reference PRC_UNIT column name that I gave in OLE DB sql command, to my destination transformation...hope it make sense....??? I can see all columns coming from Source DB, but dont know about PRC_UNIT!!!

Thanks,

Suresh

Answer 1

Hi Suresh,
Can you not modify the source query to fetch this data initially using joins?

---

Hope this helps !!
Sudeep   |    My Blog

Answer 2

You can't use an OLE DB Command to return a value to the data flow.  Instead use the Lookup Component.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 3

You can take all the UOM_COD into a temp/staging table (##TEMPTABLE) inside the DFT.
Then at control flow, you can take a execute sql task with query like:

SELECT ISNULL(UOM_RID, 'NULL') PRC_UNIT INTO ##TEMP  FROM T_UOM where UOM_COD IN
(SELECT UOM_COD FROM ##TEMPTABLE)

Now you have all the PRC_UNIT column values are in ##TEMP table.
Use this ##TEMP table inside a different DFT if required

---

Nitesh Rai- Please mark the post as answered if it answers your question