.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Staging vs Temp tables in SSIS

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 
Hi SSIS Guru,

Currently, we have our own internal app to pull data from the internet nightly to serverA. On serverA, there is a huge stored precedure doing update, insert and delete by joining lots of tables on serverB via synonyms over LINKED SERVER. However, there are only 3-4 tables on serverA used to join tables on serverB.

I am rewritting this process to eliminate linked server and use SSIS instead of stored procedure. What is the best option to bring tables from serverA to serverB? Should I create staging tables on serverB and refresh tables with TRUNCATE or use temp table instead?

Thanks much,
Staging vs Temp tables in SSIS
KongDBA



View Complete Post


More Related Resource Links

Looping through Excel 2007 tables in SSIS?

  
Saw the (http://msdn.microsoft.com/en-us/library/ms345182(v=SQL.90).aspx) msdn article on MSDN, however I need to loop through Excel 2007 tables, which is not supported via the .Net Connection.  Is there a way to loop through the 2007 sheets without resorting to a script component?Steve Walker

How to create a SSIS package to import records form SQl Server 2008 tables to text files

  
I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

extracting from multiple tables in an odbc call/ssis

  
Hi,   I have an odbc for DBISAM, and can use it to import into sql server, but only one table at a time.  (when it get's to the choose table screen, it will only let me create a single SQL query.   does anyone know if I can extract from multiple tables using this?   If not - what would I need to change in the DTSX file so that my query goes from "select * from tablea" to "select * from tableb" and the destination table is also "tableb"?   also - can the DTSX file be change to run for multiple tables?

Import a flat file with combined data into separate SQL tables using SSIS

  
I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

SSIS from MS SQL Server 05 to Oracle 10g 250 Tables

  
Hi Guys, I want to write a neat SSIS to automate the monthly load of 250 MS Sql Server Tables to the Oracle 10g database. Now, I know how it works but I dont want to create 250 data export/import steps. Is there a that the SSIS captures the schema, does the data type conversion and dumping automatically? I know Oracle SQL Developer does this whole "schema capturing" but it lacks automation. Thanks, metalray

creating temp tables!

  
hi friends, 1. Is the tempdb in teh SQL server (2005) is a global database that is shared among other user defined databases? 2. Is is possible to create User defined temp databases like tempdb and how? i have create a temp table create table #usr_with_rls (idx int primary key, usr_name nvarchar(50), f_name nvarchar(50), l_name nvarchar(50), desig nvarchar(50)) and it was created in the tempdb database.  but the sp that creates the this table is in another database, therefore 3. How do i create this temp table "urs_with_rls" in my datbase and not in the tempdb? 4. I need to remove this temp datanase "urs_with_rls" from the server, onces teh sp completes it execution, how do i do that? thanks    

Is there any Data Flow Task in SSIS to merge 3 different recordsets (from 3 different tables) and se

  
I have a requirement, that has 3 different Execute SQL Task that returns 3 different recordsets which differ from each other.  I want to know whether these 3 recordsets can be sent as Input to a Script Component.  I know that Script Component accepts only 1 Input... But is there any way, to have this done

how to use? ssis to transfer tables(from two db)

  

Hi I have to transfer 12 tables from say databse 'a' and 10 tables from database 'b' using ssis package   to database 'c' and i have to rename the all the table in database 'c' with '_bak' at the end of all table's.Table name are same i.e

databse 'a' have table's

1. transaction 2. suplyer etc   and

databse 'b' have table

1. deliverd 2. status 

 

then database 'c'  already have those table from 'a' and 'b' 

now they have to rename by _bak and copy againg those table from database 'a' and databse 'b' to database 'c'


Importing multiple files to multiple tables in SSIS

  

I have a directory with 200+ txt files to import into SQL tables in a database. Each file name is the exact table name in the database(without the file extension, obviously). I am looping through each file with a for each loop and a variable is mapped and set in the source connection properties for the Expressions -> ConnectionString property, so each name will go into that variable without the file extensions, correct?  Now, I set the variable name to the table name in the destination for the table name under "Data Access Mode", but it is giving an error...do I have to assign variables to each part, (Connection String and Name)? Does anyone have a quick setup for this?

 

Thanks!


use temp tables instead of CTE's

  

I need to re write this query with CTE's to reflect differences in DNC ($$) between my @mindt and @maxdt

do I need to show a DNC @ MIN Date and DNC @ Max DT so i can calculate the differerence...can you help?

declare @mindt datetime
declare @maxdt datetime

set @maxdt = getdate()
set @mindt = getdate()-14;




With itmlvl as (


SELECT
r.EM_ITEM_NUM
,r.DSCR
,r.RBT_TYP_ID
,rb.AMT
,rb.EFF_DT
,rb.END_DT


FROM GEPRS_DNC..T_REBATE r
JOIN GEPRS_DNC..T_REBATE_PERIOD rb 
ON rb.REBATE_ID = r.REBATE_ID

)
,
--GTR, SSR: from T_REBATE, T_REBATE_PERIOD, and V_ITEM_SPLR
 GTR_SSR AS (
select RB.RBT_TYP_ID
	, rb.DSCR, rp.AMT
	, rp.EFF_DT, RP.END_DT
	, splr.EM_ITEM_NUM

from geprs_dnc..t_rebate rb 
		join

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

  
Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]
blog.ganeshzone.net

how to use? ssis to transfer tables(from two db)

  

Hi I have to transfer 12 tables from say databse 'a' and 10 tables from database 'b' using ssis package   to database 'c' and i have to rename the all the table in database 'c' with '_bak' at the end of all table's.Table name are same i.e

databse 'a' have table's

1. transaction 2. suplyer etc   and

databse 'b' have table

1. deliverd 2. status 

 

then database 'c'  already have those table from 'a' and 'b' 

now they have to rename by _bak and copy againg those table from database 'a' and databse 'b' to database 'c'


temp tables!

  

hi friend,

assume i have created a temp table, #t1 and after completion of query execution in the sp i can delete it from db.

in multi user enviorement, each user who acess that sp creates a temp table, i know teml tables has a structre #_______________<number>  so when each user access this sp, does it create a seperate temp table for each call?

thanks

 


SSIS - XML Source to multiple SQL tables(Master-Detail)

  

Hi,

I've a scenario in which i need to map my xml file to multiple sql server tables, maintaining Referential Integrity. 

Below is my sample xml structure

<Header>

 <id>IDENTITY COLUMN</id>

 <Hdr_col1>xxx</Hdr_col1>

</Header>

<Detail1>

 <id>IDENTITY COLUMN</id>

 <Hdr_ID>ID of the above record</Hdr_ID>

</Detail1>

<Detail2>

 <id>IDENTITY COLUMN</id>

 <Hdr_ID>ID of the above record</Hdr_ID>

</Detail2>

as u can see above, 1 parent record has 2 child records. So i need to save the parent record first, based on the ID which is generated automatically, i need to save my child records into the Detail table. I've searched everywhere in google, but couldn't get a solution yet.

(I had done this sort of thing in vb.net but now my job is to do it using SSIS)   :(

Can you gurus help me in achieving my task.

Thanks in advance.

 


How to take the scripts of the changed SPs/Views/Temp tables/Functions automatically?

  

Hi,

  We have a huge Database with more than 100 tables , more than 150 SPs.We are 10 members and we will be creating/deleting/updating SPs , functions, views, tem tables  everyday. Actually at the end of the day, we have to send the  scripts of latest SPs,functions,views,Temp tables to the server. So far what we are doing is  we manually go to the SQL server Scripts Wizard and selecting one by one all the SPs,functions,TTables and views. Is there any quicker way to take the changed stuffs instead going manually?


SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran

  

Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.

 

thanks

 


Union all in SSIS join two tables row wise or column wise

  
Union all in SSIS join two tables row wise or column wise
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend