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

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:

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:

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:

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:

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:

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:

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:

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:

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: