November 27, 2018 at 12:15 am
HI All,
I am struggling to use table value CLR function in OLE DB command in data flow task.
I am trying to give the input from OLEDB source , each row input to a CLR table valued Function which in tern will give more rows like table ,which i wanted to insert in a table via OLE DB destination.
I am unable to do it in OLE DB command.
I tried like below in OLE DB Command.
select col1, col2,col3
from dbo.clrfunction(?,?,?,?)
but col1, col2 , col3 are not coming in available output.
Have any one faced these type of scenario ? Please help me> thanks in advance
November 27, 2018 at 5:23 am
JoNTSQLSrv - Tuesday, November 27, 2018 12:15 AMHI All,I am struggling to use table value CLR function in OLE DB command in data flow task.
I am trying to give the input from OLEDB source , each row input to a CLR table valued Function which in tern will give more rows like table ,which i wanted to insert in a table via OLE DB destination.
I am unable to do it in OLE DB command.
I tried like below in OLE DB Command.
select col1, col2,col3
from dbo.clrfunction(?,?,?,?)but col1, col2 , col3 are not coming in available output.
Have any one faced these type of scenario ? Please help me> thanks in advance
Why are you using OLEDB Command here, and not just OLEDB source and OLEDB destination?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 27, 2018 at 6:19 am
Phil Parkin - Tuesday, November 27, 2018 5:23 AMJoNTSQLSrv - Tuesday, November 27, 2018 12:15 AMHI All,I am struggling to use table value CLR function in OLE DB command in data flow task.
I am trying to give the input from OLEDB source , each row input to a CLR table valued Function which in tern will give more rows like table ,which i wanted to insert in a table via OLE DB destination.
I am unable to do it in OLE DB command.
I tried like below in OLE DB Command.
select col1, col2,col3
from dbo.clrfunction(?,?,?,?)but col1, col2 , col3 are not coming in available output.
Have any one faced these type of scenario ? Please help me> thanks in advance
Why are you using OLEDB Command here, and not just OLEDB source and OLEDB destination?
Good question.
Thanks for your reply.
I am trying to use Balance data distributor, i.e., from OLEDB source to BDD to split million of records into 4 parts for parallel processing.
so after BDD i need to send the input to CLR function which inturn will give me more records for 1 row.
So i am searching a SQL processing task in Data flow but only OLEDB can handle SQL so i am trying to do with that.
Can you please tell me if i am doing right ? or is there any way ?
I am trying with script component but i feel like it will be performance killer.
Please help me out
November 27, 2018 at 6:36 am
JoNTSQLSrv - Tuesday, November 27, 2018 6:19 AMPhil Parkin - Tuesday, November 27, 2018 5:23 AMJoNTSQLSrv - Tuesday, November 27, 2018 12:15 AMHI All,I am struggling to use table value CLR function in OLE DB command in data flow task.
I am trying to give the input from OLEDB source , each row input to a CLR table valued Function which in tern will give more rows like table ,which i wanted to insert in a table via OLE DB destination.
I am unable to do it in OLE DB command.
I tried like below in OLE DB Command.
select col1, col2,col3
from dbo.clrfunction(?,?,?,?)but col1, col2 , col3 are not coming in available output.
Have any one faced these type of scenario ? Please help me> thanks in advance
Why are you using OLEDB Command here, and not just OLEDB source and OLEDB destination?
Good question.
Thanks for your reply.I am trying to use Balance data distributor, i.e., from OLEDB source to BDD to split million of records into 4 parts for parallel processing.
so after BDD i need to send the input to CLR function which inturn will give me more records for 1 row.
So i am searching a SQL processing task in Data flow but only OLEDB can handle SQL so i am trying to do with that.
Can you please tell me if i am doing right ? or is there any way ?
I am trying with script component but i feel like it will be performance killer.
Please help me out
The architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 27, 2018 at 10:47 am
Phil Parkin - Tuesday, November 27, 2018 6:36 AMJoNTSQLSrv - Tuesday, November 27, 2018 6:19 AMPhil Parkin - Tuesday, November 27, 2018 5:23 AMJoNTSQLSrv - Tuesday, November 27, 2018 12:15 AMHI All,I am struggling to use table value CLR function in OLE DB command in data flow task.
I am trying to give the input from OLEDB source , each row input to a CLR table valued Function which in tern will give more rows like table ,which i wanted to insert in a table via OLE DB destination.
I am unable to do it in OLE DB command.
I tried like below in OLE DB Command.
select col1, col2,col3
from dbo.clrfunction(?,?,?,?)but col1, col2 , col3 are not coming in available output.
Have any one faced these type of scenario ? Please help me> thanks in advance
Why are you using OLEDB Command here, and not just OLEDB source and OLEDB destination?
Good question.
Thanks for your reply.I am trying to use Balance data distributor, i.e., from OLEDB source to BDD to split million of records into 4 parts for parallel processing.
so after BDD i need to send the input to CLR function which inturn will give me more records for 1 row.
So i am searching a SQL processing task in Data flow but only OLEDB can handle SQL so i am trying to do with that.
Can you please tell me if i am doing right ? or is there any way ?
I am trying with script component but i feel like it will be performance killer.
Please help me out
The architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?
Thats amazing. Thanks for your set-based idea. Even i was thinking the same. I know RBAR will be performance killer , any how i just done for testing.
Can you please suggest me or give idea on how to do set based using CLR function(because Table values function itself is RBAR) in data flow task ?
I know we can try from control flow by using execute sql task. Is this what you meant?
November 27, 2018 at 11:06 am
JoNTSQLSrv - Tuesday, November 27, 2018 10:47 AMPhil Parkin - Tuesday, November 27, 2018 6:36 AMThe architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?Thats amazing. Thanks for your set-based idea. Even i was thinking the same. I know RBAR will be performance killer , any how i just done for testing.
Can you please suggest me or give idea on how to do set based using CLR function(because Table values function itself is RBAR) in data flow task ?
I know we can try from control flow by using execute sql task. Is this what you meant?
Can you provide an overview of what the CLR function does? I think it would be useful background.
Can the CLR be changed to provide output for a range of input rows, instead of only one? (Eg, WHERE STATUS = 'A', or , WHERE DATE >='20180101' AND DATE < '20190101')
If the CLR could be changed (or a new CLR created) in this manner, you should be able to use it in your OLEDB source.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 27, 2018 at 11:13 pm
Phil Parkin - Tuesday, November 27, 2018 11:06 AMJoNTSQLSrv - Tuesday, November 27, 2018 10:47 AMPhil Parkin - Tuesday, November 27, 2018 6:36 AMThe architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?Thats amazing. Thanks for your set-based idea. Even i was thinking the same. I know RBAR will be performance killer , any how i just done for testing.
Can you please suggest me or give idea on how to do set based using CLR function(because Table values function itself is RBAR) in data flow task ?
I know we can try from control flow by using execute sql task. Is this what you meant?Can you provide an overview of what the CLR function does? I think it would be useful background.
Can the CLR be changed to provide output for a range of input rows, instead of only one? (Eg, WHERE STATUS = 'A', or , WHERE DATE >='20180101' AND DATE < '20190101')
If the CLR could be changed (or a new CLR created) in this manner, you should be able to use it in your OLEDB source.
Thanks Phil Parkin
CLR will take 1 row as input and calculate some business logic(using c#) and give us 6 rows or sometimes more depending upon category.
In Sp we usually call CLR like below,
Insert into output_table(col1,col2,col3)
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20190101'
so I am trying to do the above in SSIS so when i try this inside data flow task i ended up BDD and ole db command so i was struggling.
So my understanding on your idea is , splitting the million of input rows into 3 batch(3 oledb source) and inside each source changing where clause and applying CLR directly in OLEDB source itself right ?
like below
1'st oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20180501'
2nd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180601' AND DATE < '20180901'
3rd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20181001' AND DATE < '20190101'
so finally , into oledb destination using union all task right ?
Please correct me if i am wrong
November 28, 2018 at 5:26 am
JoNTSQLSrv - Tuesday, November 27, 2018 11:13 PMPhil Parkin - Tuesday, November 27, 2018 11:06 AMJoNTSQLSrv - Tuesday, November 27, 2018 10:47 AMPhil Parkin - Tuesday, November 27, 2018 6:36 AMThe architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?Thats amazing. Thanks for your set-based idea. Even i was thinking the same. I know RBAR will be performance killer , any how i just done for testing.
Can you please suggest me or give idea on how to do set based using CLR function(because Table values function itself is RBAR) in data flow task ?
I know we can try from control flow by using execute sql task. Is this what you meant?Can you provide an overview of what the CLR function does? I think it would be useful background.
Can the CLR be changed to provide output for a range of input rows, instead of only one? (Eg, WHERE STATUS = 'A', or , WHERE DATE >='20180101' AND DATE < '20190101')
If the CLR could be changed (or a new CLR created) in this manner, you should be able to use it in your OLEDB source.
Thanks Phil Parkin
CLR will take 1 row as input and calculate some business logic(using c#) and give us 6 rows or sometimes more depending upon category.
In Sp we usually call CLR like below,
Insert into output_table(col1,col2,col3)
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20190101'so I am trying to do the above in SSIS so when i try this inside data flow task i ended up BDD and ole db command so i was struggling.
So my understanding on your idea is , splitting the million of input rows into 3 batch(3 oledb source) and inside each source changing where clause and applying CLR directly in OLEDB source itself right ?
like below
1'st oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20180501'2nd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180601' AND DATE < '20180901'3rd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20181001' AND DATE < '20190101'so finally , into oledb destination using union all task right ?
Please correct me if i am wrong
This sounds good to me
Ensure that an appropriate index is in place on your source table to keep the initial batched SELECT running well.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2018 at 6:25 am
Phil Parkin - Wednesday, November 28, 2018 5:26 AMJoNTSQLSrv - Tuesday, November 27, 2018 11:13 PMPhil Parkin - Tuesday, November 27, 2018 11:06 AMJoNTSQLSrv - Tuesday, November 27, 2018 10:47 AMPhil Parkin - Tuesday, November 27, 2018 6:36 AMThe architecture of your solution is inherently slow, unfortunately – if I am understanding things right.
You have millions of source rows, which you'd like to split and process in parallel ... good start.
But, for each of those rows, you wish to call a CLR which itself generates multiple rows of data per input row. This is your pain point. Can this part be re-architected to be set-based, rather than RBAR?Thats amazing. Thanks for your set-based idea. Even i was thinking the same. I know RBAR will be performance killer , any how i just done for testing.
Can you please suggest me or give idea on how to do set based using CLR function(because Table values function itself is RBAR) in data flow task ?
I know we can try from control flow by using execute sql task. Is this what you meant?Can you provide an overview of what the CLR function does? I think it would be useful background.
Can the CLR be changed to provide output for a range of input rows, instead of only one? (Eg, WHERE STATUS = 'A', or , WHERE DATE >='20180101' AND DATE < '20190101')
If the CLR could be changed (or a new CLR created) in this manner, you should be able to use it in your OLEDB source.
Thanks Phil Parkin
CLR will take 1 row as input and calculate some business logic(using c#) and give us 6 rows or sometimes more depending upon category.
In Sp we usually call CLR like below,
Insert into output_table(col1,col2,col3)
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20190101'so I am trying to do the above in SSIS so when i try this inside data flow task i ended up BDD and ole db command so i was struggling.
So my understanding on your idea is , splitting the million of input rows into 3 batch(3 oledb source) and inside each source changing where clause and applying CLR directly in OLEDB source itself right ?
like below
1'st oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180101' AND DATE < '20180501'2nd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20180601' AND DATE < '20180901'3rd oledb source
Select b.col1,b.col2,b.col3
from Input_table a
cross apply dbo.fn_clr(a.col1,a.col2,a.col3) b
where a.col1 >='20181001' AND DATE < '20190101'so finally , into oledb destination using union all task right ?
Please correct me if i am wrong
This sounds good to me
Ensure that an appropriate index is in place on your source table to keep the initial batched SELECT running well.
Thanks a lot Phil Parkin 🙂 yes i will check index as well
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply