如何在SSIS中动态存储表架构名并映射CampusID至数据仓库

Hey, I get exactly why creating 15 separate SSIS packages feels like a total drag—let’s fix that with a dynamic approach that handles all your campus schemas in a single package. Here’s how to pull it off step by step:

1. Set Up Core Variables #

Start by creating these variables in your SSIS package (adjust data types as needed):

• @SchemaName (String): Stores the current campus schema name we’re processing (e.g., AucklandPark)
• @CampusID (Int32): Holds the mapped campus ID for the current schema
• @SourceQuery (String): Dynamically generates the SQL to pull data from the correct schema’s Marks table

2. Fetch All Campus Schemas #

First, we need a list of all campus schemas in your OLTP database. Add an Execute SQL Task with this query to grab the schema list:

SELECT name AS SchemaName
FROM sys.schemas
WHERE name IN ('AucklandPark', 'DurbanCampus', 'CapeTownCampus') -- Filter to your actual campus schemas

Map the result set to an Object-type variable @SchemaList, then wrap the rest of your logic in a Foreach Loop Container that iterates over this list. On each loop, assign the current schema name to @SchemaName.

3. Map Schema Name to CampusID #

You’ve got two solid options here, depending on how your campus data is stored:

Option A: Query the Campus Table (Dynamic Mapping) #

Add an Execute SQL Task inside the Foreach Loop with this query:

SELECT CampusID
FROM dbo.Campus
WHERE CampusName = ? -- Match CampusName to your schema name

Set up a parameter mapping to pass @SchemaName as the input parameter, then map the result to @CampusID.

Option B: Hardcode Mappings (Fixed Relationships) #

If your schema-to-CampusID mapping never changes, use an Expression Task instead to skip the database call:

@CampusID = (@SchemaName == "AucklandPark" ? 1 : 
              @SchemaName == "DurbanCampus" ? 2 : 
              @SchemaName == "CapeTownCampus" ? 3 : 
              0) -- Add all 15 campus mappings here

4. Generate Dynamic Source Query #

Set an expression on the @SourceQuery variable to build the correct SELECT statement for the current schema:

"SELECT MarkID, StudentID, FA_1, FA_2, FA_3, SA_1, SA_2, INT_1, INT_2, INT_3 FROM " + @SchemaName + ".Marks"

This will automatically update the query to target the right campus schema on each loop.

5. Build the Data Flow #

Add a Data Flow Task inside the Foreach Loop, and configure it like this:

• OLE DB Source: Set the data access mode to SQL command from variable and select @SourceQuery—this pulls data from the current campus’s Marks table.
• Derived Column Transformation: Add a new column named CampusID and set its value to the @CampusID variable. This injects the mapped campus ID into your data stream.
• Lookup Transformations (if needed): Use these to map OLTP StudentID to data warehouse DW_StudentID, and any other dimension IDs (like CourseID or DW_FacilitatorID) required by your dbo.Marks table.
• OLE DB Destination: Map all columns (including the new CampusID) to your data warehouse’s dbo.Marks table and configure the insert logic.

6. Test & Refine #

• Test with one campus first to verify variable assignments, query generation, and data insertion work correctly.
• Once that’s solid, run the full loop to process all 15 campuses.
• For performance, consider adding batch processing or indexing on your OLTP Marks tables if you’re dealing with large datasets.

Reference Table Definitions #

Your OLTP Marks table (example schema):

CREATE TABLE AucklandPark.Marks( 
    MarkID INT PRIMARY KEY IDENTITY, 
    StudentID INT NOT NULL FOREIGN KEY REFERENCES AucklandPark.StudentInfo(StudentID) ON DELETE CASCADE, 
    FA_1 TINYINT CHECK(FA_1 BETWEEN 0 AND 100), 
    FA_2 TINYINT CHECK(FA_2 BETWEEN 0 AND 100), 
    FA_3 TINYINT CHECK(FA_3 BETWEEN 0 AND 100), 
    SA_1 TINYINT CHECK(SA_1 BETWEEN 0 AND 100), 
    SA_2 TINYINT CHECK(SA_2 BETWEEN 0 AND 100), 
    INT_1 TINYINT CHECK(INT_1 BETWEEN 0 AND 100), 
    INT_2 TINYINT CHECK(INT_2 BETWEEN 0 AND 100), 
    INT_3 TINYINT CHECK(INT_3 BETWEEN 0 AND 100) 
); 
GO

Data Warehouse Marks table (simplified):

CREATE TABLE [dbo].[Marks]( 
    [DW_MarkID] [int] IDENTITY(1,1) NOT NULL, 
    [MarkID] [int] NOT NULL, 
    [DW_StudentID] [int] NOT NULL, 
    [CourseID] [tinyint] NOT NULL, 
    [CampusID] [tinyint] NOT NULL, 
    [DW_FacilitatorID] [int] NOT NULL, 
    [DateID] [int] NOT NULL, 
    [FA_1] [tinyint] NULL, 
    [FA_2] [tinyint] NULL, 
    [FA_3] [tinyint] NULL, 
    [SA_1] [tinyint] NULL, 
    [SA_2] [tinyint] NULL, 
    [INT_1] [tinyint] NULL, 
    [INT_2] [tinyint] NULL, 
    [INT_3] [tinyint] NULL
);

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