Avatar of fippif
fippif

asked on 

SSIS - Loop within an "Union All" Transformation?

I have an "Union All" transformation that inputs the following:
a. A dataset with columns 1, 2, and 3 (with several rows):

column1      column2      column3
   11            12            13
   21            22            23
   31            32            33



b. A dataset with only one column and only one row:

OnlyColumn
       5



I would like to add item b to item a. The "Union All" transformation would work, except that I need the following result:

column1      column2      column3      OnlyColumn
    11               12           13                 5
    21               22           23                 5
    31               32           33                 5

But I am getting the following result:
column1      column2      column3      OnlyColumn
    11              12           13                  5
    21              22           23               NULL
    31              32           33               NULL
 
Is there any way to make this happen?
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
k_rasuri
Avatar of fippif
fippif

ASKER

Actually, the UNION ALL transformation is giving me the following output:
column1      column2      column3      OnlyColumn
   NULL         NULL            NULL               5
    11               12           13                     NULL
    21               22           23                     NULL
    31               32           33                     NULL

So it would not work at all. Is there another transformation I can use to accomplish the result?

Thx.
SOLUTION
Avatar of k_rasuri
k_rasuri

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of k_rasuri
k_rasuri

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of fippif
fippif

ASKER

I have tried to explain the issue better in the attached word doc. I hope this helps. Thx.
SSIS-Question.doc
ASKER CERTIFIED SOLUTION
Avatar of k_rasuri
k_rasuri

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo