SSIS look up table questions

I am using SSIS (SQL Server 2008) to lookup values from a table. I wanted to know if the following are possible:

Question 1

Lookup Table 1

Manager_Name    EMP_UNIT    Job_Profile_ID
AAA             SALES       27
BBB      开发者_运维百科       HR          28
AAA             SALES       29

I have to pass the ‘Manager_Name ‘and ‘EMP_UNIT ‘value in the Lookup table and fetch Job Profile ID in Comma separated format. Like I will pass the Values ‘AAA’ and ‘SALES’ and I want the return value as 27,29 How will I do this?

Question 2

Lookup Table 2

Job_Profile_ID  Job_Name
27              Jr. Salesman
28              Sales Manager
29              Sr. Salesman

I have to pass the Job Profile ID values in Comma separated format (27, 29) and I want the return value in Comma separated format (Jr. Salesman, Sr. Salesman). Is this possible in SSIS?

Environment details: MS SQL Server integration services (bids) 2008 on windows 2008 server

any help is appreciated,

Thanks

If I understand your question correctly, you need the list of job names for a given manager and employee unit combination. If that is the case, you need a stored procedure that can give the list of job names in a comma separated value.

In my opinion, two lookup transformation tasks seems to be a overkill.

Based on the data provided in the question, the section Create and populate tables provide the sample data.

Create the following stored procedure dbo.GetManagerJobProfiles that will take Manager_Name and Emp_Unit as input parameters and will return the list of matching job names for the given parameters as a comma separated list. This stored procedure uses FOR XML clause to generate the comma separated values. Since the comma is appended at the beginning, we have to truncate the first comma from the list. Hence, the substring function is used to do that job to give a cleaner output.

CREATE PROCEDURE dbo.GetManagerJobProfiles
(
        @Manager_Name   NVARCHAR(80)
    ,   @Emp_Unit       NVARCHAR(80)
)
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT SUBSTRING(
            (
                SELECT          ', ' + J.Job_Name
                FROM            dbo.Managers        M
                LEFT OUTER JOIN dbo.Jobs            J
                ON              M.Job_Profile_Id    = J.Job_Profile_Id
                WHERE           M.Manager_Name      = @Manager_Name
                AND             M.Emp_Unit          = @Emp_Unit
                FOR XML PATH ('')
            )
        , 3, 1000) AS Job_Name
END
GO

Screenshot #1 shows the sample data in the tables dbo.Managers and dbo.Jobs.

Screenshot #2 shows the stored procedure output for two different sets of parameters.

If I have to use this in an SSIS package, I would get the list of distinct Manager_Name and Emp_Unit combinations using an Execute SQL Task and will populate the resultset into an SSIS Package variable of data type Object.

I will then loop through the object variable using Foreach loop container with Foreach ADO enumerator. Within the Foreach loop container, I will place a Data Flow Task. Within the data flow task, I will place an OLE DB Source which will use the stored procedure as the source. For each Manager_Name and Emp_Unit combination being looped through, the values will be passed as parameters to the OLE DB Source to fetch the Job name values.

Hope that helps.

Create and populate tables: This structure is based on the data provided in the question.

CREATE TABLE [dbo].[Jobs](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Job_Profile_Id] [int] NOT NULL,
    [Job_Name] [nvarchar](40) NOT NULL,
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Managers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Manager_Name] [nvarchar](80) NOT NULL,
    [Emp_Unit] [nvarchar](80) NOT NULL,
    [Job_Profile_Id] [int] NOT NULL,
CONSTRAINT [PK_Managers] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

INSERT INTO dbo.Managers (Manager_Name, Emp_Unit, Job_Profile_Id) VALUES
    ('AAA', 'SALES', 27),
    ('BBB', 'HR', 28),
    ('AAA', 'SALES', 29);

INSERT INTO dbo.Jobs(Job_Profile_Id, Job_Name) VALUES
    (27, 'Jr. Salesman'),
    (28, 'Sales Manager'),
    (29, 'Sr. Salesman');

I can help you with question 1, but i'm not sure about your second question.

To look up a single column, you would normally use the LookUp component, matching the search columns accordingly and returning the corresponding value.

However, as you are expecting multiple values to be returned, this is not going to be an option for you as the LookUp component will only return the first matching value.

To achieve multiple results per starting row, you will have to use two source controls (one for main table and other for lookup table) along with the Merge Join control

The merge join control will enable you to combine both sets of data, however when multiple matches exist you will get multiple rows. So in your example, AAA and Sales as input will provide two rows;

• AAA, Sales, 27
• AAA, Sales, 29

You can then pass this data to a destination table.

For the second question, it is unusual to query and expect a consolidated response with comma separated values in SQL Server.

If you able to utilise the data with multiple search and result rows (i.e. search for row id 27 first and then row with id 29) then use the lookup task will suffice.

Otherwise, maybe make a second pass (new data flow task) and use the first pass temporary table to consolidate the data (using a script?) into your desired csv format.