Solved

SSIS, can't use variable in source because it's not yet formed

Posted on 2013-11-06
6
251 Views
Last Modified: 2016-02-11
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 . . .
0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39627543
Curiosity overwhelms me ... wouldn't it be easier to have your dynamic SQL in a Stored Procedure, with the SSIS calling the SP and passing whatever parameters is needed, instead of doing it in SSIS with variables?
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39627593
Jim,

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.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39627662
> how does SSIS know what the columns are using this SP approach ?
SSIS can use an Execute SQL Task to call a Stored Procedure, and that requires that you pass whatever parameters the SP requires as variables.  This process is somewhat high complexity.  

I don't see any tutorials with a lot of screen shots on EE, so try this one.

>is my approach totally a wreck ?
It would be more accurate to say that it's more of a wreck then having to use dynamic SQL and calling an SP via SSIS in the first place.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39627769
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.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39629124
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.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39634822
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.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question