Question on getting data out...

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)

Question on getting data out...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 09:19:09

Thanks Peso for helping me with my stored procedure:

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

How do I set this up in SSIS? Do I put this stored procedure in an Execute SQL Task on the Control Flow Tab? What would go on the Data Flow Tab along with the destination?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:28:13

Yup either put query istself in Execute SQL task or wrap this up in a stored procedure and use EXEC Spname in your Execute SQL task.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 09:40:49

Okay thanks but how do I added the results to a new table called Cleared?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:43:58

quote:
Originally posted by JJ297

Okay thanks but how do I added the results to a new table called Cleared?

just use

INSERT INTO Cleared (field...)
yourselectquery

if inside data flow tab you can use OLEDB command task for this.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 09:51:02

I do want to use it inside my Data Flow because I have too many field names to write.

So I would put this in the Execute SQL Task

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

Then what items from the toolbox do I put on the dataflow tab along with the destination. I'm confused.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:53:10

quote:
Originally posted by JJ297

I do want to use it inside my Data Flow because I have too many field names to write.

So I would put this in the Execute SQL Task

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

Then what items from the toolbox do I put on the dataflow tab along with the destination. I'm confused.

Can you give some background info sequence of operations you're trying to perform in package?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:10:23

[code]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[/code]

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 10:18:43

On the Control Flow I have

1. Data Flow Task
2. Execute SQL TASK (I listed all of the fields)

Insert into Cleared (RGN, AREA, DIST, DOC, [OP-JURIS], TSC, HUN, RECNO, PAN, FNAME, LNAME, [PERSON-TYPE], [REDET-IND], [OVERPAY-IND], [OVERPAY-AMT],CURSTAT, APPEAL, GKC, REDLOWPRO, PROFILE, REDTYP, [GK-STATUS], ARC, OPST, OPSC, OPSND, OPSNT, FILLER, [IN-CT], DryCd1, DryDte1, DryFu1, DryCd2, DryDte2, DryFu2, DryCd3, DryDte3, DryCu3, DryCd4, DryDte4, DryFu4, DryCd5, DryDte5, DryFu5, DryCd6, DryDte6, DryFu6, DryCd7, DryDte7, DryFu7, DryCd8, DryDte8, DryFu8, DryCd9, DryDte9, DryFu9, DryCd10, DryDte10, DryFu10, DryCd11, DryDte11, DryFu11, DryCd12, DryDte12, DryFu12, DryCd13, DryDte13, DryFu13, DryCd14, DryDte14, DryFu14, DryCd15, DryDte15, DryFu15, DryCd16, DryDte16, DryFu16, Out_DryDte1, Out_DryDte2, Out_DryDte3, Out_DryDte4, Out_DryDte5, Out_DryDte6, Out_DryDte7, Out_DryDte8)

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

I have the green arrow going from Data Flow Task to the Execute SQL Task. When I run it I get this error:

[DTS.Pipeline] Warning: The DataFlow task has no components. Add components or remove the task.

I don't know what I need to put on the Data Flow Task as I have nothing there.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 10:23:14

Perfect Peso that did the job. I thought I had to have a items on the Data Flow Tab too. Thanks visakh16 for your help too. Until again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:41:53

quote:
Originally posted by JJ297

On the Control Flow I have

1. Data Flow Task
2. Execute SQL TASK (I listed all of the fields)

Insert into Cleared (RGN, AREA, DIST, DOC, [OP-JURIS], TSC, HUN, RECNO, PAN, FNAME, LNAME, [PERSON-TYPE], [REDET-IND], [OVERPAY-IND], [OVERPAY-AMT],CURSTAT, APPEAL, GKC, REDLOWPRO, PROFILE, REDTYP, [GK-STATUS], ARC, OPST, OPSC, OPSND, OPSNT, FILLER, [IN-CT], DryCd1, DryDte1, DryFu1, DryCd2, DryDte2, DryFu2, DryCd3, DryDte3, DryCu3, DryCd4, DryDte4, DryFu4, DryCd5, DryDte5, DryFu5, DryCd6, DryDte6, DryFu6, DryCd7, DryDte7, DryFu7, DryCd8, DryDte8, DryFu8, DryCd9, DryDte9, DryFu9, DryCd10, DryDte10, DryFu10, DryCd11, DryDte11, DryFu11, DryCd12, DryDte12, DryFu12, DryCd13, DryDte13, DryFu13, DryCd14, DryDte14, DryFu14, DryCd15, DryDte15, DryFu15, DryCd16, DryDte16, DryFu16, Out_DryDte1, Out_DryDte2, Out_DryDte3, Out_DryDte4, Out_DryDte5, Out_DryDte6, Out_DryDte7, Out_DryDte8)

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

I have the green arrow going from Data Flow Task to the Execute SQL Task. When I run it I get this error:

[DTS.Pipeline] Warning: The DataFlow task has no components. Add components or remove the task.

I don't know what I need to put on the Data Flow Task as I have nothing there.

as error suggest you dont have any components used in data flow task. then why use it at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:49:08

quote:
Originally posted by JJ297

Perfect Peso that did the job. I thought I had to have a items on the Data Flow Tab too. Thanks visakh16 for your help too. Until again...

No problem is using Pesos solution. But make sure you drop table before you start over package tasks each time. else it will error for subsequent runs of package since again tries to create a table which already exists. thats why i suggested you to use INSERT...SELECT.SELECT..INTO is ok when you're using it for one time.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-26 : 11:02:10

Thanks visakh16

I will add an Execute SQL Task to drop the table.

Thanks again for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources