Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

SSIS 2008: Order of execution between set variable / get variable

I have an SSIS package where I am setting two variables in tasks the beginning (first image). Good to go.
variable-set-execute-1.jpgFurther down I have a data flow task with an expression (second image) to set a Lookup.SQLCommand property to concatenate a string value that uses those two variables.
 
Good to go BUT the problem I'm having is that the data flow task.lookup is grabbing the variables values that are in the SSIS package BEFORE the tasks are executed to set those variables.
variable-set-2.jpgvariables-before-execute.jpglookup-after.jpgBig hairy question: How can I specifically state to get variables at runtime when a component is executed, and not before the package runs?

Possible workaround question: What's the VB syntax in a script task to set a lookup.SQLCommand to a value?

The reason for doing this is I am pumping 40+ tables from SalesForce.com to SQL, and I'd like to avoid inserting duplicates. The package extracts from these tables only between these two dates. The lookup on the target will lock the entire table, so to avoid that I'd like to add a WHERE clause on these two dates.
0
Jim Horn
Asked:
Jim Horn
  • 6
  • 6
  • 2
2 Solutions
 
DcpKingCommented:
If the two variables come in as parameters then they should be the runtime values ... so one thing you could do is call most of your code from another package, which has just the first two or three tasks, and which passes in the values - or even the whole string - as a parameter.

Alternatively, have you tried composing the select statement in a simple script task so that when control hits the task in picture two it executes a variable and not a string?

hth

Mike
0
 
Phillip BurtonCommented:
By using the "Execute SQL Task". Here's an article on how to map the Result Set to a variable.
0
 
DcpKingCommented:
Another possibility occurs to me ... put the whole determination of the string to execute into a variable definition (call it strSQL)  and have the task just execute strSQL. I believe that strSQL will get evaluated when its components change value.

Mike
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
@DcpKing - Can you provide VB Script that sets a Lookup task's SQLCommand property to a variable?  
I'm not able to find it.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
In response to your first comment..

>If the two variables come in as parameters then they should be the runtime values
No, two Execute SQL tasks are calling Stored Procedures, and the return values are then stored in these variables.

>have you tried composing the select statement in a simple script task so that when control hits the task in picture two it executes a variable and not a string?
No, but I can certainly do that now.  It would also help if I (my above comment) could create VB Script that sets a Lookup task's SQLCommand property to a variable.  Looking at the Lookup task (no pun intended) there is not a selection for 'Variable Execution'.
0
 
Phillip BurtonCommented:
By using the "Execute SQL Task". Here's an article on how to map the Result Set to a variable. https://msdn.microsoft.com/en-us/library/ms141689.aspx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Uhh Phil, the two tasks in the top image are Execute SQL tasks, which do map the result set to a variable.

The problem apparently is that my expression that feeds the Lookup is grabbing those values when the package executes, and not after the first two tasks populate them.
0
 
DcpKingCommented:
"Looking at the Lookup task (no pun intended) there is not a selection for 'Variable Execution'."  ... not inside the "build query" part ? (sorry - it's been a while since I used 2008 - I'll spin up a VM and look tonight if you haven't an answer by then

Mike
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
The Build Query... button is just a basic query editor, and I don't see any funcationality to get variables.
Same with 2012, I just upgraded the package (we had to do it anyways..)
0
 
DcpKingCommented:
OK. I just spent (wasted ? :) ) 90 minutes fighting it ..... and got bored!

You bring it a data stream for it to compare with a lookup of data from SF_Assessment  between two values of SystemModStamp.

1. Create a table SSISTempTable with two columns - an integer (called Row) and a datetime (called TheTime). At the start of your SSIS fill these fields instead of the variables.

2. Create a view called v_SF_Assessment
select id, SystemModStamp from SF_Assessment
where SystemModStamp > (select TheTime from SSISTempTable where Row = 1)
and SystemModStamp < (select TheTime from SSISTempTable where Row = 2)

3. Before getting to the lookup task fill the two rows of SSISTempTable with the start and end times you want to have as your range

4. In your LookUp task select "Use a table or a view" and select v_SF_Assessment

5. At runtime v_SF_Assessment will look to SSISTempTable for its bounds, and find the values you've put in there (instead of filling variables)


Hopefully this should work for you

hth

Mike
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
... looking ...
0
 
DcpKingCommented:
Feel free to change table names :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I still wasn't able to figure this out using variables and the Lookup.SQLCommand property, but thanks to DcpKing's comment above I have a truly excellent work-around.

1.

Create a Stored Procedure that accepts the parameters, and uses dynamic SQL to drop/create a view with the columns id and SystemModstamp.

2.

Add an Execute SQL Task to every data flow, calling the above parameter and passing the table name and dates (or in my case log_id because it's easier to always pass one parameter instead of two)

3.

Change the Lookup's Connection to use that view.

1.

Create a Stored Procedure
CREATE PROC SF_SSIS_DFT_LOOKUP(@log_id int, @table_name varchar(100)) AS

/*
Create a view with a table name and date range on-the-fly, for use in SSIS lookups
   that can't handle variable assignment. 

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28601191.html
   
01-23-14  jhorn104  Original

-- TESTING ONLY
Declare @table_name varchar(100) = 'SF_Order', @log_id int = 402
*/

Declare @sql nvarchar(1000), @load_data_as_of_dt datetime, @start_dt datetime 

-- Grab the date range for this package run
SELECT @load_data_as_of_dt = LOAD_DATA_AS_OF_DT, @start_dt = START_DT
FROM SSIS_LOG_PACKAGE
WHERE LOG_ID = @log_id

-- Drop the existing view
SELECT @sql = 'IF EXISTS(SELECT name FROM sys.views WHERE name=''vw_SF_SSIS_DFT_LOOKUP'') '
SELECT @sql = @sql + 'DROP VIEW vw_SF_SSIS_DFT_LOOKUP'

exec sp_executesql @sql

-- Create the view for this table and date range
SELECT @sql = 'CREATE VIEW vw_SF_SSIS_DFT_LOOKUP as '
SELECT @sql = @sql + 'SELECT id, SystemModstamp '
SELECT @sql = @sql + 'FROM ' + @table_name + ' (NOLOCK)'
SELECT @sql = @sql + 'WHERE SystemModstamp > ''' + CONVERT(varchar, @load_data_as_of_dt, 120) + '''' 
SELECT @sql = @sql + '   AND SystemModstamp <= ''' + CONVERT(varchar, @start_dt, 120) + ''''

exec sp_executesql @sql

GO

Open in new window

2.

Add an Execute SQL Task to every dataContainerSQL Statement, with @log_id as parameter

3.

Change the Lookup's Connection to use that view.Runs like a champ.  The lookup calls the view, which only returns rows from the date range, and is NOT dependant on SSIS variables and timing.
Data Flow Task
Since I have 40+ of these containers, I can propogate these changes to the remaining containers with minimal customization.

Thanks again.  Much more effort than I was expecting for a single EE question, and it works great.
Jim
0
 
DcpKingCommented:
We live to serve!

Mike
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now