How to ignore columns when using union all

Question

How to ignore columns when using union all

jagadeeshb

Right there with Babe

Points: 782
More actions
December 22, 2009 at 6:37 am

#215049

Hi,
I have written one ssis package in that i am getting data from two tables one from oledb source1 and another from oledb source2.
In first one i am getting 10 columns and in second one i am getting 6 columns. I want to display these in one flat file by adding new column.
So i added one new column by using derived column.
When i using union all i am getting two different records with 16 columns.
But my intension is i want to get two different records with 10 columns will be in one row and 6 columns will be in another row. Here i mapped the columns and ignoring the columns in union all but there i am getting spaces. But i dont want spaces there and i want only the 10 columns in first row and 6 columns in second row.
Can any one help me to ignore columns as well as spaces when using union all control.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

The result of a union can be thought of as a single table with x rows and y columns with various datatypes - you can't mix and match within that table. The best you should be able to do is get the 'non-existent' columns populated with NULL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jagadeeshb Right there with Babe Points: 782 More actions · Answer 2

Hi Phil,

Here i am not matching the columns just i want to ignore some columns and display only mapped columns without spaces.

Thanks,

Jags

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 3

[font="Comic Sans MS"]

As Phil has already mentioned - you can not just mix and match the columns here.

From my understanding - you just want those 2 datasets to be literally merged into a flatfile. What you can do is - wtite those 2 datasets into different flatfiles and then execute a script task or execute process task (use : copy a.txt+b.txt c.txt) to merge them ..

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Just add a Carriage Return/Line Feed at the end of column 10.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jagadeeshb Right there with Babe Points: 782 More actions · Answer 5

Hi,

Is anyone knows how to copy two files content into one file using script or process task.

Please help me its urgent.

Thanks,

Jags

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

Sabyasm told you already - what is wrong with that method?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jagadeeshb Right there with Babe Points: 782 More actions · Answer 7

Hi,

I want to add two text files into one text file using script Task.

I know how to copy source file into destination file, the code is

Ex:System.IO.File.Copy(Dts.Connections("Sourcefile").ConnectionString, Dts.Connections("Destination file").ConnectionString)

but i dont know how to add two sources file into one destination file

Can you give me the code for that please.

Thirumalesh SSC Rookie Points: 25 More actions · Answer 8

when you are using union all there is no chance of getting the data from only the intended columns you wish to display. union all supports only if the TDs of all the tables are matching.