Showing posts with label SCRIPT COMPONENT. Show all posts
Showing posts with label SCRIPT COMPONENT. Show all posts
Case
I want to calculate a hash value for a couple of columns in SSIS. In T-SQL you can use HASHBYTES, but that doesn't work for other sources like flat files and for SQL 2012/2014 the input is limited to only 8000 bytes. Is there an alternative for HASHBYTES?
Calculating a hash value over multiple columns















Solution
There are several alternatives for the T-SQL HASHBYTES. First of all there are various custom components available for SSIS like the SSIS Multiple Hash on codeplex, but if you don't want to (or cannot) use custom components, you can accomplish the same result with a little .NET scripting. If you really want to stick to T-SQL, then you can also first stage your files in a table and then calculate the hash with T-SQL afterwards. This blog will show you the scripting solution.

But first, why do you need a hash? When you want to keep track of history with a Persistent stage, Data Vault or Data Warehouse you want to know whether the record from the stage layer is different then the one you have in your historical data layer. You could check each column one by one, but when you have a whole bunch of columns that could be a lot of work and a bit slow.

A hash in ETL is used to generate a single, corresponding (but shorter) value for a whole bunch of columns. It is stored in the stage table as a new column. If one character changes in one of those columns then the hash value will also be different. When comparing the two records (one from the stage layer and one from the historical layer) you now only have to compare the hash value. If it did not change you know you don't have to process the record in your historical layer. Since you only want to calculate the hash once (in the stage package) you will also store it in the historical layer.

Now it is time to explain the scripting solution

1) Starting point
The starting point of this example is a Data Flow Task with a Flat File source component.
Flat File Source












2) Script Component - Input Columns
Add a new Script Component (transformation) to the Data Flow Task. Give it a suitable name and connect it to your flow. Then edit it and select all columns you want to hash on the Input Columns pane. Since we are not changing the existing column you can keep the default Usage Type 'ReadOnly'.
Script Component Input Columns

















Which columns do you want to hash? Three most chosen options:
  1. If you do not know the Primary Key: select all columns to calculate the hash.
  2. If you do know the Primary Key: select all columns except the Primary Key to calculate the hash.
  3. If the Primary Key consists of multiple columns you could even calculate a separate hash for the key columns only.
3) Script Component - Output Column
We need to store the calculated hash in a new column. Go to the Inputs and Outputs pane and add a new column in Output 0. The data type is string and the size depends on which hash algoritme you want to use. For this example we use the MD5 algoritme which returns a 128 bits hash. When you convert that to an ASCII string it would be a 32 character string (that only contains hexadecimal digits).
Script Component Inputs and Outputs























4) Script Component - The script preparation
Now we are almost ready to add the actual script. Go to the Script pane. Select your scripting language. This example will be in C#. Then hit the Edit Script... button to start the Vsta environment. This is a new instance of Visual Studio and will take a few moments to start.
Edit Script...























Optional:
I always start by removing all unnecessary methods and comments to keep the code clean. For this example we do not need the PreExecute and PostExecute methods and I do not want to keep the default help comments.
Clean up before start



















5) Script Component - The code
First we need to add two extra usings to shorten the code. Unfold the Namespaces region at the top and add the following usings:
using System.Security.Cryptography;
using System.Text;

Then Locate the method called Input0_ProcessInputRow and add a new GetMd5Hash method below this existing method (below its closing }). The new method is copied from this MSDN page. I only changed the encoding to Unicode (see note 1):
static string GetMd5Hash(MD5 md5Hash, string input)
{
    // Convert the input string to a byte array and compute the hash.
    byte[] data = md5Hash.ComputeHash(Encoding.Unicode.GetBytes(input));

    // Create a new Stringbuilder to collect the bytes
    // and create a string.
    StringBuilder sBuilder = new StringBuilder();

    // Loop through each byte of the hashed data
    // and format each one as a hexadecimal string.
    for (int i = 0; i < data.Length; i++)
    {
        sBuilder.Append(data[i].ToString("x2"));
    }

    // Return the hexadecimal string.
    return sBuilder.ToString();
}


And at last change the code of the existing method Input0_ProcessInputRow to:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string Separator = "|";
    string RowData = "";

    using (MD5 md5Hash = MD5.Create())
    {
        Row.Hash = GetMd5Hash(md5Hash, RowData = (Row.Title_IsNull ? "" : Row.Title.ToString()) + Separator + (Row.FirstName_IsNull ? "" : Row.FirstName.ToString()) + Separator + (Row.MiddleName_IsNull ? "" : Row.MiddleName.ToString()) + Separator + (Row.LastName_IsNull ? "" : Row.LastName.ToString()));
    }
}


The code above first concatenates all columns with a separator between them (see note 2) and it checks whether the value isn't NULL because we cannot add NULL to a string (see note 3). You will see that it repeats this piece of code for each column before calling the hash method:
(Row.Title_IsNull ? "" : Row.Title.ToString()) + Separator
For the first record in our example it will hash the following text: Mr.|Syed|E|Abbas
And for the third row that contains a null value it will hash the this text: Ms.|Kim||Abercrombie

6) Testing the code
After closing the Vsta editor and clicking OK in the Script Component to close it, add a dummy Derived Column behind it and add a Data Viewer to see the result.
Similar result to T-SQL HASHBYTES


















Note 1:
When you want the exact same result as with T-SQL HASHBYTES then you have to make sure you use the same encoding. Otherwise you get a different hash. In the method GetMd5Hash on the first line of code you see Encoding.Unicode.GetBytes(. There are more options besides Unicode. For example: ASCII, UTF7, UTF8, UTF32, etc. etc. However, as long as you don't have to compare hashes generated by to different methods (T-SQL and .Net) it doesn't matter. In this stackoverflow post you find more examples.

Note 2:
The column separator is added to prevent unwanted matches. If you have these two records with two columns:
Column1Column2
123456
123456
Without the separator these two will both get concatenated to 123456 and therefor generate the same hash. With the separator you will have two different values to hash: 123|456 and 12|3456. Choose your separator wisely. The number 3 would not be a wise choice in this case.

Note 3:
In the code you see that the columns are checked for null values because you cannot add null to a string. The null values are replace with an empty string. However this shows a bit of an imperfection of this method, because a string with a null value isn't the same as an empty string. To overcome this you could use a different string that is likely to occur in your text. For Numeric and Date data types you could just add an empty string, something like:
(Row.MyNumberColumn_IsNull ? "" : Row.MyNumberColumn.ToString()) + Separator
(Row.MyDateColumn_IsNull ? "" : Row.MyDateColumn.ToString()) + Separator


Note 4:
md5 only uses 128 bits and there are better, saver (, but also a bit slower) methods to calculate hashes:
SHA and SHA1 - 160 bits
SHA2_256 - 256 bits
SHA2_512 - 512 bits

Safer? As long as you don't use it to hash passwords you are still OK with md5.
Better? In rare cases two different strings could return the same md5 hash, but you have a higher chance to win the galaxy lottery.

Rather use SHA2_512? Just use this code instead:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string Separator = "|";
    string RowData = "";

    using (SHA512 shaHash = new SHA512Managed())
    {
        Row.hash2 = GetShaHash(shaHash, RowData = (Row.Title_IsNull ? "" : Row.Title.ToString()) + Separator + (Row.FirstName_IsNull ? "" : Row.FirstName.ToString()) + Separator + (Row.MiddleName_IsNull ? "" : Row.MiddleName.ToString()) + Separator + (Row.LastName_IsNull ? "" : Row.LastName.ToString()));
    }
}

static string GetShaHash(SHA512 shaHash, string input)
{
    // Convert the input string to a byte array and compute the hash.
    byte[] data = shaHash.ComputeHash(Encoding.Unicode.GetBytes(input));

    // Create a new Stringbuilder to collect the bytes
    // and create a string.
    StringBuilder sBuilder = new StringBuilder();

    // Loop through each byte of the hashed data
    // and format each one as a hexadecimal string.
    for (int i = 0; i < data.Length; i++)
    {
        sBuilder.Append(data[i].ToString("x2"));
    }

    // Return the hexadecimal string.
    return sBuilder.ToString();
}

Too much columns => too much coding?
In my book Extending SSIS with .NET Scripting you will find a script component example that just loops through all columns to generates the hash. No money to buy it? I used this code as the base for that script.
An other alternative is to generate the Script Component and its code with BIML. Here is an example of a Script Component in BIML, but getting the hash to work is a bit of a challenge but doable.
Case
I want to use the users from my Active Directory in my Data Warehouse for an Employee Dimension. How can I accomplish that in SSIS?














Solution
With a little .Net scripting in a Script Component you can accomplish an Active Directory source in your Data Flow Task. Before you start you need to figure out which fields are available in your Active Directory. If you don't know the fieldnames you could set a breakpoint on the foreach loop and add a watch on the result variable. Then you could browse through the properties of that variable to find all available fields.

1) Script Component Source
Create a Data Flow Task and add a Script Component and choose "Source" as the type. After that give you source a suitable name like "SRC_SCR - Active Directory Users".
Script Component - Source





















2) Output columns
Next edit the Script Component and go to the Inputs and Outputs pane. This is where we need to specify all the new output columns and datatypes. For my Active Directory example I have added 5 string (DT_STR) columns: Fullname, Firstname, Surename, Department and Manager. You have to figure out the required length. You could start with the default 50 or change it to a more save 255.
Add new output columns

























3) Edit script
Go back to the first page and choose C# as your scripting language and then hit the Edit Script buton to open the vsta environment.
Edit script

























4) Reference
To tell our script about Active Directory, we first need to add a new reference to the .NET assembly System.DirectoryServices. In the Solution Explorer right click References and
 choose Add Reference... Then scroll down, select System.DirectoryServices and click OK to confirm.
Add reference

















5) The Script - Namespaces
Under namespaces add a new using for our newly referenced assembly: using System.DirectoryServices; This allows you to shorten the code.
Add using



















6) The Script - CreateNewOutputRows
Now replace your CreateNewOutputRows method with the one below and add the GetPropertyValue method from below within your class tags under the existing methods. You could remove the PostExecute and PreExecute methods since we don't need them in this example.

The CreateNewOutputRows method adds new rows to the buffer by calling Output0Buffer.AddRow(); within a loop construction and then it fills all columns with data from the Active Directory. Before the foreach loop you find the code to connect to the Active Directory with some optional filter.
public override void CreateNewOutputRows()
{
    // Specify the connnectionstring of your domain
    // @mycompany.com => LDAP://DC=mycompany,dc=com
    // Consider using a variable or parameter instead
    // of this hardcoded value. On the other hand
    // how many times does your domain changes
    string domainConnectionString = "LDAP://DC=ilionx,dc=com";

    using (DirectorySearcher ds = new DirectorySearcher(new DirectoryEntry(domainConnectionString)))
    {
        ds.Filter = "(&" +
                    "(objectClass=user)" +  // Only users and not groups
                    "(department=*)" +      // All departments
                    "(givenname=j*)" +      // Firstname starts with j
                    ")";
        // See ds. for more options like PageSize.
        //ds.PageSize = 1000;

        // Find all persons matching your filter
        using (SearchResultCollection results = ds.FindAll())
        {
            // Loop through all rows of the search results
            foreach (SearchResult result in results)
            {
                // Add a new row to the buffer
                Output0Buffer.AddRow();
                // Fill all columns with the value from the Active Directory
                Output0Buffer.Fullname = GetPropertyValue(result, "cn");
                Output0Buffer.Surename = GetPropertyValue(result, "sn");
                Output0Buffer.Department = GetPropertyValue(result, "department");
                Output0Buffer.Manager = GetPropertyValue(result, "manager");
                Output0Buffer.Firstname = GetPropertyValue(result, "givenname");
            }
        }
    }
}

// Extra method to avoid having an if construction around each column
// It checks whether it can find a value. If not it returns an empty string.
private static string GetPropertyValue(SearchResult Results, string Property)
{
    // Null value results in count zero
    if (Results.Properties[Property].Count > 0)
    {
        return Results.Properties[Property][0].ToString();
    }
    else
    {
        return "";
    }
}


7) The result
To test the result add a dummy Derived Column and a Data Viewer to see the values. If it works you can add the rest of the components to the Data Flow Task to fill your dimension table.
The result of my script
















Summary
A very simple and short script to get data from your Active Directory. For more filter examples visit
MSDN. For very large Active Directories you have to play with the PageSize property.
Note that there are also scriptless options like with an ADO.Net source or with Third Party components. Each solution has its own pros and cons.




Case
For a client I need to read hundreds of bus route matrices and they all vary in size. This makes it hard to read them dynamically with a Foreach Loop Container because the number of columns differs per file. And I don't want to create hundreds of Data Flow Tasks by hand. Even BIML won't help this time, because the routes change regularly and I don't want to generate and deploy packages every day.
I need to dynamically unpivot data within the Data Flow Task. How do I solve this within SSIS?
Dynamically unpivot data



















Solution
The trick for this case is to read everything as one big column and then dynamically split and unpivot the column in a Script Component Transformation. The unpivot output will always have three columns: Start Station, End Station and Distance. And the good news is that it has only a few lines of relatively easy code.
The solution


























1) Source with one big column
Change your Flat File Connection Manager so that it will read everything as one big column. Make sure the column is big enough to fit all data. For this example I called the column 'ColumnOne'.
Flat File with one column only














2) Script Component Transformation Input
Drag a Script Component on the surface and choose Transformation. Connect it to your source. Then edit the Script Component  and go to the 'Input Columns' page. On that page select the column with all the matrix data as ReadOnly.
Input Columns
























3) Script Component Transformation Input
On the 'Inputs and Outputs' page we need to add the new output columns. For this example I need a StartStation (string), EndStation (string) and the Distance (int).
An other important step is setting the SynchronousInputID property (of Output 0) to 'None'. This makes the transformation asynchronous which means the number of row in could be unequal to the number of rows out. And that means the input buffer with records isn't reused in this component, but a new output buffer will be created.
Inputs and Outputs
























4) The script
Go to the script page, choose C# as scripting language and hit the Edit Script button. And now copy the contents of my Input0_ProcessInputRow method to your Input0_ProcessInputRow method. And there are also two variables called Stations and Distances. They are declared above this method. Copy those to your code and put them on the same place.
I also remove the unused methods PreExecute, PostExecute and CreateNewOutputRows to keep the code clean and mean.
#C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

/// <summary>
/// Split and unpivot data
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Define two arrays for distances and stations
    // The Stations array will be filled only once
    // The Distances array will change for each row
    string[] Stations;
    string[] Distances;

    /// <summary>
    /// This method is called once for every row that passes through the component from Input0.
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // The first time this method executes the Stations array
        // is still empty (null). In the true clause of the if-
        // statement we will fill the Stations array.
        // Therefore the second, third, etc. time this method
        // executes we will go to the false clause of the if-
        // statement.
        if (Stations == null)
        {
            // We know that the first row contains the stations.
            // We will add those to the stations array and use
            // it to determine the end station later on.

            // Split the string from ColumnOne on ; (or your own
            // column separator). The Split returns an array.
            Stations = Row.ColumnOne.Split(';');
        }
        else
        {
            // Now the rows will contain distances (and the StartStation)
            // Split the distances on ; (or your own column separator)
            Distances = Row.ColumnOne.Split(';');

            // Now loop through distances array, but start on 1 (not on 0)
            // because 0 contains the StartStation in the distances array
            for (int counter = 1; counter < Distances.Length; counter++)
            {
                // Add new Row and then fill the columns
                Output0Buffer.AddRow();
                // Get the Distance from the Distance array and convert it to int
                Output0Buffer.Distance = Convert.ToInt32(Distances[counter]);
                // Get the Start station from the distance array (the first item)
                Output0Buffer.StartStation = Distances[0];
                // Get the End station from stations array
                Output0Buffer.EndStation = Stations[counter];
            }
        }
    }
}

4) The result
Now close the Script Component and add more transformations or a destination and see what the Script Component does with your data. I added a dummy Derived Column and Data Viewer to see the data before and after the Script Component. For this file I had 27 rows and columns as input and 676 rows as output (26 * 26).



Case
In SQL 2016 CTP 2.3 Microsoft introduced a new simple way with to get the name of the column causing the error with some .NET code in a Script Component. In the final release this code doesn't work.

Solution
Not sure why, but they changed the code. Instead of one line we now need two lines. Below the complete example with the new code.


1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

























2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".
Add Script Component Transformation


















3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.
Input Columns

























4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.
Output Columns

























5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following three lines of code to it.
// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    // componentMetaData (starting with a lowercase "c") is just a name.
    // You can change that name if you like, but also change it in the
    // second row.
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    ' componentMetaData (starting with a lowercase "c") Is just a name.
    ' You can change that name if you Like, but also change it in the
    ' second row.
    Dim componentMetaData As IDTSComponentMetaData130 = TryCast(Me.ComponentMetaData, IDTSComponentMetaData130)
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn)
End Sub


6) Testing
Close the VSTA environment to save the code and press OK the close editor. Now add a Data Viewer behind the Script Component Transformation to see the results
Error Description and ColumnName

Case
I have a sensitive parameter in my package with a password in it. I want to use it in a Script Task, but when I try that it throws an error: Exception has been thrown by the target of an invocation. Can I use a sensitive parameter in a Script Task or Component?

Exception has been thrown by the target of an invocation.

















Solution
Yes you can read sensitive package and project parameters in the Script Task, but with a minor change in the code.

1) Lock for read
First open the Script Task editor and add the parameter to the ReadOnlyVariables field to lock it for read in the script.
ReadOnlyVariables























2) The Script
Open the VSTA environment by clicking in the Edit Script button. In the Main method you have something like this at the moment:
// C# Code (incorrect)
public void Main()
{
    // Create string variable to store the parameter value
    string mySecretPassword = Dts.Variables["$Package::MySecretPassword"].Value.ToString();

    // Show the parameter value with a messagebox
    MessageBox.Show("Your secret password is " + mySecretPassword);

    // Close the Script Task with success
 Dts.TaskResult = (int)ScriptResults.Success;
}

Change the .Value in to .GetGetSensitiveValue() in order to retrieve the sensitive information. But from now on you are responsible for not leaking the sensitive information accidentally!

// C# Code (correct)
public void Main()
{
    // Create string variable to store the parameter value
    string mySecretPassword = Dts.Variables["$Package::MySecretPassword"].GetSensitiveValue().ToString();

    // Show the parameter value with a messagebox
    MessageBox.Show("Your secret password is " + mySecretPassword);

    // Close the Script Task with success
 Dts.TaskResult = (int)ScriptResults.Success;
}
The Result
Now run the script to see the result.
Oops
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Note: This GetSensitiveValue method is not available in the Script Component. And when using the .Value you get an error: Accessing value of the parameter variable for the sensitive parameter "MySecretPassword" is not allowed. Verify that the variable is used properly and that it protects the sensitive information. A tricky/ugly workaround could be to use a Script Task to retrieve the sensitive parameter and to save it in a regular package variable and then use the variable in the Script Component (but be careful!).
It's not ready yet, but I'm proud to announce the first SSIS book by me and fellow MVP Régis Baccaro (B|T).
Extending SSIS with .NET Scripting


Extending SSIS with .NET Scripting will be a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations will help you to develop your own scripting solutions, but this book will also show a broad arsenal of readymade and well documented scripting solutions for all common problems.

Feel free to contact us for ideas and suggestions. We will post status updates on twitter and our blogs.

It could be that the number of blogposts will slightly reduce the coming months due writing obligations (but only temporarily).
Case
I want to add a Script Component transformation to my bimlscript to add a rownumber functionality to my packages.

Solution
For this example I will continue with an existing BIML example. Note the target in this example is an OLE DB destination that supports an identity column. Use your own destination like Excel, Flat File or PDW that doesn't supports identity columns.
Script Component Transformation Rownumber


















Above the <packages>-tag we are adding a <ScriptProjects>-tag where we define the Script Component code, including references, variables, input columns and output columns. In the <Transformations>-tag (Data Flow Task) we only reference to this Script Project.

The script code within the BIML script is aligned to the left to get a neat Script Component script layout. Otherwise you get a lot of ugly white space.


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Annotations>
  <Annotation>
   File: Script Component Transformation RowNumber.biml
   Description: Example of using the Script Component as
   a transformation to add a rownumber to the destination.
   Note: Example has an OLE DB Destination that supports
   an identity column. Use your own Flat File, Excel or
   PDW destination that doesn't supports an identity.
   VS2012 BIDS Helper 1.6.6.0
   By Joost van Rossum http://server.hoit.asia
  </Annotation>
 </Annotations>

 <!--Package connection managers-->
    <Connections>
            <OleDbConnection
                Name="Source"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostS;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
            <OleDbConnection
                Name="Destination"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostD;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
       </Connections>
 
       <ScriptProjects>
             <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SCR - Rownumber">
                    <AssemblyReferences>
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
                           <AssemblyReference AssemblyPath="System.dll" />
                           <AssemblyReference AssemblyPath="System.AddIn.dll" />
                           <AssemblyReference AssemblyPath="System.Data.dll" />
                           <AssemblyReference AssemblyPath="System.Xml.dll" />
                    </AssemblyReferences>
                    <ReadOnlyVariables>
                           <Variable VariableName="maxrownumber" Namespace="User" DataType="Int32"></Variable>
                    </ReadOnlyVariables>
                    <Files>
       <!-- Left alignment of .Net script to get a neat layout in package-->
                           <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
 
//
// General Information about an assembly is controlled through the following 
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("SSISJoost")]
[assembly: AssemblyProduct("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyCopyright("Copyright @ SSISJoost 2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version 
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Revision and Build Numbers 
// by using the '*' as shown below:
 
[assembly: AssemblyVersion("1.0.*")]
                           </File>
       <!-- Replaced greater/less than by &gt; and &lt; -->
                           <File Path="main.cs">#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
 
/// &lt;summary&gt;
/// Rownumber transformation to create an identity column
/// &lt;/summary&gt;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
 int rownumber = 0;
 
 /// &lt;summary&gt;
 /// Get max rownumber from variable
 /// &lt;/summary&gt;
 public override void PreExecute()
 {
  rownumber = this.Variables.maxrownumber;
 }
  
 /// &lt;summary&gt;
 /// Increase rownumber and fill rownumber column
 /// &lt;/summary&gt;
 /// &lt;param name="Row"&gt;The row that is currently passing through the component&lt;/param&gt;
 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
  rownumber++;
  Row.rownumber = rownumber;
 }
}
                           </File>
                    </Files>
                    <InputBuffer Name="Input0">
                           <Columns>
                           </Columns>
                    </InputBuffer>
                    <OutputBuffers>
                           <OutputBuffer Name="Output0">
                                  <Columns>
                                        <Column Name="rownumber" DataType="Int32"></Column>
                                  </Columns> 
                           </OutputBuffer>
                    </OutputBuffers>
             </ScriptComponentProject>
       </ScriptProjects>
 
       <Packages>
             <!--A query to get all tables from a certain database and loop through that collection-->
             <# string sConn = @"Provider=SQLNCLI11.1;Server=.;Initial Catalog=ssisjoostS;Integrated Security=SSPI;";#>
             <# string sSQL  = "SELECT name as TableName FROM dbo.sysobjects where xtype = 'U' and category = 0 ORDER BY name";#>
             <# DataTable tblAllTables = ExternalDataAccess.GetDataTable(sConn,sSQL);#>
             <# foreach (DataRow row in tblAllTables.Rows) { #>
 
             <!--Create a package for each table and use the tablename in the packagename-->
             <Package ProtectionLevel="DontSaveSensitive" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" Name="ssisjoost_<#=row["TableName"]#>"> 
                    <Variables>
                           <Variable Name="maxrownumber" DataType="Int32">0</Variable>
                    </Variables>
             
                    <!--The tasks of my control flow: get max rownumber and a data flow task-->
                    <Tasks>
                    <!--Execute SQL Task to get max rownumber from destination-->
                    <ExecuteSQL
                           Name="SQL - Get max rownumber <#=row["TableName"]#>"
                           ConnectionName="Destination"
                           ResultSet="SingleRow">
                           <DirectInput>SELECT ISNULL(max([rownumber]),0) as maxrownumber FROM  <#=row["TableName"]#></DirectInput>
                           <Results> 
                           <Result Name="0" VariableName="User.maxrownumber" /> 
                           </Results> 
                    </ExecuteSQL>
 
                    <!--Data Flow Task to fill the destination table-->
                    <Dataflow Name="DFT - Process <#=row["TableName"]#>">
                    <!--Connect it to the preceding Execute SQL Task-->
                    <PrecedenceConstraints>
                           <Inputs>
                                  <Input OutputPathName="SQL - Get max rownumber <#=row["TableName"]#>.Output"></Input>
                           </Inputs>
                    </PrecedenceConstraints>
 
                    <Transformations>
                    <!--My source with dynamic, but ugly * which could be replace by some .NET/SQL code retrieving the columnnames-->
                    <OleDbSource Name="OLE_SRC - <#=row["TableName"]#>" ConnectionName="Source">
                           <DirectInput>SELECT * FROM <#=row["TableName"]#></DirectInput>
                    </OleDbSource>
 
                    <ScriptComponentTransformation Name="SCR - Rownumber">
                           <ScriptComponentProjectReference ScriptComponentProjectName="SCR - Rownumber" />
                    </ScriptComponentTransformation>
                                               
                    <!--My destination with no column mapping because all source columns exist in destination table-->                        
                    <OleDbDestination Name="OLE_DST - <#=row["TableName"]#>" ConnectionName="Destination">
                           <ExternalTableOutput Table="<#=row["TableName"]#>"></ExternalTableOutput>
                    </OleDbDestination>
                    </Transformations>
                    </Dataflow>
                    </Tasks>
             </Package>
             <# } #>
       </Packages>
       </Biml>
 
<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>

The result
After generating the package with the Script Component we have a neat script for adding the rownumber.
Row number script
Case
A while a go I did a post on how to insert, update or delete records in CRM (2013), but what if you want to (de)activate records instead of deleting them to preserve some history in CRM?

Solution
This is a continuation of a previous blog post. Please read it to get more inside details.

1) Start
To deactivate or activate records in CRM, you need the identity id (GUID) of that record. In SSIS you could full outer join your source with a CRM identity view and if the records exists in CRM, but not in your source then you can deactivate the record.


2a) Download CRM SDK
For this example I used CRM 2013 and I downloaded the free Microsoft Dynamics CRM 2013 Software Development Kit (SDK). Execute the downloaded file to extract all the files. We only need the Microsoft.Xrm.Sdk.dll and Microsoft.Crm.Sdk.Proxy.dll assemblies which can be found in the SDK\Bin folder.
SDK download















2b) Windows Identity Foundation
The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.

3) DLL to SSIS machine
To use the assemblies (DLL) from step 2a in SSIS, you need to add the DLL files to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the GAC on Win Server 2008 R2. You also need to copy them to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

4) Parameters
To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.
4 parameters, password is marked sensitive.







5) Script Component - Parameters
Add a Script Component (type destination) for the deactivate. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.

Add parameters as read only variables


























6) Script Component - Input Columns
Add the columns that you need as Input Columns. For the deactivation you only need the entity ID from CRM. This is the Technical Id (a guid) from the CRM entity that you want to update.

Input Columns for deativate























7) The Script - Add assembly
Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add four references (microsoft.crm.sdk.proxy.dll is for inactivation):
  • microsoft.xrm.sdk.dll (from the SSIS bin folder mentioned in step 3, use browse)
  • microsoft.crm.sdk.proxy.dll (from the SSIS bin folder mentioned in step 3, use browse)
  • System.Runtime.Serialization.dll (from .Net tab)
  • System.ServiceModel.dll (from .Net tab)
