1

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Kate
  • 445
  • 3
  • 9
  • 22
  • Help me understand what you're trying to do here. You have a source file and a ?reference file. For values in the source, you want to match against the reference/map file and add additional data to your work flow? Examples would greatly improve this question (click the edit button) – billinkc Jun 29 '19 at 16:26
  • It's difficult to understand what you want to do. If you show some basic sample data I'm sure it would help. – Nick.Mc Jun 30 '19 at 08:46
  • @billinkc you got it. Yes 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?Hope this explains it. – Kate Jul 01 '19 at 01:57
  • @Nick.McDermaid I just explain problem few seconds ago in above comment . Hope this explains problem .Thanks – Kate Jul 01 '19 at 02:01

2 Answers2

1

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thank you so much this is helpful information. I will try this option tonight to see if this solve my problem . – Kate Jul 01 '19 at 13:23
0

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124