Alaska Cowboy
asked on
SSIS, can't use variable in source because it's not yet formed
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 . . .
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 . . .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim, ok, thanks.
so in the Execute Sql Task, it calls the SP, but are the columns available in my source, so that I can see them and then they can be mapped to Dest ?
thanks for the tutorial.
my solution is not so much a wreck as it is "DOA" - it doesn't work, because the variable isn't formed until runtime so columns are not available for mapping. But it seemed so quick and easy, just not usable.
I spoke to a couple of others today, one said use the SP approach, the other said, it's a lot of hoop-jumping for something simple, so I'm going with the "Select * " approach. there's no real downside, there's like 250 columns in the table and I need 250 of them.
so in the Execute Sql Task, it calls the SP, but are the columns available in my source, so that I can see them and then they can be mapped to Dest ?
thanks for the tutorial.
my solution is not so much a wreck as it is "DOA" - it doesn't work, because the variable isn't formed until runtime so columns are not available for mapping. But it seemed so quick and easy, just not usable.
I spoke to a couple of others today, one said use the SP approach, the other said, it's a lot of hoop-jumping for something simple, so I'm going with the "Select * " approach. there's no real downside, there's like 250 columns in the table and I need 250 of them.
ASKER
I got it working by using "select * ". While this might not be the preferred method, it's the right (simple) solution. But good to know about the SP approach, thanks.
ASKER
Jim,
hope you don't mind a follow-up . . . looks like I might need to go the SP route . . .
so my question is, if I go this route, then when I get to setting up the source, what do I choose ? "Sql query from variable" ? and then what pop's up in the "Sql Command Text" ? Because something needs to be there so that columns are available for the mapping.
hope you don't mind a follow-up . . . looks like I might need to go the SP route . . .
so my question is, if I go this route, then when I get to setting up the source, what do I choose ? "Sql query from variable" ? and then what pop's up in the "Sql Command Text" ? Because something needs to be there so that columns are available for the mapping.
ASKER
I have no idea if that is easier, that's why I'm here . . . :-). In fact, I didn't even know it was an option !
I'm learning one thing at a time here, like yesterday was a screwdriver, today is a wrench, etc. . . .
since I posted this, I bailed out on trying to jump through hoops like I was doing, and I said "Select * from [table a, table b, etc.], and now in my Source, I selected the variable, and it showed all the columns. So I was just going to deal with that, it actually doesn't look too bad, other then the poor practice of using "select * ".
For your solution, I'd first have to learn how to write a SP (medium effort), then I think I'd still be in the same position as I am now - how does SSIS know what the columns are using this SP approach ?
And I'm curious too (and not being sarcastic), is my approach totally a wreck ? It's what I thought I saw in googling this, where there are many people who squawk when the expression is > 4000.
Thanks.