Execute SQL Task help... - 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)

Execute SQL Task help...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-15 : 14:06:40

I have two tables in SQL 2005 where I have to compare duplicates and place them in another table. I will be receiving an updated file once a week. Is this the best way to go about it?

This is what I have thus far...

1. Data Flow Task

2. Execute SQL Task with SQL statement
SELECT NewDiaries.HUN, COUNT(*) AS Pending, NewDiaries.FName, NewDiaries.LName
FROM NewDiaries INNER JOIN
ItWorks ON ItWorks.HUN = NewDiaries.HUN
GROUP BY NewDiaries.HUN, NewDiaries.FName, NewDiaries.LName

3. Do I add the DB Source and connect it to ItWorks Table?

4. Add two Destinations - DiscardRows and ValidRows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 14:13:04

compare duplicates you mean those existing on both tables? or multiple occurances on both?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-15 : 14:18:54

Existing in both tables.

I want to compare this weeks data with last week data. If the number is in last weeks data and not in this weeks data then that info sohould go into the Pending Table.

I hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 14:23:06

quote:
Originally posted by JJ297

Existing in both tables.

I want to compare this weeks data with last week data. If the number is in last weeks data and not in this weeks data then that info sohould go into the Pending Table.

I hope that makes sense.

it will be like

INSERT INTO Pending(fields...)
SELECT i.fields...
FROM lastweeks l
left join thisweeks t
on t.pk=l.pk
where t.pk is null

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-15 : 14:39:28

Do I need to truncate the info in the Pending Table before excuting this statement...

INSERT INTO Pending(fields...)
SELECT i.fields...
FROM lastweeks l
left join thisweeks t
on t.pk=l.pk
where t.pk is null

Then add the thisweeks DB Source and connect it to a Pending Destination? I'm a bit confused.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 15:14:16

quote:
Originally posted by JJ297

Do I need to truncate the info in the Pending Table before excuting this statement...

INSERT INTO Pending(fields...)
SELECT i.fields...
FROM lastweeks l
left join thisweeks t
on t.pk=l.pk
where t.pk is null

Then add the thisweeks DB Source and connect it to a Pending Destination? I'm a bit confused.

that depends on your requirement. i think u should as you always need only pending info as on that month.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-15 : 15:23:43

Okay so how do I set that up on the Control Flow?

1. Data Flow Task
2. Execute SQL = Truncate Table "Pending"

Can I add the insert statment on the same Execute SQL Task like this?

Truncate Table "Pending"

INSERT INTO Pending(fields...)
SELECT i.fields...
FROM lastweeks l
left join thisweeks t
on t.pk=l.pk
where t.pk is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 04:46:31

quote:
Originally posted by JJ297

Okay so how do I set that up on the Control Flow?

1. Data Flow Task
2. Execute SQL = Truncate Table "Pending"

Can I add the insert statment on the same Execute SQL Task like this?

Truncate Table "Pending"

INSERT INTO Pending(fields...)
SELECT i.fields...
FROM lastweeks l
left join thisweeks t
on t.pk=l.pk
where t.pk is null

Nope you just need two execute sql jobs one for truncate table and other one for insert

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-16 : 08:34:41

Okay so I can set it up this way...

1. DataFlow Task
2. Execute SQL Task (Truncate Table) green's arrow going into
the Insert pending table
3. The second Execute SQL Task (insert into Pending Table) going into the dataflow task.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 09:25:15

quote:
Originally posted by JJ297

Okay so I can set it up this way...

1. DataFlow Task
2. Execute SQL Task (Truncate Table) green's arrow going into
the Insert pending table
3. The second Execute SQL Task (insert into Pending Table) going into the dataflow task.

yup..and wats the first data flow task for?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-16 : 11:49:28

Don't I need that to put the info into the database?

On the Data Flow Tab I have a DB Source which is last weeks data then have a destination going into the Pending Table? Is that how I set it up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 11:54:52

quote:
Originally posted by JJ297

Don't I need that to put the info into the database?

On the Data Flow Tab I have a DB Source which is last weeks data then have a destination going into the Pending Table? Is that how I set it up?

yup so your data flow task will extract data from file onto sql table?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-16 : 12:13:35

Yes I plan to have a DB Source called LastWeekFile and then have a Destination called Pending File.

So It should flow as such...

On the Control Flow Tab:

1. Data Flow Task
2. Execute SQL Task (Truncate Pending Table) green arrow going into
3. Execute SQL Task (Insert into Pending Table) green arrow going into the Data Flow Task

On the Data Flow Tab:

1. DB Source (Lastweek Table)
2. Destination (Pending Table)

Is this the correct way to set it up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 13:15:51

quote:
Originally posted by JJ297

Yes I plan to have a DB Source called LastWeekFile and then have a Destination called Pending File.

So It should flow as such...

On the Control Flow Tab:

1. Data Flow Task
2. Execute SQL Task (Truncate Pending Table) green arrow going into
3. Execute SQL Task (Insert into Pending Table) green arrow going into the Data Flow Task

On the Data Flow Tab:

1. DB Source (Lastweek Table)
2. Destination (Pending Table)

Is this the correct way to set it up?

looks ok.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-16 : 13:20:44

Great thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources