2007年8月27日星期一

How to effectively transfer and filter data source in SSIS via a variable

Last weekend, I got this question from our customer. I think that the most effective way is to use DataReader source in SSIS. Please refer to the following steps:
1. Drag a DataReader Source to your Data Flow panel, set the Connection
Manager and initial SQL Command. You can just input a string without any
filter now, such as "SELECT * FROM Orders".

2. Drag a OLE DB Destination to your Data Flow panel, set the DataReader
Source as its input, then set its Connection Manager and destination table,
and map the columns.

3. Right click the Data Flow panel, select Variables, create a variable
(assuming that the name is SQL_FilterSource) with String data type, and
initialize its value with a T-SQL statement like "SELECT * from orders".
You can also use a filtered query statement as its value. Also, you need to
set its property "EvaluateAsExpression" as True.

4. Click the Data Flow panel, select the Properties window, select
Expressions, click the button (...), select the Property as [DataReader
Source].[SqlCommand], input "@[User::SQL_FilterSource]" to the expression
field. You can also do this via clicking the button "...", and drag the
variable to the Expression field.

5.Save your package.

Then you can run it via dtexec command like the following:
dtexec /FILE "F:\Projects\ISPFilter\ISPFilter\filter_source_transfer.dtsx"
/SET
"\Package\FilterSourceTask.Variables[User::SQL_FilterSource].Properties[Valu
e]";"SELECT * FROM ORDERS WHERE ORDERID<10800"

--Wacle

没有评论: