SSIS Package Question

Question

SSIS Package Question

Davekahn

SSCommitted

Points: 1840
More actions
November 15, 2010 at 3:17 pm

#157316

Hi,
(Step I) I'm building a Package where I need to run Query to update a table say "mytable1"
Mytable is a tmp table
(Step 2) then I need to run a store procedure which will update certain records in "mytable"
(Step 3) : Send the data to network drive as a text file
Is it possible to design all this in SSIS package ? (I know yes)
I'm able to design Step I by Datasource
then I need to run store procedure which depends on the sucess of Step 1- how to do this step? because I still have to create the text file which is step3
Please let me know ASAP, thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

qew420 (11/15/2010)
Hi,
(Step I) I'm building a Package where I need to run Query to update a table say "mytable1"
Mytable is a tmp table

You're already in trouble. SSIS should not be working with #tmp tables outside of a *single* Execute T-SQL object. It does not guarantee a consistent batch.

(Step 2) then I need to run a store procedure which will update certain records in "mytable"

Using a staging table, instead of a #tmp, this will be easy.

(Step 3) : Send the data to network drive as a text file

You'd use the updated data and then ship it to a text file that you could adjust as a variable in scripts as to the exact name/location if you like.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

Craig has answered you question completely. As a matter of design...create table --> Stage Data-->Data Transformations-->Truncate Table

Raunak J

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Craig Farrell (11/15/2010)
You're already in trouble. SSIS should not be working with #tmp tables outside of a *single* Execute T-SQL object. It does not guarantee a consistent batch.

If you set the property RetainSameConnection to True of the corresponding connection manager, there should be no problem. (if all task/components that use the temp table have the same connection manager of course)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

da-zero (11/16/2010)
Craig Farrell (11/15/2010)
You're already in trouble. SSIS should not be working with #tmp tables outside of a *single* Execute T-SQL object. It does not guarantee a consistent batch.
If you set the property RetainSameConnection to True of the corresponding connection manager, there should be no problem. (if all task/components that use the temp table have the same connection manager of course)

:w00t: Whoopsies! Thanks for that correction Da-Zero.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Davekahn SSCommitted Points: 1840 More actions · Answer 5

Davekahn

SSCommitted

Points: 1840

November 18, 2010 at 11:25 am

#1252668

thanks all

I'll try out and will update you all