0
votes

Want some ideas how to implement the below logic please?

This question relates to SSIS package. I am reading flat file of fixed width, which will contain at least 500 rows. I have a lookup table in SQL with a single two columns ID and Description. If any of the rows of flat file contain the lookup table description column data, then those rows should not be processed.

What I have done so far:

I am using a script task and loading lookup column data in to data reader. And checking every column of the input file in the script task to see if it contains the data reader items, then that row is not processed. I know this giving me bad performance, so I just need an approach how to tackle this problem. I am not asking for solution. Please let me know, if you need more information about the problem.

1

1 Answers

0
votes

If I understand correctly, the flat file being read contains all of the columns, but the values for the "lookup table description" column will either be NULL or populated? If this is the case, then an easier alternative approach would be to create a staging table in your database. When the SSIS package runs, it is first truncated and then repopulated with the contents of the flat file. This should run pretty quickly. Then apply a SQL task using perhaps a MERGE command to import the records from staging to the final table that do not contain the lookup table description column data. I've used staging tables and staging databases many times as it also provides an easier means to verify that all of the data is being properly read from the source and automate the proactive checking for potential data problems originating from the source.

Otherwise, if the number of columns differs from row to row in the source flat file ("lookup table description" columns exists or not), then your script task is the best approach.

Hope this helps.