Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package Question Expand / Collapse
Author
Message
Posted Monday, November 15, 2010 3:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 45, Visits: 485
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


Post #1021125
Posted Monday, November 15, 2010 3:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1021132
Posted Tuesday, November 16, 2010 5:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 1,042, Visits: 1,829
Craig has answered you question completely. As a matter of design...create table --> Stage Data-->Data Transformations-->Truncate Table

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1021407
Posted Tuesday, November 16, 2010 10:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
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)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021665
Posted Tuesday, November 16, 2010 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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)


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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1021730
Posted Thursday, November 18, 2010 11:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 45, Visits: 485
thanks all
I'll try out and will update you all
Post #1023076
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse