August 22, 2012 at 6:36 pm
Hello SSIS people... I have an issue I'd like to pose to the group, as I cannot think of a simple solution to this issue. Essentially I have been tasked with an export project, for which I am connecting to a source database, and exporting csv files based off of a set of views I have created. The issue is this, one circumstance requires that I use one statement for the SQL Command, while another scenario requires I use a different SQL command to populate my OLE DB Source. The "circumstance" is easy to detect pragmatically. Currently I have a SSIS Var which is a bool configured, and I set that var accordingly. If the var is true, my source needs to be defined by "select * from scrm.customer where team = ?," and if the var is true, my source needs to be defined by "select * from scrm.customer." The concern is that I have to implement this logic on about 30 different OLE DB Sources. I've worked out a way I think I can do it with an script component configured as a source. Anything I am missing?
August 22, 2012 at 10:09 pm
By '30 different OLEDB Sources', i am assuming based on your question, you mean same server / same database but 30 different tables/views.
Yes, Script component could be one way to go. You are suggesting this because you can make the view name dynamic in that and also put the condition in dynamically.
Well, i think this might be possible w/o the script compnent also.(nothing wrong with a script component)
Basically, have a foreach loop container driven off a recordset containing the names of your 30 different datasources....i am guessing these will be your 30 different views.(again the very important assumption here that the server is the same)
Map the name of the view in each iteration to a variable, say vw_nm.
Inside the foreach loop have a dataflow setup.
Inside the dataflow have an OLEDB Source connector outputting to a file destination.
Set the DataAccess Mode of the OLEDB Source as ' SQL Command from a Variable'
Set the above variable up in your package....say var_query.
Based on your description, you already have a condition var set up as a bool var....lets call it var_b
Also, you will need another var for the parameter in your query....say var_param
Set the expression on the variable var_query as something like :
(var_b == true) ? "select * from " + vw_nm + " where team = " + var_param : "select * from " + vw_nm
(This basically means if var_b is true apply the param else not)
This way on each of the 30 iterations, the vw_nm will be set dynamically and the OLEDB source would pick that up and also put the condition dynamically.
Please let me know if this works or not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply