SQL Server - parameter for data flow?

Asked By Tom Williams

17-Nov-09 11:22 AM

Typical disclaimer: I am pretty new at this SSIS stuff.

I need to transfer 35+ million rows from Oracle to SQL Server.  At the
transfer rate I have seen so far, this will take about 29 hrs.  Way too long.

I do not really need all 35 million rows right now.  I'd like to transfer
rows based on a year value in the tables.   Since there are 120+ tables, I'd
like to write select statements with a parameter for the year.  Sounds
pretty basic to me, but I do not see how to read a variable into a select
statement in a data flow.  Can this be done?  Is there a better way?

Thanks
Tom

17-Nov-09 12:32 PM

There are several ways, but they can be boiled down to two categories:
1. Dynamic SQL
2. mapping variables to parameters

If you choose dynamic sql, then my preference is to create a variable
based on an expression. Then set the source property in your data flow
task to the variable.
http://sqlblog.com/blogs/andy_leonard/archive/2009/01/31/ssis-expression-language-and-variables.aspx

If you choose parameters, then my preference is to use an ADO.Net
connection manager so I can use named paramters rather than indexed
parameter markers. You should be able to find an example for this in SQL
BOL with minimal effort.

As to getting the parameter value into your package, you can use a
Package Configuration. Todd has a good blog about this:
http://toddchitt.wordpress.com/2008/06/27/ssis_config



--
HTH,
Bob Barrows

18-Nov-09 08:54 AM

Actually, I would not use my Config database for this. I reserve that
primarily for Connection Strings and other values that change when migrating
a package from DEV to TEST to PROD (though it would certainly work).

I would set up a 'control' table;
CREATE TABLE dbo.YearList (
LoadYear INT,
LastLoaded DateTime )

Populate it with all the years you want loaded, leaving the DateTime field
as NULL.

Then first Task on your Control Flow, have an Execut SQL Task with:
SELECT MIN(LoadYear)
FROM dbo.YearList
WHERE LastLoadeded IS NULL

That should return a single INT value, which you would put into a Package
Variable using the Result Set. Now use that Package Variable as a parameter
when you do all your SELECT statements against the Oracle Db.

Lastly, you need to update the control table to tell it that the particular
year was loaded. Do this with another Execute SQL task:

UPDATE dbo.YearList SET LastUpdated = GETDATE() WHERE LoadYear = ?

and map the Package Variable to the parameter.

Now you have a package that you can run once a night and it will always get
a different year each time, without you needing to do any manual table
manipulation.

HTH. Good luck.
=====
Todd C

18-Nov-09 04:00 PM

So would I. Actually, I got the idea for doing so from reading your
blog, so i guess I was hoping the OP would make a similar leap rather
than spelling it out for him :-)

... ok, I got lazy!

--
HTH,
Bob Barrows