Data Flow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Data Flow

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 11:50:47

I'm back!

,

My boss now wants me to capture all data (the one's that match this query and the one's that don't)

SELECT nd.*
Into Cleared
FROM NewDiary AS nd
WHERE NOT EXISTS (SELECT * FROM LastWeekDiary AS lwd WHERE lwd.Pan = nd.Pan)
ORDER BY nd.LName

I now need a pending table if (items don't meet the above criteria). I wanted to do this is Data Flow as I need to use a conditional split later.

I've added the two tables New Diary and LastWeekDiary DB Sources

What could I do next a union?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 11:56:11

just use merge join task and join based on relationship

lwd.Pan = nd.Pan

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 13:03:02

Okay having problems...I have to sort the data first in both tables before joining them to the merge join. I added two sorts and in the available Input Columns selected PAN (there are checks in all of the pass through column). It's sorting in ascending order.

Now I'm connecting one of the green arrows to the Merge Join and I get an Input Output Selection

Output = Sort Output
Input has two options:
1. Merge Join left Input
2. Merge Join right Input

Which one do I select?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 13:07:03

quote:
Originally posted by JJ297

Okay having problems...I have to sort the data first in both tables before joining them to the merge join. I added two sorts and in the available Input Columns selected PAN (there are checks in all of the pass through column). It's sorting in ascending order.

Now I'm connecting one of the green arrows to the Merge Join and I get an Input Output Selection

Output = Sort Output
Input has two options:
1. Merge Join left Input
2. Merge Join right Input

Which one do I select?

one of the source table path as merge join left and other path as merge join right. ALso based on experince, its better to order records directly from source by using ORDER BY in OLEDB source rather than using Sort task as sort task impacts performance of package.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 13:44:05

Where do I set up the order by on the DB Source? In the Advance Editor changing IsSorted to True and Sort Key Position to 1?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 13:47:14

quote:
Originally posted by JJ297

Where do I set up the order by on the DB Source? In the Advance Editor changing IsSorted to True and Sort Key Position to 1?

not only that. instead or using table or view option use command option and write query as

SELECT columns
FROM YourTable
ORDER BY reqdfield

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 14:04:20

Okay great thanks for teaching me that I didn't know I could access the table that way and get things out of it. Whew I've got so much to learn.

Okay now I'm in the Merge Join Transormation Editor

Do I have to select all the check boxes for Source 1 and Source 2? I have the PAN as the Join Key. I have a lot of fields listed.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 14:22:55

I changed the Join Type to Left outer join

I selected all records in source 1 and selected PAn in Source 2

Now I want to add a conditional split to get the results in two tables. What condition do I put in to get the valid rows. This is what I'm looking for:

SELECT nd.*
Into Cleared
FROM NewDiary AS nd
WHERE NOT EXISTS (SELECT * FROM LastWeekDiary AS lwd WHERE lwd.Pan = nd.Pan)
ORDER BY nd.LName

I want the other ones to go into the Pending Table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 14:29:43

quote:
Originally posted by JJ297

I changed the Join Type to Left outer join

I selected all records in source 1 and selected PAn in Source 2

Now I want to add a conditional split to get the results in two tables. What condition do I put in to get the valid rows. This is what I'm looking for:

SELECT nd.*
Into Cleared
FROM NewDiary AS nd
WHERE NOT EXISTS (SELECT * FROM LastWeekDiary AS lwd WHERE lwd.Pan = nd.Pan)
ORDER BY nd.LName

I want the other ones to go into the Pending Table.

ok give condition as lwd.Pan IS NULL for valid
and lwd.Pan IS NOT NULL as Pending

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 14:43:00

Not quite understanding you...

Inthe conditional split transformation I have:

Output Name = ValidRows
Condition = ISNULL(PAN)

Default outputname Pending

It's not working getting this for all fields:

[DTS.Pipeline] Warning: The output column "RGN" (7179) on output "OLE DB Source Output" (6880) and component "OLE DB Source 1" (6870) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:53:16

quote:
Originally posted by JJ297

Not quite understanding you...

Inthe conditional split transformation I have:

Output Name = ValidRows
Condition = ISNULL(PAN)

Default outputname Pending

It's not working getting this for all fields:

[DTS.Pipeline] Warning: The output column "RGN" (7179) on output "OLE DB Source Output" (6880) and component "OLE DB Source 1" (6870) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

not ISNULL(PAN) but PAN IS NULL
and connect true output to cleared table population step and else output to pending table step.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-29 : 09:14:04

Good Morning,

PAN IS NULL is not working it remains Red and gives me the following error:

Error at compariing files: Atempt to parse the expression "PAN IS NULL" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources