0

I have joined 5 tables and done transformation on these tables. Now I got a single table at the end. Now I want to perform sql query on this single table to filter records. But I don't know how to perform simple sql query on this table. I have attached a snap shot which shows the resulting table. How I get this resulting data set as the source? I want to populate my destination after filter out this data. I am using SSIS 2008.

Click here to see the Table on which I want to perform a simple sql query

Cœur
  • 37,241
  • 25
  • 195
  • 267
user1772390
  • 53
  • 2
  • 10

3 Answers3

0
SELECT * FROM `first_table`
where `some_column` = 
(
SELECT `*`
FROM second_table
WHERE
`some_column2`='something'
LIMIT 1
)

Try this code This will help. You can even use this to connect all those four tables with each other.

0

From the image you posted, it looks like you have a set of data in the dataflow you're trying to query against. You need to do one of two things at this point. Either you insert the data into a table in the database and use another data flow to query it, or you use use a conditional split (or multicast and conditional splits) to filter the rows down further from there.

Without more detail about what you're actually trying to accomplish, these are the recommendations I can determine.

You could send the rows into a record set destination, but you aren't able to query it like a regular table and you'd need some C#/VB skills to access it to do more than a FOR EACH loop.

Vinnie
  • 3,889
  • 1
  • 26
  • 29
0

Assuming your sql query that you want to run against the resulting table is simple, you can use a script component task. By simple, I mean, if it is of this nature: SELECT * FROM T WHERE a = 'zz' and b = 'XX' etc.

However, if your query has self joins, then you would be better of dumping the outcome of joining those 5 tables in to a physical table, and go from there.

It appears that query is going to be real straight-forward; in that case using a script component would be helpful.

A separate question: It's advisable to do the sorting at the database level. You are using 5 sort tasks in your solution. Can you please elucidate the reason?

Anoop Verma
  • 1,495
  • 14
  • 19