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


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

SSIS Incremental Load

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

Hi SSIS Guru,

Our existing process pulls data from source (Internet) into our staging database nightly. We then have a big stored procedure to do all kind of insert, update and delete records into destination table. There are two big logic inside this stored procedure:

The stored procedure inserts data into temporary table (called #temp).

1. For existing records, update to relavant tables.

   Update sales.customer set active=1  From sales.customer a join #temp b a.customerID=b.customerID where a.active !=1

  Update sales.customer set active=0  From sales.customer a join #temp b a.customerID=b.customerID where a.active !=0

  INSERT into sales.customer (customerID,name,address)

  select a.customerid,a.name,a.address from #temp a join customer_main b on a.customerID=b.customerID

  AND NOT EXISTS (select 'something' from sales.customer c where a.customerID=b.customerID)

  We have five more tables that doing the dame update logic.

2. For new records, insert to relevant tables.

   Use CURSOR doing insert. Below is logic of inserting new customer:

   declare c cursor forward_only for select customerID from #temp a

   where NOT EXISTS(select 'something' from sales.Customer_Load b where


View Complete Post


More Related Resource Links

Incremental data load from MySQL database to SQL server 2008 using SSIS

  

I am looking for best solution to perform incremental data load from MySQL database to SQL Server 2008.  I tried to follow instructions from articles on incremental data load in SSIS  using MD5 hashes but I failed to make connection in Lookup transformation editor between MySQL Id (4 byte unsigned integer) and Id in SQl Server 2008 table which is  bigint. i tried to add  data conversion and convert MySQl Id to string. It works fine when you map this field directly to OLEDB destination but in Lookup component it failed. 

 

Any input greatly appriciated.

 

 


Adding exception table to incremental Load with SSIS Lookup task and conditional split

  

Hello,

I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.

 

Below is what I have put in the conditional transformation editor for my first output condition:

 

How to load a new table with the value of a variable from SSIS package?

  
Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

SSIS FOR each loop to load 1 file after other into database for particaular folder

  
HI I am using ssis 2008,i have folder in which i have 10 excel file.i wont to load them one after other in data  base I have created a Dataflow task that load one file into database.i have inserted this dataflow task into For each loop and in For each loop i have give that folder path and *.XLSX  and in variable mapping i have created one variable  which i have passed in Database destination connecion string (properties->expression) but still i get error "cannot acquire connection from connection manager" please guide me or provide me with some reference so that i cud load file from folder into database one after other

Difference between delta load and incremental load and full load

  
Difference between delta load and incremental load and full load

ssis how should i load data from excel to Xml destination

  

Hi

could any one tell me how could i load data from EXCEl file to XML
if some one could provide with appropriate link then it will b  a great help

i have  taken Excel source part is completed ..but what to do after that..

 


SSIS - load table from source text file with multiple record lengths

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Load multiple record length text file into table using SSIS

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Massive Incremental loads with ssis

  

Hi guys, I am very new with SSIS and I did some researches on google before stoping here to ask if there is a way to do a massive incremental load with the ssis? What I need to do is to update my development database with data from the production server. I found the incremental loads solution but there are too many tables for me to do that one by one, by hand.

I am asking for advices :)

Thank you


SSIS Import - incremental file import

  

Hi

Is it possible to import an incrementing file in SSIS using a different file name each day?
For example – on Monday I want to import “file1” on Tuesday I want to import “file” and so on.

This is so I can set a package up in SQL server to run everyday using a different file.

If not, are they are similar work arounds?

Thanks,

Ant

 


Incremental Load and the source archiving problem

  

Folks,

I will appreciate your feedback on the following challenge:

Context

  • A SQL Server transactional source system is being replicated to a shadow copy
  • Triggers were added on tables in the shadow copy to populate a Log Table with actions and timestamps (Added , Deleted , Updated)
  • The Log table is used for ETL to populate an Oracle DWH
Problem
  • The source system data will be archived and all rows is all tables will be deleted prior to certain date.
  • The archived data is still required to be maintained in the Oracle DWH
  • The replication between the source and shadow will populate the log table with lots of delete actions on data that in the DWH
  • The data in the DWH will be lost

How can we maintain the data in the DWH and prevent the data from being lost after the archiving ?


Essam Salah

how to load dimension table by using dimension processing in ssis

  
how to load dimension table into cube by using dimension proccesing destination in ssis

ssis deployment issue - Unable to load custom dll - due to change in Sql server installation

  

Hello all

I had a strange issue in testing my package, after it was deployed to a Test server.

The details of the server

Sql 2005 is installed, in the D:\ drive

I have used a package, where i use a custom DLL to read values. Hence, i added that DLL to the GAC. I was in the assumption that this should complete my proceess of deploying this package.

 

However once i executed the package, i got an error - Could not load the Helper object.

Unable to CAST "system.object"  to namaspace.class.Helper.

 

I reviewed the setting on my macihine, since as part of development inorder to reference the dll i added this to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies location so that i can reference this from BIDS. I checked that location in my test server, i was not present. This location was present in the D: drive ( i assume this was present since i installed my complete SQL Server in the D:\ ). I copied the dLL in that location and added it to GAC from that location. My package still failed with the same error.

 

To resolve this issue, i created the same path in the C:\ folder as C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

assemblies was newly created in C: and i added the dll to GAC from there. Now my package worked.

 

how to have full and incremental load in a single package

  

I have two separate packages one for full laod and the other for incremental load.

In full load I will be truncating the destination table and loading the data but in incremental I am using lookups for checking the record whether it is new or old record.

Is it possbile to have a single package so that if I select full load ,it should do full load or else it sholud do incremental skipping trunctaing task and perform the load operation.

 


Reduce Load Time of Cube Data using SSIS

  

Hi, I have an issue where I am trying to reduce the time it takes to load SSAS cube data to SQL table using SSIS.

Within a Data flow task I have an OEDB source where in I select "command from variable" where the variable holds the dynamic MDX. I then have a data conv, lookup, derived transform and OLEDB destination respectively.

However the load takes some 2.5 hours for inserting 1 million rows. Any pointers as to how I may reduce it?

I have around 110 columns returned from the source MDX and it has 1 million rows as I said.

the lookup is to get a key not present in cube. since the lookup table is huge, i am using partial cache and the derived column transform just adds a string (hard coded) value of "ETL" and a time using getdate(). Data conversion is for the obvious conv for ntext data (returned from MDX to corresponding datatypes in SQL table...I have the fast load option in the OLEDB destination.

I can't use openquery though. Let me know what may I do to reduce the load time.


SSIS Error 0x80070008 while preparing to load the package. Not enough storage is available to proces

  

Hello guys,

Have anyone seen this error message before in SSIS? Error 0x80070008 while preparing to load the package. Not enough storage is available to process this command.

Appreciate your help!


SSIS - Script Task Error - Could not load file or assembly

  
Hello

I have script task that has references added to it but still giving me above error? How should i resolve this situation?


Thanks

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