SSIS, can't use variable in source because it's not yet formed
Posted on 2013-11-06
I have a query that I need to form in an expression in a variable, but it's > 4000 chars, so the expression can't handle it.
So I thought I was being crafty, and I created three variables:
Sql_1 - has the 1st half of the query
Sql_1 - 2nd half of the query, using an Expression and some other variables
Sql_full - simply adds Sql_1 and Sql_2 together in a script task.
I was really proud of myself to create Sql_Full in my script task, and I used a "MsgBox" to display the result, and I went to bed with a smile.
This morning, however, I went to complete my work and ran into what seems like a brick wall . . .
So in my source (Oracle), I choose "Sql command from variable", then I choose "Sql_full", but then when I click on "Columns", SSIS croaks, because Sql_full is not yet formed . . . so what am I supposed to do ?
I researched this and there's plenty of people with this issue, I saw that a script task was an option, and I did populate Sql_Full, but it's only formed at runtime and so I can't figure this out . . .