CODEWITHSUNDEEP
csvssisetlflat-file
Kate
Kate

Reputation: 455

Can we compare columns of multiple inputs file to derive a new column in SSIS

I am trying to create a derived column based on columns provided in different input file but unfortunately I keep getting error when I tried to map my Raw_File_1 with Derived Column.The error looks like this:

Cannot create a connector. The destination component does not have any available inputs for use in creating a path.

My goal is to able to connect both Raw_File_1 and Map_File_1 into Derived Column and generate a new column.

If anyone can provide me any suggestion that would be great!!

I have source file and reference file both are flat file. My source file has column a, column b and column c and my reference file has column d, column e and column f.

If column a=column d and column b=column f then I want to populate column c as the same value as column f. How can I do this kind of analysis or lookup in SSIS

enter image description here

Upvotes: 1

Views: 289

Answers (2)

billinkc
billinkc

Reputation: 61231

Based on your comments that I patched into the question, you're looking to augment the existing data based on matching data from your reference file.

The core of your SSIS package will look like this

Control flow has 2 data flow tasks

In the first data flow, we will source from map_file_1 and load into a "raw" file.

enter image description here

I configure my raw file destination like this

enter image description here

When the package runs, it'll fill that special format file with the reference data. It's important, because you can either use a database or a raw file as your lookup source.

Finally, we get to work! A flat file source to a Lookup component. In the first tab of that lookup, be sure to change the Connection type from the default of "OLE DB connection manager" to "Cache connection manager"

In Connection tab, click to create a new CCM and use the raw file generated in the preceding step.

Map columns A to D and B to E (assuming data types match). Click the check box on column F and in the Lookup Operation part, Replace C with that value.

enter image description here

Final thoughts

This will be a case sensitive lookup. If things don't have a match in the reference file, it's going to blow up. That's probably not what you want so configure the Lookup transformation to not do that ;)

I blogged about using Excel to populate the cache if you want more words http://billfellows.blogspot.com/2011/11/using-excel-in-ssis-lookup.html

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Your question is not clear, i will try to give some suggestions:

If you are looking to perform a lookup with a derived column:

You can use Cache Transform component and Cache connection manager to achieve that:

If you are looking to Merge both input:

Then you need to use Merge Join or Union All components:

Upvotes: 0

Related Questions