Solved

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

Posted on 2015-01-21
14
335 Views
Last Modified: 2016-02-15
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
Comment
Question by:Jim Horn
  • 6
  • 6
  • 2
14 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 40564069
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40564085
By using the "Execute SQL Task". Here's an article on how to map the Result Set to a variable.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40564212
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40564218
@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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40564232
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40564333
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40564344
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 16

Expert Comment

by:DcpKing
ID: 40564593
"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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40564818
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
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 40565749
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40566357
... looking ...
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40566439
Feel free to change table names :)
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 0 total points
ID: 40566530
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 40566536
We live to serve!

Mike
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now