January 18, 2011 at 11:50 am
Hello,
I am starting to use SSIS more and more. In fact, one of my individual development goals for the year is to get certified in Business Intelligence.
But for now, I use SSIS quite often to move a table from production to DEV.
1) Go into Visual Studio to move project.
2) Select OLE data flow source
3) Select OLE data flow destination
4) Create a appropriate connect managers
I drop the table and then select my source and destination and I can easily create a new table.
But I was wondering, how would I update a table. If I just want to truncate the table, what would be the steps to update a table?
The above information is for one server to another. SSIS works well since we are not allowed by company policy to create linked servers.
Thank you.
Things will work out. Get back up, change some parameters and recode.
January 18, 2011 at 12:48 pm
For truncating use an Execute SQL task in the main flow.
For updating (especially UpSert) use the Slowly changing Dimension component. It's a wizard that will handle your update/inserts easily.
For selective updating or significant logic, I recommend dumping to a staging table and then running procs to load from staging to primary tables.
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
January 20, 2011 at 12:37 am
Craig Farrell (1/18/2011)
For truncating use an Execute SQL task in the main flow.For updating (especially UpSert) use the Slowly changing Dimension component. It's a wizard that will handle your update/inserts easily.
For selective updating or significant logic, I recommend dumping to a staging table and then running procs to load from staging to primary tables.
I'd like to add that the list option of Craig will always outperform the Slowly Changing Dimension component.
To update rows, you can use the TSQL UPDATE statement in an Execute SQL Task.
Good luck on your preparation for the BI exam!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply