Is there a way to use an ADO.NET grid to hold data inside an SSIS package? I'd like to load a grid with Unique IDs from a SQL Server table then loop through the grid and update each record with values from the cooresponding record in the Oracle database. Is this possible?

In order to use an Data Grid you would need to do all the work within a Script Task.  This will let you code the entire thing within VB.NET.

As an option you should be able to put your recordset into an XML document and then use a foreach loop container to process the XML document.  You should be able to use a Script task to then update the XML document with the values from the Oracle database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

I don't see the purpose of holding the records in a grid at all.  If the unique ID is what you use to join the data on and update the MS database from the Oracle values then why not build a process that pulls the needed records from both systems joins them into a single flow then compare the values in a conditional split, and output the changed records.  You funnel this output into an OLEDB commad which executes a stored procedure that accepts the values and updates the appropriate record.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

Huh?  

Actually, that sounds great. I'm new to SSIS. I'll start looking up everything you said. Thanks.

How would I pull the data from both systems? Is there a way to run a join in SSIS from heterogenous datasources?

yes you use the merge join task within a dataflow.  however this task requires a sorted data input which you ca either do in your datasource object, or the sort transform.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

I created a DataReader to retrieve the records from the Oracle database and used a query and used "ORDER BY" to sort the data coming in. I created an OLE DB Source to retrieve the data from the SQL Server table and used ORDER BY in the query as well. I connected the 2 to a MergeJoin transaction but I'm getting the following error when I double clivk the mergeJoin to set the properties:

The " input "Merge Join Left Input" (3020)" must be sorted.

What am I doing wrong? I thought using ORDER BY in each query sorted them.

Is that the Oracle or SQL connection which it doesn't think is sorted?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

I am not sure about the datareader source as I only use OLE DB but here it goes.

The dataflow is not aware that your dataset is ordered by just including an order by task.  To acomplish this you must:

1. Right click on the datasource object and choose advanced settings (no sure 100% what it is but you should notice it).
2. The last Tab in the Advanced Configuration dialog has the metadata regarding your object. Select the output and the properties should have a IsSorted setting change this to True.
3. Expand the output to show each column. Each Column has a SortKey property.  These need to be set from 1 - n based on the order you specified in your select statement.

Both Dataflows must have the Exact sort order for the Columns.  The merge join doesn't care about the names just the sortkey  Sorted Column 1 must join to Sorted Column 1.

ALSO...

Your datatypes must match.  If you have different datatypes you can also change this in the advanced configuration editor.

Good Luck

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

I have 2 sources and the merge set up. I have added an OLE DB Destination and seleted the table I want the data to go in but I'm getting an error stating:

"Column "Column_Name" cannot convert between unicode and non-unicode string data types".

I treid to go in the source and change the data type but I wasn't allowed to change it. This is the data coming from the oracle table going into the SQL table.

at somepoint in your dataflow you need to add a data conversion task and convert all your unicode characters to nonunicode.  THe better way would be to change the data types on your SQL Server side that way you avoid potential problems down the road.


The data conversion task does not chage the datatype of the column but adds a column to the output for each column coverted.

I usually do my data conversions as early in the process as possible so I am not carrying un used columns through the entire dataflow.  You can do this before your merge join and not flag the unicode columns to pass through the merge.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

I was able to get the merge sort to work however it takes too long to run. It appears to return every record from the Oracle db first then merges based on the 10k records from the SQL Table. Is there a way to use a foreach container to select the 10k records out of the Oracle database instead of comparing with all records in the oracle table? The oracle table has 90 million records in it.

is there a column in the oracle side that maintains a records creation or modification date?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

yes...well we have something we can use. I'll use that as a filter