SQL OleDB --> Scrpt Component --> SharePoint Destination List

[EDN Admin]

Hello
I am usisng SSIS.
SQL Ole DB --> Script (below) -->SharePoint DestinationList
I am trying to understand why the field AssetTypeO remains empty!! AssetTypeO, knwon in the Output of the Script Component and in the Input of the SharePointDestinationList, is supposed to be filled up with AssetType the SQL field... known in the Input and
Output column in SQL and in the Input columns as well as Input 0 > Input Columns of the Script Component.
<pre>/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SharePoint;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.CompilerServices;
using ExtensionMethods;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
}
}

namespace ExtensionMethods
{
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SharePoint;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.CompilerServices;
using ExtensionMethods;

public static class SPListItemExtensions
{
/// <summary>
/// Returns the value of a Lookup Field.
/// </summary>

public static string GetFieldValueLookup(this SPListItem item,
string fieldName)

{
if (item != null)
{
SPFieldLookupValue lookupValue =
new SPFieldLookupValue(item[fieldName] as string);
return lookupValue.LookupValue;
}
else
{
return string.Empty;
}
}

/// <summary>
/// Returns the value of a Lookup-Field with multiple values.
/// </summary>
public static IEnumerable<string> GetFieldValueLookupCollection(
this SPListItem item, string fieldName)
{
List<string> result = new List<string>();
if (item != null)
{
SPFieldLookupValueCollection values =
item[fieldName] as SPFieldLookupValueCollection;
foreach (SPFieldLookupValue value in values)
{
result.Add(value.LookupValue);
}
}
return result;
}

/// <summary>
/// Returns the SPFieldLookupValue instance of a lookup value.
/// The ID value will be obtained using SPQuery.
/// </summary>
private static SPFieldLookupValue GetLookupValue(
SPWeb web, SPFieldLookup field, string lookupValue)
{
string queryFormat =
@"<Where>
<Eq>
<FieldRef Name={0} />
<Value Type=Text>{1}</Value>
</Eq>
</Where>";
string queryText =
string.Format(queryFormat, field.LookupField, lookupValue);
SPList lookupList = web.Lists[new Guid(field.LookupList)];
SPListItemCollection lookupItems =
lookupList.GetItems(new SPQuery() { Query = queryText });
if (lookupItems.Count > 0)
{
int lookupId =
Convert.ToInt32(lookupItems[0][SPBuiltInFieldId.ID]);
return new SPFieldLookupValue(lookupId, lookupValue);
}
else
{
return null;
}
}

/// <summary>
/// Sets the value of a Lookup-Field.
/// </summary>
public static void SetFieldValueLookup(
this SPListItem item, string fieldName, string lookupValue)
{
if (item != null)
{
SPFieldLookup field =
item.Fields.GetField(fieldName) as SPFieldLookup;
item[fieldName] = GetLookupValue(item.Web, field, lookupValue);
}
else
{
item[fieldName] = null;
}
}

/// <summary>
/// Set the values of a Lookup-Field with multiple values allowed.
/// </summary>
public static void SetFieldValueLookup(this SPListItem item,
string fieldName, IEnumerable<string> lookupValues)
{
if (item != null)
{
SPFieldLookup field =
item.Fields.GetField(fieldName) as SPFieldLookup;
SPFieldLookupValueCollection fieldValues =
new SPFieldLookupValueCollection();
foreach (string lookupValue in lookupValues)
{
fieldValues.Add(
GetLookupValue(item.Web, field, lookupValue));
}
item[fieldName] = fieldValues;
}
}
[/code]
<pre> static void Method(this SPListItem item1, SPWeb web, String AssetTypes)
{
SPList list = web.Lists["TEST - Servers Inventory"];

// Set the value of a SPFieldLookup field
item1.SetFieldValueLookup("AssetTypes", "AssetTypeO");
list.Update();
}
}
} [/code]
Thanks,<br/>
Dom
<
System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager<br/>
<br/>


View the full article