How can I group multiples input in one output

2023-03-13 11:56 问答作者：

I have an Excel like this

region    state    city开发者_运维百科
-------------------------
south     state1    city1
north     state2    city2

and I want to fill a data base table like this

  id      name       type
 ------------------------
   1      state1     state
   2      south      region
   3      city1      city

But I don't have any idea about how to group these columns and headers to fill the data base table. Any ideas? I'm pretty lost with this app

You can achieve this in SSIS using Unpivot Transformation available in Data Flow Task. Following example illustrates how this can be done. The example uses SSIS 2008 R2 and SQL 2008 R2 database.

Step-by-step process:

Create an Excel file with sample data as shown in screenshot #1. I have named the file as Source.xlsx.
Create a table in the SQL Server database named dbo.Destination using the script given under SQL Scripts section. This table will be populated with Excel data. I have introduced a new field named GroupId so the data can be grouped together.
On the SSIS package, create an Excel connection named Excel and an OLE DB connection named SQLServer as shown in screenshot #2. Excel connection manager should be configured as shown as in screenshot #3. OLE DB connection will be configured to connect to the database of your choice.
On the package's Control Flow tab, place a Data Flow Task as shown in screenshot #4.
Configure the Data Flow tab as shown in screenshot #5 with an Excel source, Script component, Unpivot transformation and an OLE DB destination.
Configure the Excel Source as shown in screenshots #6 and #7. This will read the data from Excel file.
Configure the Script Component as Transformation and add an Output column as shown in screenshot #8. On the Script section, click Edit Script and replace the code with the code given under Script Component Code section.
Configure the Unpivot transformation as shown in screenshot #9. We don't want to transform the GroupId, so don't configure that but make it to Pass Through.
Configure the OLE DB destination as shown in screenshots #10 and #11.
Screenshot #12 shows data in the table dbo.Destination before the package execution.
Screenshot #13 shows the package execution.
Screenshot #14 shows data in the table dbo.Destination after the package execution.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NULL,
    [Type] [nvarchar](255) NULL,
    [GroupId] [int] NULL,
 CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

Script Component Code:

C# code that can be used only in SSIS 2008 or above.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    int groupId = 1;
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.GroupId = groupId;
        groupId += 1;
    }
}

Screenshot #1:

How can I group multiples input in one output