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


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

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

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

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:

 


View Complete Post


More Related Resource Links

Using Conditional Split data Transfer in SSIS 2008

  
This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

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

Using User Variables (ONLY) in SSIS Conditional Split

  
After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

  
Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

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.

 

 


SSIS Incremental Load

  

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

SSIS - Lookup Task (Duplicate Row)

  

Good day,

I'm having difficulty with a lookup task.

Firstly in my Lookup my query contains a "DISTINCT" and my cols match source as only some records fail, Source also has a distinct so no Duplicate there either.

I also added on error to display error records to a dataview. Now the lookup code does exist not sure why he is failing

The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Is the records locking each other as source & lookup read from same table with diff filter criteria to gether with (NOLOCK) specified.

Please Assist!


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

DateTime Validation in SSIS using conditional split

  

Hi,

I am facing issue with null validation for DateTime. I have input csv file which contains DateTime field as Date datatype. In the destination database table, I am storing this field as datetime. I am using conditional split feature of SSIS package and try to validate this field before inserting into the database.

The value which is going into the db is "1899-12-30 00:00:00.000". I dont know why?

I have tried various expressions like ISNULL(DateTime), LEN(DTSTR, DateTime) == 0, ISNULL(DateTime) ? False : DateTime. Nothing is working.

Req is simple, if date is valid insert in db otherwise write is as bad data in error log file.

Can anyone provide suggestion on this.

Thanks,

SandyLeo


SandyLeo

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

How To Store Task Start Time and Task End Time in log table using SSIS?

  

Hi All,

 

Present my log table storing each task functionality  Same log table in that  I want store every task start time and end time also. How to implement this in ssis?

 

Thanks

 

CMK..


SSIS Conditional Split

  

 i created a small DTS flow which is fetching data from OLD DB source to two  flat files using condition given in Conditional Split Transformation

 

Conditional Spilt has two condition according to source table column as given

Case1      Department == "IT"

Case2      Department == "Management"

Case3      Department == "Sales"

now as per this Case "IT", "Management" and "Sales" i am generating the flatfiles in three location C:\IT , C:\Management , C:\ Sales folder respectevly

 

this package is running fine but here the logical problem occurs, All the files are generating with accurate records in there respective folders but simultanuasly two other file also generating in all folders. which is not required,

it seems the condition which get satisfied executes and generate the file in there respective folders and fill the record in that file but other 2 case2 is also executing simultaneously and generating blank files

Assistance is highly appreciable.

Thanks in advance

 

 

 

 

 


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

Adding the table row below a particular row.

  

What is the best method in jQuery to add an additional row to a table as the row below a particular row?


Dynamic Checkboxlist exception when adding to Panel when using Master Page

  

In essence, I am creating Dynamic Checkboxlist controls and adding them to a Panel.  It works well when I do not have a Master Page configure to this webpage. Once I have a masterpage congifured.. I get a "Object Reference not set to instance of object" error on the Panel_Control.Controls.Add(Checkboxlistnew) line:


protected void Page_PreInit(object sender, EventArgs e)
    {
       
        XML OXML = new XML();
        
        alistChkBoxLabels = OXML.XMLTransform("C:\\Documents and Settings\..\\Config_new.xml", "config_options/check_box/applications/app/@name");
        int count = 1;
        alistCheckisFacility = new ArrayList();
        alIDTracker = new ArrayList();
        while (count <= alistChkBoxLabels.Count)
        {

            Checkboxlistnew = new CheckBoxList();
            Checkboxlistnew.ID = count.ToString();
            alIDTracker.Add(Checkboxlistnew.ID);
            Checkboxlistnew.Items.Add(new ListItem(alistChkBoxLabels[count - 1].ToString(), alistChkBoxLabels[count - 1].ToString()));

            String sAppName = alistChkBoxLabels[count - 1].ToString().Trim();

            
            alistCheckisFacility = OXML.XMLTransform("C:\\Documents and Settings\\Administrator\\My Documents\\Visual Studio 2005\\WebSites\\User_Trial\\Config_new
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