Right click references and choose add reference












    Now very important: press the Save All button to save the entire internal vsta project (including references)
    Save All













    8a) The Script - Deactivate
    Here is an C# example (for VB.Net use this translator) for deactivating an existing CRM account with SSIS 2012.
    // C# Code
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.Xrm.Sdk;                // Added
    using Microsoft.Xrm.Sdk.Client;         // Added
    using Microsoft.Crm.Sdk.Messages;       // Added
    using System.ServiceModel.Description;  // Added

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        // Webservice
        IOrganizationService organizationservice;

        // Variables for the CRM webservice credentials
        // You could also declare them in the PreExecute
        // if you don't use it anywhere else
        string CrmUrl = "";
        string CrmDomainName = "";
        string CrmUserName = "";
        string CrmPassWord = "";

        // This method is called once, before rows begin to be processed in the data flow.
        public override void PreExecute()
        {
            base.PreExecute();
            // Fill variables with values from project parameters
            CrmUrl = this.Variables.CrmWebservice.ToString();
            CrmDomainName = this.Variables.CrmDomain.ToString();
            CrmUserName = this.Variables.CrmUser.ToString();
            CrmPassWord = this.Variables.CrmPassword.ToString();

            // Connect to webservice with credentials
            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
            credentials.UserName.Password = CrmPassWord;
            organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
          }

        // This method is called once for every row that passes through the component from Input0.
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Create CRM request to (de)activate record
            SetStateRequest setStateRequest = new SetStateRequest();

            // Which entity/record should be (de)activate?
            // First part in the entityname, second
            // is the entity id from the CRM source.
            setStateRequest.EntityMoniker = new EntityReference("account", Row.myGuid);

            // Setting 'State' (0 – Active ; 1 – InActive)
            setStateRequest.State = new OptionSetValue(1);

            // Setting 'Status' (1 – Active ; 2 – InActive)
            setStateRequest.Status = new OptionSetValue(2);

            // Execute the request
            SetStateResponse response = (SetStateResponse)organizationservice.Execute(setStateRequest);
        }
    }


    8b) The Script - Adding an inactive account
    And you can also combine adding and deactivating. Here is an C# example (for VB.Net use this translator) for inserting an inactive account with SSIS 2012.
    // C# Code
    // This method is called once for every row that passes through the component from Input0.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create a Entity object of type 'account'
        Entity newAccount = new Entity("account");
        
        // fill crm fields. Note fieldnames are case sensitive!
        newAccount["name"] = Row.AccountName;
        newAccount["emailaddress1"] = Row.Email;
        newAccount["telephone1"] = Row.Phone;

        // Create account and store its Entity ID to deactivate the account
        Guid AccountGuid = organizationservice.Create(newAccount);

        // Create CRM request to (de)activate record
        SetStateRequest setStateRequest = new SetStateRequest();

        // Which entity/record should be (de)activate?
        // First part in the entityname, second
        // is the entity id you got from adding
        // the new account.
        setStateRequest.EntityMoniker = new EntityReference("account", AccountGuid);

        // Setting 'State' (0 – Active ; 1 – InActive)
        setStateRequest.State = new OptionSetValue(1);

        // Setting 'Status' (1 – Active ; 2 – InActive)
        setStateRequest.Status = new OptionSetValue(2);

        // Execute the request
        SetStateResponse response = (SetStateResponse)organizationservice.Execute(setStateRequest);
    }
    Note: these code examples don't contain logging or exception handling!
    Case
    I want to upload files to CRM Annotations with SSIS. I already uploaded contacts, but now I also want to upload their documents. How do I do that?

    Solution
    You can use the same webservice in a Script Component that you used to insert/update records in CRM. For this example I have a source with a businessKey from my contact, a filename and a filepath. If your source doesn't contain the actual file in a blob, but only a filepath then you can use the Import Column Transformation.
    I used a Lookup Transformation to get the identity id from a CRM database view because I need it to reference the document to my contacts.
    Data Flow Example with Import Columns























    1a) Download CRM SDK
    For this example I used CRM 2013 and I downloaded the free Microsoft Dynamics CRM 2013 Software Development Kit (SDK). Execute the downloaded file to extract all the files. We only need Microsoft.Xrm.Sdk.dll assembly which can be found in the SDK\Bin folder.
    SDK download















    1b) Windows Identity Foundation
    The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.

    2) DLL to SSIS machine
    To use the assembly (DLL) from step 1a in SSIS, you need to add the DLL to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the gac on Win Server 2008 R2. You also need to copy it to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\


    3) Parameters
    To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.
    4 parameters, password is marked sensitive.

    





    4) Script Component - Parameters
    Add a Script Component (type destination) for adding the files. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.
    
    Add parameters as read only variables

    
























    5) Script Component - Input Columns
    Add the columns that you need as Input Columns. For inserting the files as annotations you need the blob column containing the actual file, a filename, an entity ID to reference the file to a CRM contact and optionally a subject and/or description.
    Input Columns for insert

    

























    6) The Script - Add assembly
    Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add three references:
    • microsoft.xrm.sdk.dll (from the SSIS bin folder mentioned in step 3, use browse)
    • System.Runtime.Serialization.dll (from .Net tab)
    • System.ServiceModel.dll (from .Net tab)
    Right click references and choose add reference

















    Now very important: press the Save All button to save the entire internal vsta project (including references)
    Save All













    7) The Script - Insert
    Here is an C# example (for VB.Net use this translator) for inserting files as annotations in CRM with SSIS 2012.
    // C# Code
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.Xrm.Sdk;                // Added
    using Microsoft.Xrm.Sdk.Client;         // Added
    using Microsoft.Xrm.Sdk.Query;          // Added
    using System.ServiceModel.Description;  // Added

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        // Webservice
        IOrganizationService organizationservice;

        // Variables for the CRM webservice credentials
        // You could also declare them in the PreExecute
        // if you don't use it anywhere else
        string CrmUrl = "";
        string CrmDomainName = "";
        string CrmUserName = "";
        string CrmPassWord = "";

        // This method is called once, before rows begin to be processed in the data flow.
        public override void PreExecute()
        {
            base.PreExecute();

            // Fill variables with values from project parameters
            CrmUrl = this.Variables.CrmWebservice.ToString();
            CrmDomainName = this.Variables.CrmDomain.ToString();
            CrmUserName = this.Variables.CrmUser.ToString();
            CrmPassWord = this.Variables.CrmPassword.ToString();

            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
            credentials.UserName.Password = CrmPassWord;
            organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
        }

        // This method is called once for every row that passes through the component from Input0.
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Create new annotation object to store the properties
            Entity newAnnotation = new Entity("annotation");

            // Create an optionset to indicate to which entity this annotation will be linked
            OptionSetValue objectidtypecode = new OptionSetValue();
            objectidtypecode.Value = 2; // 2 is the enitity Contact in this case. Look it up in CRM.
            newAnnotation.Attributes.Add("objectidtypecode", (OptionSetValue)objectidtypecode);

            // Create an entity reference to contact and store it in the column ObjectId
            EntityReference Contact = new EntityReference("contact", Row.ContactId);
            newAnnotation["objectid"] = Contact;

            // Filename of the attachment (without path)
            newAnnotation["filename"] = Row.Filename;

            // The actual file is retrieved from the blob column as byte[]
            // And that byte[] is converted to a Base64String:
            newAnnotation["documentbody"] = Convert.ToBase64String(Row.Document.GetBlobData(0, Convert.ToInt32(Row.Document.Length)));

            // A subject with some title
            newAnnotation["subject"] = Row.Subject;

            // Add the annotation
            organizationservice.Create(newAnnotation);
        }
    }
    Note: this code example doesn't contain logging or exception handling!
    Case
    I have a business application with clients, accounts and other data and I want to make an interface to Microsoft Dynamics CRM 2013. How do you insert, update or delete records in CRM with SSIS?

    Solution
    Although the CRM data is stored in a regular SQL Server database, you’re not allowed to insert or change the data in those tables (otherwise you will lose Microsoft support on CRM).

    There are a couple of third party CRM destination components available like CozyRoc and BlueSSIS. This solution uses a Script Component that inserst/updates/deletes data in CRM via a webservice and a CRM SDK assembly. There are other .Net solutions with for example a custom assembly or a service reference. All roads lead to Rome, but this is a relatively easy script for those with less .Net skills.

    1) Guidlines
    The first part of this solution will probably be different for everybody, but there are a few guidelines.
    1. To make sure you don’t insert records twice, you need a key from your business application in CRM. That could be a hidden field in CRM, but it allows you to check whether the record already exists in CRM.
    2. To update or delete records in CRM you need the guid (the unique key or entity id) from the CRM record.
    So you want to make a lookup or a join to the CRM view to get the required data.
    1. You probably don’t want to update records unnecessarily. This is slow and pollutes the CRM history.
    In this solution I selected the exact same columns from CRM and added the GUID. I joined the two sources on the business key with a left outer join. If the business key on the CRM part is null then it’s an insert else it could be an update.
    You could compare all column the check whether you need an update or not, but because I have about 20 columns to check this could end up in a huge, unreadable and unmaintainable expression. I used a checksum transformation to calculate a hash from all columns and then I only have to compare those two hashes. You could also do this in the source query with some TSQL code.
    example package























    Below I will describe those Script Components.

    2a) Download CRM SDK
    For this example I used CRM 2013 and I downloaded the free Microsoft Dynamics CRM 2013 Software Development Kit (SDK). Execute the downloaded file to extract all the files. We only need Microsoft.Xrm.Sdk.dll assembly which can be found in the SDK\Bin folder.
    SDK download















    2b) Windows Identity Foundation
    The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.

    3) DLL to SSIS machine
    To use the assembly (DLL) from step 2a in SSIS, you need to add the DLL to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the gac on Win Server 2008 R2. You also need to copy it to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

    4) Parameters
    To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.
    4 parameters, password is marked sensitive.

    





    5) Script Component - Parameters
    Add a Script Component (type destination) for Insert, Update or Delete. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.
    Add parameters as read only variables


























    6) Script Component - Input Columns
    Add the columns that you need as Input Columns. For the Insert you only need the columns from your business application. For the update you also need the entity ID from CRM. This is the Technical Id (a guid) from the CRM entity that you want to update. For a delete you only need that entity ID.
    Input Columns for insert























    7) The Script - Add assembly
    Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add three references:
    • microsoft.xrm.sdk.dll (from the SSIS bin folder mentioned in step 3, use browse)
    • System.Runtime.Serialization.dll (from .Net tab)
    • System.ServiceModel.dll (from .Net tab)
    Right click references and choose add reference

















    Now very important: press the Save All button to save the entire internal vsta project (including references)
    Save All













    8a) The Script - Insert
    Here is an C# example (for VB.Net use this translator) for inserting CRM records with SSIS 2012.
    // C# Code
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.Xrm.Sdk;                // Added
    using Microsoft.Xrm.Sdk.Client;         // Added
    using Microsoft.Xrm.Sdk.Query;          // Added
    using System.ServiceModel.Description;  // Added

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        // Webservice
        IOrganizationService organizationservice;

        // Variables for the CRM webservice credentials
        // You could also declare them in the PreExecute
        // if you don't use it anywhere else
        string CrmUrl = "";
        string CrmDomainName = "";
        string CrmUserName = "";
        string CrmPassWord = "";

        // This method is called once, before rows begin to be processed in the data flow.
        public override void PreExecute()
        {
            base.PreExecute();

            // Fill variables with values from project parameters
            CrmUrl = this.Variables.CrmWebservice.ToString();
            CrmDomainName = this.Variables.CrmDomain.ToString();
            CrmUserName = this.Variables.CrmUser.ToString();
            CrmPassWord = this.Variables.CrmPassword.ToString();

            // Connect to webservice with credentials
            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
            credentials.UserName.Password = CrmPassWord;
            organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
        }
      
        // This method is called once for every row that passes through the component from Input0.
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Create a Entity object of type 'account'
            Entity newAccount = new Entity("account");

            // Store the business key of the source in CRM
            // This makes it easier to compare and filter records for update
            newAccount["cst_caressid"] = Row.CaressId;
            
            // fill crm fields. Note fieldnames are case sensitive!
            newAccount["name"] = Row.AccountName;
            newAccount["emailaddress1"] = Row.Email;
            newAccount["telephone1"] = Row.Phone;

            // Address, but check if the columns are filled
            if (!Row.Street_IsNull)
            {
                newAccount["address1_line1"] = Row.Street;
            }

            if (!Row.Housenumber_IsNull)
            {
                newAccount["address1_line2"] = Row.Housenumber;
            }

            if (!Row.Zipcode_IsNull)
            {
                newAccount["address1_postalcode"] = Row.Zipcode;
            }

            if (!Row.Residence_IsNull)
            {
                newAccount["address1_city"] = Row.Residence;
            }
           
            if (!Row.Country_IsNull)
            {
                newAccount["address1_country"] = Row.Country;
            }

            // Filling a OptionSet (dropdownbox) is a little different
            // You need to know the codes defined in CRM. You need
            // CRM knowledge to find those so ask the CRM consultant.
            OptionSetValue accountType = new OptionSetValue();
            if (!Row.AccountType_IsNull)
            {
                switch (Row.AccountType)
                {
                    case "Large":
                        accountType.Value = 1;
                        break;
                    case "Medium":
                        accountType.Value = 2;
                        break;
                    case "Small":
                        accountType.Value = 3;
                        break;
                    default:
                        accountType.Value = 2;
                        break;
                }
                newAccount.Attributes.Add("accounttype", (OptionSetValue)accountType);
            }

            // Reference to an other entity (lookup)
            EntityReference Contact = new EntityReference("contact", Row.ClientGuid);
            newAccount["contactid"] = Contact;


            // Create account
            organizationservice.Create(newAccount);
        }
    }


    8b) The Script - Update
    Here is an C# example (for VB.Net use this translator) for updating CRM records with SSIS 2012.
    // C# Code
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.Xrm.Sdk;                // Added
    using Microsoft.Xrm.Sdk.Client;         // Added
    using Microsoft.Xrm.Sdk.Query;          // Added
    using System.ServiceModel.Description;  // Added

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        // Webservice
        IOrganizationService organizationservice;

        // Variables for the CRM webservice credentials
        // You could also declare them in the PreExecute
        // if you don't use it anywhere else
        string CrmUrl = "";
        string CrmDomainName = "";
        string CrmUserName = "";
        string CrmPassWord = "";

        // This method is called once, before rows begin to be processed in the data flow.
        public override void PreExecute()
        {
            base.PreExecute();

            // Fill variables with values from project parameters
            CrmUrl = this.Variables.CrmWebservice.ToString();
            CrmDomainName = this.Variables.CrmDomain.ToString();
            CrmUserName = this.Variables.CrmUser.ToString();
            CrmPassWord = this.Variables.CrmPassword.ToString();

            // Connect to webservice with credentials
            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
            credentials.UserName.Password = CrmPassWord;
            organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
        }
      
        // This method is called once for every row that passes through the component from Input0.
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Create a Entity object of type 'account'
            Entity existingAccount = new Entity("account");

            // Most important attribute to fill is the entity id
            // This is a GUID column from CRM. Without this
            // column you can't update records in CRM.
            existingAccount["accountid"] = Row.AccountId;
      
            // Since we joined on the business key, it shouldn't
            // be updated. That why this line is a comment.
            // existingAccount["cst_caressid"] = Row.CaressId;
            
            // fill crm fields. Note fieldnames are case sensitive!
            existingAccount["name"] = Row.AccountName;
            existingAccount["emailaddress1"] = Row.Email;
            existingAccount["telephone1"] = Row.Phone;

            // Address, but check if the columns are filled
            if (!Row.Street_IsNull)
            {
                existingAccount["address1_line1"] = Row.Street;
            }

            if (!Row.Housenumber_IsNull)
            {
                existingAccount["address1_line2"] = Row.Housenumber;
            }

            if (!Row.Zipcode_IsNull)
            {
                existingAccount["address1_postalcode"] = Row.Zipcode;
            }

            if (!Row.Residence_IsNull)
            {
                existingAccount["address1_city"] = Row.Residence;
            }
           
            if (!Row.Country_IsNull)
            {
                existingAccount["address1_country"] = Row.Country;
            }

            // Filling a OptionSet (dropdownbox) is a little different
            // You need to know the codes defined in CRM. You need
            // CRM knowledge to find those so ask the CRM consultant.
            OptionSetValue accountType = new OptionSetValue();
            if (!Row.AccountType_IsNull)
            {
                switch (Row.AccountType)
                {
                    case "Large":
                        accountType.Value = 1;
                        break;
                    case "Medium":
                        accountType.Value = 2;
                        break;
                    case "Small":
                        accountType.Value = 3;
                        break;
                    default:
                        accountType.Value = 2;
                        break;
                }
                existingAccount.Attributes.Add("accounttype", (OptionSetValue)accountType);
            }

            // Reference to an other entity (lookup)
            EntityReference Contact = new EntityReference("contact", Row.ClientGuid);
            existingAccount["contactid"] = Contact;
      
            // Update account
            organizationservice.Update(existingAccount);
        }
    }


    8c) The Script - Delete
    Here is an C# example (for VB.Net use this translator) for deleting CRM records with SSIS 2012. It wasn't mentioned in the solution example. You need a full outer join for it. Warning: this is a physical delete which can't be undone. In an other post I will publish an inactivate script example, but it uses other assemblies.
    // C# Code
    using System;
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.Xrm.Sdk;                // Added
    using Microsoft.Xrm.Sdk.Client;         // Added
    using Microsoft.Xrm.Sdk.Query;          // Added
    using System.ServiceModel.Description;  // Added

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        // Webservice
        IOrganizationService organizationservice;

        // Variables for the CRM webservice credentials
        // You could also declare them in the PreExecute
        // if you don't use it anywhere else
        string CrmUrl = "";
        string CrmDomainName = "";
        string CrmUserName = "";
        string CrmPassWord = "";

        // This method is called once, before rows begin to be processed in the data flow.
        public override void PreExecute()
        {
            base.PreExecute();

            // Fill variables with values from project parameters
            CrmUrl = this.Variables.CrmWebservice.ToString();
            CrmDomainName = this.Variables.CrmDomain.ToString();
            CrmUserName = this.Variables.CrmUser.ToString();
            CrmPassWord = this.Variables.CrmPassword.ToString();

            // Connect to webservice with credentials
            ClientCredentials credentials = new ClientCredentials();
            credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
            credentials.UserName.Password = CrmPassWord;
            organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
        }
      
        // This method is called once for every row that passes through the component from Input0.
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Warning: this is a physical delete from CRM which can't be undone
      
            // Delete account. First part in the entityname, second
            // is the entity id from the CRM source.
            organizationservice.Delete("account", Row.AccountId);
        }
    }


    Note: The method above is called late binding. You can also use early binding, but late binding is apparently faster.
    // C# Code
    // Early binding
    Account newAccount = new Account();
    newAccount.Name = "SSISJoost";
    organizationservice.Create(newAccount);

    // Late binding
    Entity newAccount = new Entity("account");
    newAccount["name"] = "SSISJoost";
    organizationservice.Create(newAccount);