Passing Value to User Defined Variable??

[sconti11]

I have not tried this before, and from what I have read it should be possible, but I am missing the beginning piece of this flow.

I need assistance with correct approach for passing a value into a user defined variable within a ssis package. If I have a Stored Procedure that contains the variable value, how can I get that value into the variable at run-time.

The basic scenario here is that I need to filter all the source data flows by this variable so that limited rows of data are actually loaded into the destination tables.

I look forward to your response!

 

[ousoonerjoe]

Take a look at RiverGuy's reply to a post I had a while back. Particularly note the WHERE clause change that was made: thread1462-1580241

If I'm understanding your need correctly, a minor restructure of your stored procedure may be a better solution that allows for arguments as needed and will filter the data BEFORE it is sent to SSRS.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[duanecwilson]

I thought I would try to help, even though I don't have the fancy screen shots and code blocks, etc.

Drag and Drop an OLE DB Source onto a Dataflow.
On the Connection Manager tab, create or use a connection to the database.
The data access mode is SQL command.
The SQL command text should return one row and one column. I used a function tha

returned one row and column, an integer DayKey like 20110101.
The statement I ues was 'Select dbo.fnDayKey() as fnDayKey.
On the columns tab, select the column returned. Here, it would be fnDayKey.
Click Parse query to make sure it is correct, and then click Preview to make sure

it returns one value.
Then click OK to close the dialog box.

Click on the dataflow surface and create a user variable with the correct datatype.
Drag a Script task onto the dataflow and connect the OLE DB Source to it.
On the script tab, in ReadWriteVariables, select your variable name.
I used 'User::intDayKeyFromSQL'.
On the Input Columns tab, select your column and make it ReadOnly Usage Type.
Click on Edit Script. Under Public Class, create a variable name.
I used 'Private vIntDayKey As Integer'
In the Input0_ProcessInputRow ..., assign your variable, ie:
vIntDayKey = Row.fnDayKey
Under PostExecute, assign your package variable, ie:
Me.Variables.intDayKeyFromSQL = vIntDayKey

Close the VB window and click OK on the script dialog box.

Drag an OLE DB Command onto the Dataflow and connect the script to it.
On the Connection Managers tab, select your connection.
On the Component Properties tab, add your SQL Command. You may need to use the

elipsis at the end of the line.
My SQL command was:
EXEC uspLOAD_STAGE_FIDELITY_DepositData_ ?
That should be your stored procedure with the parameter.
The question mark is the parameter location.
On the Column Mappings tab, map your Input and Destination columns.
I hooked up intDayKey with @pintDayKey.

Let me know if you have more questions. And anyone else, chime in whether I have any errors or can make improvements.

Duane Wilson