从Power Query转SSIS的新手：能否创建同类条件列？

Absolutely! SSIS absolutely lets you create conditional columns just like you did in Power Query—here’s how to replicate that exact logic you described, step by step:

Using the Derived Column Transformation (Most Similar to Power Query) #

This is the closest equivalent to Power Query’s conditional column feature, as it lets you define logic directly in a visual editor with no coding required.

1. Add the Derived Column Transformation to your Data Flow Task, connecting it to your source component (the one that reads your file names).
2. Open the Derived Column Editor:
   • In the Derived Column dropdown, select <Add new column> and name your new column (e.g., Region).
   • Paste or write this expression in the Expression box to match your Power Query logic:

     (FINDSTRING(UPPER(FileName), "USA", 1) > 0 || FINDSTRING(UPPER(FileName), "CANADA", 1) > 0 || FINDSTRING(UPPER(FileName), "UNITED STATES", 1) > 0 || FINDSTRING(UPPER(FileName), "AMERICA", 1) > 0) ? "North America" : NULL(DT_WSTR, 50)
     

Breakdown of the expression: #

• UPPER(FileName) converts the file name to uppercase, making the match case-insensitive (remove this if you need strict case-sensitive matching).
• FINDSTRING() checks if a substring exists in the file name; it returns the position of the substring (a value greater than 0 means the substring is present).
• || acts as a logical "OR" to check if any of your target terms are present.
• The ternary operator ? : returns "North America" if any condition is true, otherwise NULL(DT_WSTR, 50) (adjust the string length 50 if needed for your use case).

Optional: Using a Script Component (For More Complex Logic) #

If you ever need more flexibility (like regex matching or dynamic term lists), you can use a Script Component instead:

1. Add a Script Component to your Data Flow, set it as a Transformation, and select FileName as an input column.
2. Edit the Script:
   • Choose your preferred language (C# or VB).
   • Replace the default code with something like this (C# example):

     using System.Linq;
     using System;
     
     public override void Input0_ProcessInputRow(Input0Buffer Row)
     {
         string fileName = Row.FileName;
         string[] northAmericaTerms = {"USA", "Canada", "United States", "America"};
         
         // Check if any term exists in the file name (case-insensitive)
         bool matches = northAmericaTerms.Any(term => fileName.IndexOf(term, StringComparison.OrdinalIgnoreCase) >= 0);
         
         // Assign value to the new column (make sure you add the column in the Script Component's Outputs tab first!)
         Row.Region = matches ? "North America" : null;
     }
     

   • Don’t forget to add your new Region column in the Outputs tab of the Script Component editor before running the script.

Either method will give you the exact conditional column behavior you had in Power Query. The Derived Column is best for straightforward logic like your example, while the Script Component handles more edge cases or custom requirements.

内容的提问来源于stack exchange，提问作者user2407147