I have already migrated a SQL 2000 DTS package to SQL 2005 DTS Legacy and it
is working fine. Now I am trying to convert it to SSIS package. It is a SQL
2005 64-bit environment and I am having issues like failed to load "Execute
DTS 2000 Package Task". I think "Execute DTS 2000 Package Task" is not
supported in SQL 2005 64-bit so I have decided to create the package from
scratch. The flow in the SQL 2000 DTS package is:
1. Source OLE DB connection
2. Destination OLE DB Connection
Transform Database Task Properties:
3. Source: SQL Query
4. Destination : TableName
5. Transformation : Source-Destination Field mapping
6. Lookups: There are two lookups:
(a) Get RefNo from destination table e.g.
(Select RefNo from dbo.TableName where status is NULL)
(b) Update Destination Table using Source SQL Query and (a) Lookup e.g.
SET Field1 = ?, Field2 = ?, Field3 = ?, Status = ?
WHERE (RefNo = ?)
In the SQL 2005 BIDS, I have created OLE DB Source and Destination
connection but for Lookup for Update Statement I am having issue. How to use
the parameterized Lookup in SQL 2005. Your assistance is appreciated.