Parameters to DTS

Hello,
Is there a way to pass parameters to the DTS package?
For example, we are using DTS to archive data from database into a flat file
every month. Is there a way to pass the name of the file and the
dateFrom/dateTo values to the DTS?
Thanks in advance.
ArsenA million ways
What version SQL Server
2000 look at the /A parameter to DTSRUN
2000 look at a dynamic properties task
7 I like to DataPump to Global Variables and then use the object model
Use the object model to execute the package and pass values to Global
Variables which sets properties of the object model.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
news:OST3tdlZDHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Is there a way to pass parameters to the DTS package?
> For example, we are using DTS to archive data from database into a flat
file
> every month. Is there a way to pass the name of the file and the
> dateFrom/dateTo values to the DTS?
> Thanks in advance.
> Arsen
>

Parameters in data flow task with Oracle database source

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

pshotts wrote:

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

below is workaround, but i don't know how "obvious" it is:

Use property expressions. The SQL statement you use to query the Oracle source can be set by a variable, and that variable can be composed using a property expression such as: "select * from Orders where OrderID > " + @.LastOrderID. It's a parameterized query - and a very flexible one - in all but name.

|||Thanks Duane, but that does not get around the 4000 character limit on variable and expression length|||

Can you use a combination of query strings (in SSIS) and views in Oracle to reduce the amount of work you are doing in the one string?

Another alternative might be to execute a stored procedure that builds a temporary table (passing the parameters to the procedure), then read from the temporary table.

A third alternative would be to pull back the various discrete data sources from Oracle and handle joining/merging within SSIS. Can't say I'd recommend this one though. Better to just pull across the data you need from the remote source.

|||

Another option is to use a script source in which you concatenate the query string and call Oracle using OLEDB or ADO.Net in the script.

Donald

|||

Thanks Donald

That sounds like a good option. I understand that you mean we should use a Script Component as the Source in the Data Flow. The script will make the connection and build and run the required query.

Nice!

|||

That's correct.

You can still use a connection manager with your script component, to take advantage of that feature, but even that is not essential. although recommended.

Donald