Subqueries on SSIS Packages

Question

Post reply

Subqueries on SSIS Packages

C-los

Old Hand

Points: 370
More actions
January 26, 2006 at 7:17 am

#364782

Hi,
I'm having the with SSIS related to subqueries.
I need to add a parameter into my subquery and in some cases this is extremely important and there is no way to create the query without the subquery.
Does anyone has any idea?
Below, follow a query example about what I'm talking:
select *
from tableA a
where exists (Select ID
from tableB b
where b.ID = A.ID
and b.Date = ? )

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Carlos,

I'm not sure b'coz I'm at the initial stages on SQL Server 2005.

Go through this link, may be it gives you the required solution

http://msdn2.microsoft.com/en-us/library/ms140355.aspx

Thanks and have a nice day!!!

Lucky

C-los Old Hand Points: 370 More actions · Answer 2

OK, this works for a simple query. However, if you have a parameter on a subquery, it doesnt work...

That is my problem...

howeld SSC Enthusiast Points: 186 More actions · Answer 3

Sounds lilke you need to build your entire query in a Scripting Task and put the query in a variable. Then set the SQLSourceType property of your Execute SQL Task to "Variable" instead of "Direct Input" and put the variable name in the SourceVariable property.

howeld SSC Enthusiast Points: 186 More actions · Answer 4

Sounds lilke you need to build your entire query in a Scripting Task and put the query in a variable. Then set the SQLSourceType property of your Execute SQL Task to "Variable" instead of "Direct Input" and put the variable name in the SourceVariable property.

C-los Old Hand Points: 370 More actions · Answer 5

well, that is my problem... the variable accepts only 4k characters and the query component 8k. My query, however, goes over 4k, so I cannot use a variable.

Tks

howeld SSC Enthusiast Points: 186 More actions · Answer 6

Have you tried splitting your query across 2 or more variables and then concatenating them together using the Expression syntax on the SQLStatementSource property?

Henrique Vieira Pinto SSC Enthusiast Points: 102 More actions · Answer 7

Hi howeld,

I think this is not going to work either. An Expression can return at maximum 4000 characters in a single string (DT_WSTR), otherwise it raises a truncation error, saying that it exceeds the max length (4000). Any other ideas?

Best Regards,

Henrique

howeld SSC Enthusiast Points: 186 More actions · Answer 8

Are you doing this in the Control Flow design screen or are you down in a data flow? Package variables don't have a 4k limit. That only applies to fields in a data flow.

What is preventing you from designing the query in a Script Task (control flow screen) and then using that variable in your OLEDB data source (or whatever source your are using) for your data flow?

I need more information as to exactly where you are doing this to fully answer your question.