I'm storing a sql command (could be an SP call or simple select) that I would like to pass into the "OLE DB Source" container in my data flow task. If I choose Data Access Mode: SQL Command from variable, I get an error saying there is a data source column with no name.
I've fought SSIS pretty good up to this point. I realize you need to "initialize" variables & expressions in the control flow ahead of time if you want to access them in the data flow. But I was kind of hoping that all I would need to do in this case would be pass a SQL command into a variable and access that somehow in the data flow without jumping through any hoops. Is this possible
This is a great forum btw, thanks,
Phil
variables in data flow OLE DB
JoraPJL
Well, I'm somewhat retarted. I looked and saw that my variable wasn't being assigned properly in the Execute SQL Task earlier, so I fixed that. But now there seems to be another issue which is the "mappings" into my excel destination are all messed up because, well, my SQL will be dynamic :).
I'm trying to make this work so that I can have any SQL command, variable file locations, create an excel file (with variable worksheets) be all database driven.
I don't have any specific question anymore unless somebody has some idea's pointers on how this can be done.
Thanks,
Phil
Alvin Kuiper
Its not feasible within SSIS, no. You will have to build a data flow for each possible combination of columns.
There is one alternative which no-one ever really tries - build a package dynamically at runtime. Its described here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx
-Jamie
Krutika
You could garantee that metadata wont change in your SQL:
SELECT col1 = convert(int, db_col1), col2 = convert(varchar(20), db_col2)
FROM db_table
Tammt
I'm sorry. I don't understand what this has to do with your problem.
-Jamie
a_guj
Absolutely right. That's what I mean by the metadata cannot change. But (for example) the server on which that destination resides could be changed.
-Jamie
raq
So, "select count(*) from table" becomes "select count(*) as recordCount from table"
This would go for any aggregate function.
DotNet_Student
I was thinking I could attempt this using an SP with paramaters. But I guess if the columns change, which they will, I may be beating my head against a brick wall. I guess I'll just have to tell my boss it can't be done this way...
Thanks again,
Phil
rwerner
Right, but an OLE DB destination (for instance) has column mappings in it... Can't really make that dynamic.
nkahootz
ckob22
Here is my bosses response:
If the problem is just binding the output columns to a spreadsheet, you can easily overcome this by just using an XSL translation and dumping out a Excel file created with XML.
Is this possible
Thanks,
Phil
Mark Shvarts
Not quite sure about that Phil. You can make the location of the destinations dynamic - that;s the whole point of configurations (as I know you know).
You can make the SQL statements be generated dynamically with the caveat that the metadata of the result set does not change.
-Jamie
dalterio
"select output_location, network........"
zion99
The issue which I'm trying to solve is how to map, dyamically, database source columns, to destination columns. In other words, have a single SSIS package be able to handle any data flow with different SQL statements as the source and output different excel or other documents.
From what I've seen/read this isn't really what SSIS was meant to do or it's rather difficult unless your output columns will always be the same. Mine will not be. My boss had the suggestion of using XML/XSL to transform the columns on the fly. I just want to know if this is feasible :).
Thanks,
Phil
jhknys
my statement, which I'm getting from a database column and storing in a variable, is simply:
select output_location,network_location
from script_master
What I want to do is execute this command in the data flow to populate a spreadsheet.
I'm going into the "OLE DB Source" container and double-clicking it w/in the data flow task. Then I'm choosing SQL Command from variable and assigning my variable which contains the SQL I want to execute. That's when I get the error.
Phil