SQL Server - Challenge converting DTS Lookups to SSIS Package

Asked By Fraz

17-Nov-09 05:28 PM

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.
UPDATE dbo.TableName
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.
Thanks,
Fraz

01-Dec-09 06:41 AM

Hi Fraz,

Hope this is not too late to reply to your question..

Here is what you need to do:
1) Goto "Advanced" tab and click "Enable Memory Restrictions"
2) Click the Modify the SQL Statement
3) Click the parameters button


You should able to see the parameters here, set the value for your parameter
accordingly..

Thanks,
Arun