Search code examples
sqlsql-serverssisetlssis-2012

How can I create new rows from source column input in SSIS?

My Source data looks like this:

CREATE TABLE [dbo].[exampleSource](
    [PK] [uniqueidentifier] NOT NULL,
    [Name] [varchar](200) NULL,
    [RelatedCodes] [varchar](max) NULL,
    CONSTRAINT [PK_exampleSource] PRIMARY KEY CLUSTERED 
(
    [PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[exampleSource] ([PK], [Name], [RelatedCodes]) VALUES (N'83ce3647-469d-e211-892f-001321b3540c', N'Promo1', NULL)
GO
INSERT [dbo].[exampleSource] ([PK], [Name], [RelatedCodes]) VALUES (N'02641b86-0564-e211-8dd3-001321b3540c', N'Promo2', N'NA')
GO
INSERT [dbo].[exampleSource] ([PK], [Name], [RelatedCodes]) VALUES (N'8d51fcd8-eae7-e111-a266-001321b3540c', N'Promo3', N'Code1;Code2')
GO
INSERT [dbo].[exampleSource] ([PK], [Name], [RelatedCodes]) VALUES (N'e9a37ae1-eae7-e111-a266-001321b3540c', N'Promo4', N'Code3;Code4')
GO
INSERT [dbo].[exampleSource] ([PK], [Name], [RelatedCodes]) VALUES (N'21702ee0-fc2f-e211-a5fd-001321b3540c', N'Promo5', N'Code5;Code6;Code7;Code8')
GO

The codes listed are a PK in another table in my data warehouse, so I want to use this source to create a bridge table.. Desired Result

I know I can use TOKEN in SSIS to split values like those in RelatedCodes into columns.. but how can I create new rows?

Solution

  • You can achieve that using SQL (no need for SSIS transformations) SPLIT_STRING() function with a cross apply. You can refer to the following official documentation for an example: