February 1, 2017 at 1:35 pm
I have A storedprocedure from where i get some columns which is my "OLEDB source"
Source(
Modelcode
,Carrier
,Description
,Manufacterer
)
I use these columns to insert into Destination table which has identity column
Table A
(
ID identity(1,1)
,Carrier
,Description
,Manufacterer
)
I need to use the "Model Code" to do a look up to get ModelKey to Insert into "Reference table" and also need "ID" column from Table A.
So my another Reference table "Xref" table has column like below.
XREf
(
ID,
ModelKey
)
Thing is I need to do all these in same Dataflow task. Can i do it?
If so how?
February 1, 2017 at 2:04 pm
komal145 - Wednesday, February 1, 2017 1:35 PMI have A storedprocedure from where i get some columns which is my "OLEDB source"Source(
Modelcode
,Carrier
,Description
,Manufacterer
)I use these columns to insert into Destination table which has identity column
Table A
(
ID identity(1,1)
,Carrier
,Description
,Manufacterer
)I need to use the "Model Code" to do a look up to get ModelKey to Insert into "Reference table" and also need "ID" column from Table A.
So my another Reference table "Xref" table has column like below.
XREf
(
ID,
ModelKey
)Thing is I need to do all these in same Dataflow task. Can i do it?
If so how?
I can't think of a way of doing this in a single DF. This data model does not feel right to me.
February 1, 2017 at 2:07 pm
Is there any other way to get the tables ?
February 1, 2017 at 2:12 pm
Why does it have to be a single data flow?
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
February 1, 2017 at 2:15 pm
komal145 - Wednesday, February 1, 2017 2:07 PMIs there any other way to get the tables ?
Add a ModelKey column to your Destination table.
Add a lookup in your data flow to add the ModelKey & populate it in the Destination table.
Run a proc after your insert to do an 'INSERT WHERE NOT EXISTS()' from Destination table to XRef table to add the new Id, ModelKey combinations.
February 1, 2017 at 2:17 pm
What i was trying to do is get the Modelcode (from storedprocedure) as we execute the storedprocedure to enter the value to Table A. Use this "Modelcode" to do look up on a another tbale to get "modelkey" and insert "Modelkey" and "ID" both into Xref table. Here i wanted to do in single Dataflow as we are executing Storedproc , and getting value modelcode.I sthere any way we use this modelcode when it runs single time.
February 1, 2017 at 2:26 pm
Phil Parkin - Wednesday, February 1, 2017 2:15 PMkomal145 - Wednesday, February 1, 2017 2:07 PMIs there any other way to get the tables ?Add a ModelKey column to your Destination table.
Add a lookup in your data flow to add the ModelKey & populate it in the Destination table.
Run a proc after your insert to do an 'INSERT WHERE NOT EXISTS()' from Destination table to XRef table to add the new Id, ModelKey combinations.
Thing is I cannot change/Modeify table "TABLE A" which is my destination.
What i was trying to do is get the Modelcode (from storedprocedure) as we execute the storedprocedure to enter the value to Table A. Use this "Modelcode" to do look up on a another tbale to get "modelkey" and insert "Modelkey" and "ID" both into Xref table. Here i wanted to do in single Dataflow as we are executing Storedproc , and getting value modelcode.I sthere any way we use this modelcode when it runs single time.
February 1, 2017 at 3:00 pm
komal145 - Wednesday, February 1, 2017 2:26 PMThing is I cannot change/Modeify table "TABLE A" which is my destination.What i was trying to do is get the Modelcode (from storedprocedure) as we execute the storedprocedure to enter the value to Table A. Use this "Modelcode" to do look up on a another tbale to get "modelkey" and insert "Modelkey" and "ID" both into Xref table. Here i wanted to do in single Dataflow as we are executing Storedproc , and getting value modelcode.I sthere any way we use this modelcode when it runs single time.
Not that I can think of.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply