Here at M&S we are working on a ground-breaking new project containing multiple data sources being brought into one Salesforce org and displayed using Visualforce with Bootstrap CSS. A crucial and challenging area of our client request involved not replicating data from external enterprise systems by storing it in a Salesforce Object (neither Standard nor Custom). This led us to the use of Salesforce External Objects. External objects are similar to custom objects, except that they map to data that’s stored outside your Salesforce organization. Each external object relies on an external data source definition to connect with the external … Read More
SSIS Tip – Xpath for Root Level Attributes (XML Tasks)
For some reason, SSIS’s XML Data source does not allow you to directly access root level attributes. Therefore, we’ve got to use a XML Task and variables in order to pull this data in. Create a variable for every attribute, use the string data type (change later in data flow if need be) Use the following example (fill your own Xpath in the Second Operand) This example will only pull in one value. Should there be multiple attributes with the same name, you’ll have to use a Foreach loop.
SSIS Tip – Xpath from a C# Script Task
At some point you may need to use Xpath from within a script task rather than using Integration Services built in XML Task’s Xpath functionality. Here’s how I implemented it. The primary limitation is that it will only return a single value, but this can be adjusted to return multiple values easily. Chances are you’ll already be using a script task, but incase you don’t, start with a standard Script Task (this can be either a Control Flow Script Task or a Data Flow Script Task). Don’t forget to set your ReadOnly / ReadWrite variables. Here’s the boilerplate code for using … Read More
SSIS Tip – Read from Variables to Rows in Data Flow using Script Task
Reading from variables to augment rows with data from variables isn’t readily apparent in SSIS. A workaround I found was to use a script task right before your OLE DB Destination and access the SSIS variable objects directly. Script Task Setup Start with a standard Script Task in your data flow. Add columns to your output in the ‘Inputs and Outputs’ tab, set the names and output data types appropriately Access Variables in your ‘main’ script section by using the following code IDTSVariables100 vars = null; //Gets reference to the SSIS Variables this.VariableDispenser.LockOneForRead(“session”, ref vars);//Locks the Variable in quotes, for Read … Read More
SSIS Tip – Save Package to MS SQL Server (Integration Services)
In order to save your SSIS package built using Business Intelligence Development Studio (BIDS) to Integration Services MSDB, you’ll need to: In BIDS, right-click the solution and select Properties Under Deployment Utility, change the CreateDeploymentUtility option from False to True Rebuild the solution Now find in your solution project folder under the bin\deployment file path an Integration Services Deployment Manifest. Right-click this manifest file and select Deploy. Follow the wizard steps to deploy to your server and choose where to save to the database. The package should then appear in SSIS in SQL Server Management Studio under Integration Services in /MSDB.
Request XML from URL in SSIS C# Script Task
Occasionally, I’ve needed to pull XML from the internet via an URL. This allows you to dynamically pull XML from sources than your local.In order to do so, you’ll need a string variable (to populate with the XML from the internet) and the code below in a Script Task. Every time you need to reference the XML in the variable, change your XML Data Sources and XML tasks to pull XML from a variable. string Url = (string)Dts.Variables[“Url”].Value; XmlDocument doc = new XmlDocument(); doc.Load(Url); //You can use a string here if you’d like…I pull from a variable for the URL … Read More
Data Validation and Cleansing – Technology, a New “Mouse Trap”
You lie in wait, looking for your prey, it approaches, and looks around. You spring the trap and
find the spoils of war…the dreaded %20
Ok, maybe not all that exciting, but it brings up an important problem in data conversion and in
application development projects. Namely the importance of input data trapping and input
validation.