 | |
Convert RecordSet to comma separated values
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
RE: Convert RecordSet to comma separated values -
10-14-2008
, 02:21 PM
|
Thanks Todd for your suggestion! The issue with the Lookup Transformation would be that the query from the source will be pulling all rows from a large amount of data. I would like to filter that data at the source, rather than in pipeline or destination server. That's why, I thought that generating values through the SQL and then using it as a variable would be much better. -- Thanks, Pasha "Todd C" wrote: Hello Pasha: Seems to me there may be a better way then a System.Object type variable. If your User_ID list can be gleaned from a SQL table, then why not use it in a lookup in the data flow? Create a Lookup Transform on the pipe line and use your SELECT DISTINCT query to lookup valid User_ID's. You will have to play around with the Error Output settings to get the results you want. Another way is to use a Merge Join transform, which may or may not give better performance based on the number of records it is dealing with. HTH -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Pasha" wrote: Hi All, I would like to convert RecordSet to comma separated values in my SSIS 2005 package. I populate a recordset usinq a SQL task: (select distinct User_id from [User_ID]) to variable User_ID (System.Object) Then, I'd like to run a Data Flow Task with a query that would filter its result by the values stored in variable User_ID. So, it would look like 'select ..... where user_id in (User_IDs from the variable)' What would be the best way to achieve it? I know how to display each value separately using Script Task and ForEach Loop container, but not sure how to list them all separated by comma. -- Thanks, Pasha |
#22
| |||
| |||
|
|
Thanks Todd for your suggestion! The issue with the Lookup Transformation would be that the query from the source will be pulling all rows from a large amount of data. I would like to filter that data at the source, rather than in pipeline or destination server. That's why, I thought that generating values through the SQL and then using it as a variable would be much better. -- Thanks, Pasha "Todd C" wrote: Hello Pasha: Seems to me there may be a better way then a System.Object type variable. If your User_ID list can be gleaned from a SQL table, then why not use it in a lookup in the data flow? Create a Lookup Transform on the pipe line and use your SELECT DISTINCT query to lookup valid User_ID's. You will have to play around with the Error Output settings to get the results you want. Another way is to use a Merge Join transform, which may or may not give better performance based on the number of records it is dealing with. HTH -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Pasha" wrote: Hi All, I would like to convert RecordSet to comma separated values in my SSIS 2005 package. I populate a recordset usinq a SQL task: (select distinct User_id from [User_ID]) to variable User_ID (System.Object) Then, I'd like to run a Data Flow Task with a query that would filter its result by the values stored in variable User_ID. So, it would look like 'select ..... where user_id in (User_IDs from the variable)' What would be the best way to achieve it? I know how to display each value separately using Script Task and ForEach Loop container, but not sure how to list them all separated by comma. -- Thanks, Pasha |
#23
| |||
| |||
|
|
Thanks Todd for your suggestion! The issue with the Lookup Transformation would be that the query from the source will be pulling all rows from a large amount of data. I would like to filter that data at the source, rather than in pipeline or destination server. That's why, I thought that generating values through the SQL and then using it as a variable would be much better. -- Thanks, Pasha "Todd C" wrote: Hello Pasha: Seems to me there may be a better way then a System.Object type variable. If your User_ID list can be gleaned from a SQL table, then why not use it in a lookup in the data flow? Create a Lookup Transform on the pipe line and use your SELECT DISTINCT query to lookup valid User_ID's. You will have to play around with the Error Output settings to get the results you want. Another way is to use a Merge Join transform, which may or may not give better performance based on the number of records it is dealing with. HTH -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Pasha" wrote: Hi All, I would like to convert RecordSet to comma separated values in my SSIS 2005 package. I populate a recordset usinq a SQL task: (select distinct User_id from [User_ID]) to variable User_ID (System.Object) Then, I'd like to run a Data Flow Task with a query that would filter its result by the values stored in variable User_ID. So, it would look like 'select ..... where user_id in (User_IDs from the variable)' What would be the best way to achieve it? I know how to display each value separately using Script Task and ForEach Loop container, but not sure how to list them all separated by comma. -- Thanks, Pasha |
#24
| |||
| |||
|
|
Thanks Todd for your suggestion! The issue with the Lookup Transformation would be that the query from the source will be pulling all rows from a large amount of data. I would like to filter that data at the source, rather than in pipeline or destination server. That's why, I thought that generating values through the SQL and then using it as a variable would be much better. -- Thanks, Pasha "Todd C" wrote: Hello Pasha: Seems to me there may be a better way then a System.Object type variable. If your User_ID list can be gleaned from a SQL table, then why not use it in a lookup in the data flow? Create a Lookup Transform on the pipe line and use your SELECT DISTINCT query to lookup valid User_ID's. You will have to play around with the Error Output settings to get the results you want. Another way is to use a Merge Join transform, which may or may not give better performance based on the number of records it is dealing with. HTH -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Pasha" wrote: Hi All, I would like to convert RecordSet to comma separated values in my SSIS 2005 package. I populate a recordset usinq a SQL task: (select distinct User_id from [User_ID]) to variable User_ID (System.Object) Then, I'd like to run a Data Flow Task with a query that would filter its result by the values stored in variable User_ID. So, it would look like 'select ..... where user_id in (User_IDs from the variable)' What would be the best way to achieve it? I know how to display each value separately using Script Task and ForEach Loop container, but not sure how to list them all separated by comma. -- Thanks, Pasha |
#25
| |||
| |||
|
|
Thanks Todd for your suggestion! The issue with the Lookup Transformation would be that the query from the source will be pulling all rows from a large amount of data. I would like to filter that data at the source, rather than in pipeline or destination server. That's why, I thought that generating values through the SQL and then using it as a variable would be much better. -- Thanks, Pasha "Todd C" wrote: Hello Pasha: Seems to me there may be a better way then a System.Object type variable. If your User_ID list can be gleaned from a SQL table, then why not use it in a lookup in the data flow? Create a Lookup Transform on the pipe line and use your SELECT DISTINCT query to lookup valid User_ID's. You will have to play around with the Error Output settings to get the results you want. Another way is to use a Merge Join transform, which may or may not give better performance based on the number of records it is dealing with. HTH -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Pasha" wrote: Hi All, I would like to convert RecordSet to comma separated values in my SSIS 2005 package. I populate a recordset usinq a SQL task: (select distinct User_id from [User_ID]) to variable User_ID (System.Object) Then, I'd like to run a Data Flow Task with a query that would filter its result by the values stored in variable User_ID. So, it would look like 'select ..... where user_id in (User_IDs from the variable)' What would be the best way to achieve it? I know how to display each value separately using Script Task and ForEach Loop container, but not sure how to list them all separated by comma. -- Thanks, Pasha |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.