本文转自：
a very common scenario in an etl process is
one in which you need to call out to some configuration tables to figure out
some values that it needs to use somewhere downstream in the process. quite
often you also want to pass in some parameters as well. so in this article i
will be addressing both of those scenarios. to start, you will just need to drop
an execute sql task item onto the control flow of an ssis package and set up the
initial configuration so that it is basically given a connection string
now
we want to enter in our sql statement that will contain the parameters. in this
case we are using a sql server connection and i will be calling a stored
procedure. so i will provide ?s for each of the parameters involved. so my
statement look like this…
exec
staging.usp_getticketqueries ?,?,?
i
could also be more verbose if i wanted to
staging.usp_getticketqueries @startdate=?,@enddate=?,@someothervalue=?
but
it isn’t entirely necessary. now that i have the statement ready i just need to
click on the parameter mapping menu item on the left hand side and map my
parameters.
notice
that there isn’t anything really tricky here. i line out the variables that will
be used with the appropriate type and size. the only part that trips a lot of
people up is the fourth one which is the parameter name. when you initially add
an additional parameter to map this column will have a text name in it. instead
what i have done is to change these so that instead it has the 0 based index of
the parameters in my statement. once you have them mapped to the proper index
value then everything should be golden.
we want to grab that result set that is being sent from the stored proc and map
its values to some different variables. so we need to go back to the general
settings screen and in the “result set” section change the value of the
resultset property to something other than none in order to be able to make
changes to the resultset screen later. in this case, i am just returning a
single row. so i merely change the value to single row and i am ready to map the
result set.
switch to the result set screen via the menu on the left. here what you will
want to do is to map the actual column names of the result set to the variables
that you want them to be placed into. pretty simple. in the end, you will have
something that looks like this.
you can click ok as you are finished with the process. this is actually a fairly
handy thing to know in order to make your etl processes a little more
dynamic.
cheers!
aj