CODEWITHSUNDEEP
sql-server-2008ssisbids
Matt
Matt

Reputation: 13

SSIS: How to incorporate a Lookup with the following problem

I have a DTS pkg that i am converting to SSIS. I am reading from a text file. In the beginning of each line, there is a hex code. If the hex code matches a value in a table, i want to add it to another table. If if the hex code does not match, i want to skip the row.

In DTS I could do a dtsLookup within an ActiveX Script to perform a lookup while the ActiveX Script was executing. How can i Incorporate this kind of a look up into SSIS 2008 in BIDS.

Thank you, very much appreciated.

*****************Edit:*************** To be a little clearer. I want to perform an SQL Query with the hex code. Then if the hex code returns a value i want to continue parsing the text file. Then add my results to a table. I know how to do this in Script component, the only piece I am missing is the Lookup. I used to be able to do this in dts::: DTSLookups("nameOfLookup").Execute("Variable") Thanks again!

Upvotes: 0

Views: 483

Answers (2)

bobs
bobs

Reputation: 22184

You probably don't need a script component at all for you requirement. SSIS has the Data Flow task which has the components you need. And, as @Ed Harper indicated, the Lookup Transformation is the part you can use to do your lookup.

In the Data Flow task, you can start with the Source component that will read from the file. The rows will go through a Lookup component, where you define the query that gets the hex value from the table.

You can configure the Lookup component to pass rows with a matching hex value to the destination. You can also configure the component to ignore errors, which a no-match will produce. These rows will not go to the next step in the data flow.

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21495

It may take a bit of work to transform your existing DTS process into the SSIS idiom, but possibly you need the DataFlow Lookup Transformation?

Upvotes: 1

Related Questions