SSIS Variables

Question

SSIS Variables

marty.seed

SSCrazy

Points: 2661
More actions
January 18, 2010 at 9:18 am

#218295

I have a package that I built and I need to transfer data from a db on one server over to another. The package will run once a day in the morning. I don't want to truncate the tables and load the entire tables again since there are millions of rows of data. I performed one big load and then going forward I just want the new data. I figure the best way to do this would be to find the Max date on the accepting table, load into a variable and pass that to the query that pulls from the initial table. How can I set a variable from one server and use it on another in this instance?
January 19, 2010 at 11:23 pm

This was removed by the editor as SPAM
January 22, 2010 at 12:17 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bgeige Say Hey Kid Points: 679 More actions · Answer 1

In the control flow: sql task, select max date from source server, Result Set = Single Row, set the result set to variable in the task, make sure the variable is a package level and not task level.

You now can use this variable in the data flow task

http://fsugeiger.blogspot.com

bgeige Say Hey Kid Points: 679 More actions · Answer 2

In the control flow: sql task, select max date from source server, Result Set = Single Row, set the result set to variable in the task, make sure the variable is a package level and not task level.

You now can use this variable in the data flow task

http://fsugeiger.blogspot.com

marty.seed SSCrazy Points: 2661 More actions · Answer 3

Hey bgeige

This isn't working for me, I must be missing a step some where...

1. Add a new SQL Task to the Control Flow - OK

2. Change the Result Set in the SQL Task to SingleRow - OK

3. Added the SQL statement to the SQL Task:

Select Max(DateCreated) from Orders - OK

I tried to execute the SQL task to test it and it failed with error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I'm not sure what this means?

Also where do I set and assign the variable?

marty.seed SSCrazy Points: 2661 More actions · Answer 4

Hi Stewart,

Thanks for this, I think I got this part down. Now how do I reference this variable in the Data Flow Task?

I set the variable in the SQL Task, the variable is MaxID. Now in the Data flow task I want to query:

Select *

from CustomerList

Where CustomerID>=@MaxID

RR-881870 SSC Enthusiast Points: 137 More actions · Answer 5

Alternately I would suggest the following:

1. Declare a string type package level variable say "sSQL"

2. Set the package level property (right click the white space in the package) "DelayValidation" to "True" (default is false, which means validate always). This is very important for ther approach i am suggesting.

3. Create a new Execute SQL Task (say using OleDB type connection). Now make sure the result "Single Row" is selected.

4. Lets say you need to find out the Max Id from "CustomerList" table. Type the following SQL command in the "sqlStatementSource" property:

SELECT 'Select * From [dbname].[schemaowner].CustomerList with (Nolock) Where CustomerId > ' + Cast(Max(CustomerId) as varchar(40)) From [dbname].[schemaowner].CustomerList with (nolock);

5. In the resultset for the ExecSQLTask, save the output to "sSQL" variable.

6. Create a DataFlow task, and perform all the column mapping using a static query (lets say Select * From CustomerList).

7. Once you finish mapping columns between OleDB Source/Destination tasks in "Data Flow" tab. Right click the OleDB based Source Task and choose properties. Under Custom properties, set "Access Mode" property to "SQL command from Variable" and also set "sqlCommandvariable" to "User::sSQL".

8. Save your package and test your process.

Hope this helps.