sql server dts: hi , i have two qustions
1. is it possible to use parameters in the data flow area of sql server
2005 dts?
if yes how?

2. how can i execute an insert command that get the values from a
global parameter - an object type taht now contains a recordset.

thanks dana

Hello dana,

Parameters to what and to do what?

You can certainly used a parameterised SQLStatement. Do you intend to use
the parameters to redirect data flow?


A Global Parameter? Do you mean a variable?


There isn't the concept now of a global variable as variables are scoped
to containers/executables. The highest level in that chain would be the
package itself.

If you want to shred a recordset then you could look to do something like
this


Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)


Allan


[quoted text, click to view]

Any data you will use in the data flow has to have been sourced using the
supported sources: Flat file, Excel source, OLE DB, ADO.NET or it must be
derived from columns thus sourced or scalar variables (not object, as there
is no way to navigate object variables like recordsets within SSIS expression
language). You can use variables for the table names or SQL queries that
source the data.

If a previous task placed the data you want in a recordset, consider using
the raw file destination instead, then picking that up in your data flow. If
you are somehow restricted to using a recordset, then you have to use a
Script task to navigate that recordset yourself. Make sure you copy the
adodb.dll from the Primary InterOp Assemblies folder to your
...\Microsoft.NET\Framework\v2.0.50727 folder and add a reference in the
script.

But the best solution is not to use a recordset; the raw file transformation
is designed to pass data between components.

It all depends on your scenario, which I don't know. Another method you
could consider is using the Lookup transformation, which will look up
information that you can then insert. But the lookup (reference) table must
be stored in an OLE DB provider source.

Charles Kangai, MCT, MCDBA

[quoted text, click to view]