Filter data in OLE DB Source

[Daniel]

Hi,

I'd like to filter data in the OLE DB Source node based on a variable value,
to realize the function simliar to "SELECT * FROM Table WHERE Col1=@var1".
But it appears to me that the only way I can do this is manually structure a
sql statement in a Script Task node and execute it in the data source node.
This doesn't look very appealing to me. Is there any better way to achieve
the goal?

Thanks.

Daniel

[Charles Wang[MSFT]]

Hi Daniel,
I understand that you would like to know if there is any other way to
implement a parameter query to filter data in your OLEDB source node in
addition to using Script Task.
If I have misunderstood, please let me know.

Is it a SQL Server 2000 DTS package or a SQL Server 2005 SSIS package?
Anyway you can use Execute SQL Task to create a parameter query by using
GLOBAL VARIABLES. Take SQL Server 2000 DTS package for example, open DTS
Package Designer in Enterprise Manager, drag a Connection for your OLEDB
data source, drag a Execute SQL Task, double click on it, input the
description and T-SQL statement such as:
SELECT Orders.OrderID, Orders.OrderDate, Orders.Freight,
Orders.ShipName, Orders.ShipCountry
FROM Orders
WHERE
(Orders.OrderID = ?)

Click the Parameters and create a new global variable:
OrderID Unsigned Int(4 byte) <default value>

Then you can run it by dtsrun utility with specifying a value to the global
variable:
dtsrun /Ssha-chlwang-2k3 /E /N MyPackage /A OrderId:19=10255

You may also refer to this article for detailed information:
http://www.supinfo-projects.com/fr/2004/dtsrun_asp_a/0/
Note that there are six parts in this article. Just select them from the
dropdown list.

You may also want the following references for SQL Server 2005 SSIS
packages:
Integration Services Variables
http://technet.microsoft.com/en-us/library/ms141085.aspx

Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive/2006/11/17/amazing-ssis-parameters-in-e
xecute-sql-task.aspx


Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a great day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Daniel]

Thanks Charles. Actually I'm working with SQL Server 2005. The Execute SQL
node you mentioned seems to be in the control flow, instead of data flow. If
I use this node, does it mean that I need to write data into memory and have
another data flow node to process the data? This looks strange if I got huge
amounts of data. I still regard what I want as a common thing that people
will do with data flow, isn't there any cleaner way of doing that?

Daniel

[Charles Wang[MSFT]]

Hi Daniel,
Thanks for your response.

I had thought that you just would like to know how to filter data in OLE DB
Source, so I did not ask your real requirements. If you are trying to
process a huge amount of data, my former suggestions could not get good
performance. In that case, I recommend that you do not use OLE DB Source
either, but use DataReader Source instead since data reader can provide the
best forward read ability. The connection manager for DataReader Source
requires ADO.NET connection.

You can implement it as following:
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"

Hope this helps. Please feel free to let me know if you have any other
questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Charles Wang[MSFT]]

Just clarify that in the 3rd step, it is not neccessary to set
"EvaluateAsExpression" based on my lastest test.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Daniel]

Thanks Charles. This is very helpful. I think the major difference is that
DataReader Source will show up a SQL Command property in Expression while
OLEDB Source doesn't have it.

Daniel

[Charles Wang[MSFT]]

Hi Daniel,
Thanks for your feedback. I am very glad to hear that the suggestions are
helpful.

Yes, there is no SQL Command in the Expression property of OLE DB Source.
However OLE DB Source can also implement your requirement with a VARIABLE.
The settings are simpler than DataReader source; however the performance
may be slower than DataReader source if you have a large amount of data.
Anyway, I would like to post the method here for your reference:
1. Drag an OLE DB Source to the Data Flow panel;
2. Right click the panel, select Variables, create a new variable named
OLEDB_FILTER_SQL and input the value as a T-SQL statement such as "SELECT *
FROM Orders";
3. Double click the OLE DB Source, select its connection manager, select
"Data access mode" as "SQL command from variable" and select "Variable
name" as User::OLEDB_FILTER_SQL;
4. Drag a OLE DB Destination to the panel, drag the green line from the OLE
DB Source to the OLE DB Destination, double click the OLE DB Destination,
set the OLE DB connection manager, set the "Data access mode" as "Table or
view - fast load", select "Name of the table or the view" as a table of
your destination database, switch to the Mappings tab and click OK.
5. Save the package.

Then you can execute it from command line as following:
C:\>dtexec /FILE "F:\Projects\ISPFilter\ISPFilter\olesource.dtsx" /SET
"\Package\FilterOleSourceTask.Variables[User::OLEDB_FILTER_SQL].Properties[V
alue]";"SELECT * FROM ORDERS WHERE ORDERID<10800"

If you have any other questions or concerns, please feel free to let me
know. Have a great day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Daniel]

Thanks Charles. This also works. Actually we only knew this approach before
and it just didn't suit too well in our particular sceanrio.

Daniel

[Charles Wang[MSFT]]

Hi Daniel,
Thanks for your response.
I do understand that SSIS has a good many of new features and it is hard to
know them if we do not have experiences of using them before. Just feel
free to have new posts here if you encounter any other issues in future.

Have a great day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================