Home > SSIS > In-memory data processing – SSIS

In-memory data processing – SSIS


In-memory data processing is the modern data processing methodology where-in huge result-set is stored in main memory and perform data analysis. So, Microsoft introduce PowePivot with Vertipaq technology in BISM and in the programming language .Net has ADO.NET disconnected architecture to store data in memory and manipulate.

Today, we are going to learn, how to implement in-memory data manipulation in SSIS?

are you looking for Hekaton?

Recordset Destination

It is a data flow data destination task. It can be used to store data in memory using package variable. It uses ADO recordset in-memory feature.

Implementation

Step 1: Create a SSIS package and add a Data flow task.

Step 2: Open Data Flow task and add a Ado.Net source and Recordset destination task.

Step 3: Create a package variable with object data type. variable scope must be to the package.

Step 4: Configure Ado.net task with Adventureworks database. Query a table named Person.CountryRegion with top 5 rows.

Step 5: Configure RecordSet destination task with the package variable and select a column name in the input column tab.

Step 6: Add foreach loop container and connect data flow with it. Now, configure foreach loop container.

Step 7: So far, we have done the settings to load data to memory and iterate recordset with foreach loop container.

Now, I am going to add a variable name variable1 with object datatype and map this variable to the foreach loop variable mappings.

Step 8: To display each record, I am adding Script Task to Foreach loop container with simple vb.net code.

Step 9: Execute package.

(updated: 1/9/2013) You can also read about SQL Server In-Memory Technology (Hekaton) here.

Thanks for reading.

Advertisements
  1. October 28, 2011 at 8:48 am

    Rui Travassos • That is a good step-by-step document. But, in my opinion, you should have provided some further explanation detailing why this can be useful. (it is all about that for each loop container). 🙂

    This is ideal if you want to connect to different and distinct servers and connections or use files depending on the memory value, etc. etc..

    I have always found it very useful.

    Well done 🙂

  2. priyaks
    December 7, 2011 at 1:17 pm

    Can recordset destinations be used as temp table. Is that possible to manipulate the data in the recordset destination and then produce reports in a flat file based off of recordset destinations?

    • December 7, 2011 at 10:01 pm

      Recordset destination stores data in-memory with a package object variable. you can manipulate this data inside the SSIS.

  3. Dave
    September 14, 2012 at 3:03 am

    why do you only show retrieving one field. Why not be more realistic and show how after you’ve binded your object variable to your foreach loop how you’d reference several field names (not just 0) in your foreach. I have no idea how to reference a specific field name and in your example it’s nice but gets lame at the point where you output the ONLY field you’re grabbing from the recordset instead of showing us how to specifically reference fields by index or whatever then spit them out into a message box, not just spit out the index field of 0.

    So your example becomes useless in applicability in the real world because you know you’re gonna want to select or reference several fields from that recordset from within your foreach loop such as maybe you want to insert new records into another table using certain fields in each iteration of the loop. You gotta be more specific and realistic here. You stopped way too soon at just selecting and showing ONE field from your select statement and of course you then don’t have to reference it in your message box in terms of fieldname cause it’s the ONLY field you have in your recordset so naturally SSIS is gonna spit back that one field without telling SSIS the field name or index for that field. Lame. Give a better ending and example from within your foreach.

  4. September 14, 2012 at 11:02 am

    Dave
    Thanks for your comment

  1. March 3, 2012 at 12:31 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: