Arquivo

Archive for the ‘SSIS’ Category

Using a Script Component Source to Consume a WebService

Thanks to Dennis and Jim for this original post at: http://dennysjymbo.blogspot.pt/2013/05/using-script-component-source-to.html

     When attempting to use XML as a data source in SSIS you really only have 2 solution (and this depends on whether or not this XML is coming from a web service). If your XML feed is coming from a web service, you can use the Web Service Task and set the output to either a file or a variable. If you set your file to a variable, or file, you can use the XML Task to perform various xpath queries, reformat using an XSLT, etc. The result of which can be set to other variables or a file. If set to a file, it can then be sourced using an XML  Source  and brought into your data flow. But what if you don’t want to use an XML file as your source? SSIS has no way of reading an XML feed from a web service directly into a data flow without custom coding. In this entry I’ll show an example of how this can be done using a Script Component source in a data flow.

     This solution is very similar to the JSON solution I wrote about, with a few changes to apply to XML.  The XML source we’re using is going to apply to metrics used in a call center. These attributes include employee login, date, calls offered, calls answered and calls missed. Our XML source will look something  like this:
<?xml version=”1.0″ encoding=”utf-8″?>
<DisplayStats>
  <DailyStat>
    <WebLogin>eli</WebLogin>
    <Date>2013-03-28T00:00:00</Date>
    <CallsOffered>4</CallsOffered>
    <CallsAnswered>4</CallsAnswered>
    <CallsMissed>0</CallsMissed>
  </DailyStat>
  <DailyStat>
    <WebLogin>rit</WebLogin>
    <Date>2013-03-28T00:00:00</Date>
    <CallsOffered>0</CallsOffered>
    <CallsAnswered>0</CallsAnswered>
    <CallsMissed>0</CallsMissed>
  </DailyStat>
  <DailyStat>
    <WebLogin>Dan</WebLogin>
    <Date>2013-03-28T00:00:00</Date>
    <CallsOffered>15</CallsOffered>
    <CallsAnswered>15</CallsAnswered>
    <CallsMissed>1</CallsMissed>
  </DailyStat>
  <DailyStat>
    <WebLogin>Pab</WebLogin>
    <Date>2013-03-28T00:00:00</Date>
    <CallsOffered>0</CallsOffered>
    <CallsAnswered>0</CallsAnswered>
    <CallsMissed>0</CallsMissed>
  </DailyStat>
  <DailyStat>
    <WebLogin>Rus</WebLogin>
    <Date>2013-03-28T00:00:00</Date>
    <CallsOffered>21</CallsOffered>
    <CallsAnswered>21</CallsAnswered>
    <CallsMissed>0</CallsMissed>
  </DailyStat>
</DisplayStats>

This comes from an ASP.NET web service I built using SOAP:

You can see from this screenshot that the web service takes one parameter, offerDate, with a date format. We're going to want to make this a variable that we can alter at run time every time this package is executed. This can be done through values returned from an Execute Sql Task from a database, set via a function, etc. Regardless of how its implemented, in this example were going to hard code the variable to 03/28/2013:
 
     Now that we have a variable for offerDate, let’s set up our script component. Drag a script component onto the design surface of a data flow. When prompted, select source as the script component type:


On the script screen, we want to make sure to add our SSIS variable in ReadOnlyVariables:

     Next, let’s configure the output buffer for this script component. On the input and output screen, click on the default output and add some columns. These will represent the XML elements were going to get back from our web service:


This screen helps us to define what our output is going to look like. The columns that will represent our XML elements are as follows: WEBLOGIN DT_STR 50,  DATEDT_DBTIMESTAMP, CALLSOFFERED DT_I4, CALLSANSWERED DT_I4 and CALLSMISSEDDT_I4. With this all set up, we can go back to the script screen, click on the Edit Script button and begin coding. In the main.cs file, paste the following code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Xml.Serialization;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        //Get SSIS Variables
         string offerDate = Variables.OFFERDATE;
        //Set Webservice URL
        string wUrl = “http://YOUR WEB SERVICE URI/Respiratory/ResService.asmx/getAgentMetricsXml?offerDate=”+offerDate;
        try
        {
            //Call getWebServiceResult to return our DailyStat array
            DailyStats outPutStats = getWebServiceResult(wUrl);
            //For each group of metrics output records
            foreach (var stats in outPutStats.MDailyStat)
            {
                Output0Buffer.AddRow();
                Output0Buffer.WEBLOGIN = stats.WebLogin;
                Output0Buffer.DATE =stats.Date;
                Output0Buffer.CALLSANSWERED =stats.CallsAnswered;
                Output0Buffer.CALLSOFFERED = stats.CallsOffered;
                Output0Buffer.CALLSMISSED = stats.CallsMissed;
            }
        }
        catch (Exception e)
        {
            failComponent(e.ToString());
        }
    }
    //Method to return our DailyStat array
    private DailyStats getWebServiceResult(string wUrl)
    {
        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
        DailyStats xmlResponse = null;
        try
        {
            //Test the connection
            if (httpWResp.StatusCode == HttpStatusCode.OK)
            {
                Stream responseStream = httpWResp.GetResponseStream();
                //Set xmlString using a stream reader
                using (StreamReader reader = new StreamReader(responseStream))
                {
                    //Deserialize our XML
                    XmlSerializer sr = new XmlSerializer(typeof(DailyStats));
                    xmlResponse = (DailyStats)sr.Deserialize(reader);
                    reader.Close();
                }
            }
            //Output connection error message
            else
            {
                failComponent(httpWResp.StatusCode.ToString());
            }
        }
        //Output xml parsing error
        catch (Exception e)
        {
            failComponent(e.ToString());
        }
        return xmlResponse;
    }
    //Outputs error message
    private void failComponent(string errorMsg)
    {
        bool fail = false;
        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
        compMetadata.FireError(1, “Error Getting Data From Webservice!”, errorMsg, “”, 0, out fail);
    }
}
//Class to hold elements within <DailyStat>
public class DailyStat
{
    public string WebLogin { get; set; }
    public DateTime Date { get; set; }
    public int CallsOffered { get; set; }
    public int CallsAnswered { get; set; }
    public int CallsMissed { get; set; }
}
//Class to hold our array of <DailyStat>
[XmlRootAttribute(“DisplayStats”)]
public class DailyStats
{
    [XmlElement(“DailyStat”)]
    public DailyStat[] MDailyStat { get; set; }
}
After calling our web service and getting our stream of XML, we deserialize into a DisplayStats object:
  XmlSerializer sr = new XmlSerializer(typeof(DisplayStats));
  xmlResponse = (DisplayStats)sr.Deserialize(reader);
This object is returned to the calling method. We then loop through the DailyStat[]array, within the DisplayStats object, and output the attributes to the output buffer:
 //Call getWebServiceResult to return our DailyStat array
            DisplayStats outPutStats = getWebServiceResult(wUrl);
            //For each group of metrics output records
            foreach (var stats in outPutStats.MDailyStat)
            {
                Output0Buffer.AddRow();
                Output0Buffer.WEBLOGIN = stats.WebLogin;
                Output0Buffer.DATE =stats.Date;
                Output0Buffer.CALLSANSWERED =stats.CallsAnswered;
                Output0Buffer.CALLSOFFERED = stats.CallsOffered;
                Output0Buffer.CALLSMISSED = stats.CallsMissed;
            }
     Build, save and lets return to the data flow. We can create a union all that the script component can output to so we can add a data viewer to see the records. In a normal implementation this would go to a data destination:


Let’s now run the package and view the results:


The XML data is now in our data flow and can be sent to a data destination. No physical XML file was needed, which means we didn’t have to rely on an XML Source to get this.

Anúncios
Categorias:WebServices

SSIS Expressions And 4,000 Character Limit

 

In Part 1 of this blog posting I demonstrated how a combination of SSIS package configurations and expressions can make your ETL process dynamic.  In this posting I am going to discuss the dreaded 4000 character limitation in expressions for SQL 2008R2 (and earlier versions).  In SQL Server 2012 (Denali) this limitation is gone.  The example I provided focused on using a variable expression to change the source T-SQL statement used in a Data Flow Task’s OLE_DB Source.

Unfortunately, sooner or later everyone encounters the dreaded 4000 character limitation.  For me, this usually happens when I need to construct a particularly involved source query.  Yes, 4000 characters sounds like a long string but I challenge you to look at some of your more involved queries.  Think about the ones that you were especially proud of.  Perhaps you (un)pivoted some data or used temporary tables.  And if you were really on your game you might have included some decent inline comments to spare those following in your footsteps the pain of dissecting the monster!  In this post, I will explore some different ways to overcome the expression length limitation.

Source System Stored Procedure or View

A quick fix is to use a stored procedure or view hosted in the source system database.  By using procedure parameters or by calling a view and adding a dynamic WHERE clause, the 4000 character limit can become a thing of the past.  However, I don’t like adding views or stored procedures to a source system database.  I don’t even like relying on stored procedures or views that already exist on the source system.  Too frequently, changes are made to source system objects without considering downstream implications.  However, if you have an interface agreement with the source system (or have control over that system) this can be an effective way to overcome the 4000 character limitation.

That said, I believe it’s better to think of a data warehouse as a separate entity from the source system on which it relies.  Tying the two together will likely complicate the data warehouse development because the source system’s development, testing and deployment schedule will generally take precedence over the data warehouse schedule.  So, if there is a change you need to complete or correct your ETL, you might have to sit on your hands for a while!

Data Flow Script Component

Script Component as Data Source

If you like cutting code, a Data Flow Task script component can be used as a data source.  SSIS variables can be passed into the script and used within the script to alter the data query.  Please note that this does not mean that a script task can be used to produce result sets with differing metadata.  Data Flow tasks must adhere to the metadata defined for the source query.  As long as your dynamic source query produces the same structure, it can be used to overcome the 4000 character expression limit.

I am very wary of this solution for performance reasons.  .Net code is a fine and wonderful thing, but it comes with an overhead price.  So, I would not use this solution unless there were other compelling reasons.

Cohosted Database Stored Procedure

The third solution involves the use of an ETL database that is hosted on the same server as your source system’s database.  This could be a staging database or even the data warehouse itself.  It this case, it would be easy to create stored procedures therein that use fully qualified object names (database.schema.object).   (I knew that if I tried hard I could fit in a highfalutin word like “therein”.)   If you do not have the luxury of a cohosted database, it would be possible to use a linked server and a four part naming convention in your stage database stored procedure.

However, I really don’t like the requirement of a cohosted database requirement.   In my experience, things change.  When one of these databases is required to move to another server without the other, you will be forced to react.  A Linked Server solution will avoid this problem but you will pay the price in data transfer performance.

Stage Database Stored Procedure

It is important to remember that the 4000 character expression limitation applies to expressions and NOT to variables.  Therefore, if the expression is a variable expression or can reference a variable, you simply need to find a way to populate the variable with the desired string.

The final technique uses a stored procedure in the stage database to generate a T-SQL query that is passed back to the SSIS package and stored in a variable.  For this demo, I have created a T-SQL statement that is almost 6000 characters long due to the number of joins and the NULL handling that occurs in the SELECT clause. Similar to what we reviewed in Part 1 of this blog posting, the query includes a WHERE clause that filters by a date field.   I have included this query below.

 

SELECT 
	CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BusinessEntityID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BusinessEntityID]))  ELSE -999 END AS [BusinessEntityID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[NationalIDNumber]))), 0) != 0 THEN LTRIM(RTRIM(ee.[NationalIDNumber]))  ELSE 'N/A' END AS [NationalIDNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[LoginID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[LoginID]))  ELSE 'N/A' END AS [LoginID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[JobTitle]))), 0) != 0 THEN LTRIM(RTRIM(ee.[JobTitle]))  ELSE 'N/A' END AS [JobTitle]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BirthDate]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BirthDate]))  ELSE 'N/A' END AS [BirthDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[MaritalStatus]))), 0) != 0 THEN LTRIM(RTRIM(ee.[MaritalStatus]))  ELSE 'N/A' END AS [MaritalStatus]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[Gender]))), 0) != 0 THEN LTRIM(RTRIM(ee.[Gender]))  ELSE 'N/A' END AS [Gender]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Title]))), 0) != 0 THEN LTRIM(RTRIM(per.[Title]))  ELSE 'N/A' END AS [Title]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[FirstName]))), 0) != 0 THEN LTRIM(RTRIM(per.[FirstName]))  ELSE 'N/A' END AS [FirstName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[MiddleName]))), 0) != 0 THEN LTRIM(RTRIM(per.[MiddleName]))  ELSE 'N/A' END AS [MiddleName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[LastName]))), 0) != 0 THEN LTRIM(RTRIM(per.[LastName]))  ELSE 'N/A' END AS [LastName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Suffix]))), 0) != 0 THEN LTRIM(RTRIM(per.[Suffix]))  ELSE 'N/A' END AS [Suffix]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[DepartmentID]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[DepartmentID]))  ELSE -999 END AS [DepartmentID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[Name]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[Name]))  ELSE 'N/A' END AS [DeptName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[GroupName]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[GroupName]))  ELSE 'N/A' END AS [GroupName]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(salespers.[TerritoryID]))), 0) != 0 THEN LTRIM(RTRIM(salespers.[TerritoryID]))  ELSE -999 END AS [TerritoryID]
	,salespers.SalesQuota
	,salespers.Bonus
	,salespers.CommissionPct
	,salespers.SalesYTD
	,salespers.SalesLastYear

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[OrderDate]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[OrderDate]))  ELSE 'N/A' END AS [OrderDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))  ELSE 'N/A' END AS [SalesOrderNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[AccountNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[AccountNumber]))  ELSE 'N/A' END AS [AccountNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SubTotal]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SubTotal]))  ELSE CAST(0 AS money) END AS [SubTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TaxAmt]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TaxAmt]))  ELSE CAST(0 AS money) END AS [TaxAmt]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Freight]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Freight]))  ELSE CAST(0 AS money) END AS [Freight]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TotalDue]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TotalDue]))  ELSE CAST(0 AS money) END AS [TotalDue]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Comment]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Comment]))  ELSE 'N/A' END AS [Comment]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderID]))  ELSE -999 END AS [SalesOrderID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderDetailID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderDetailID]))  ELSE -999 END AS [SalesOrderDetailID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))  ELSE 'N/A' END AS [CarrierTrackingNumber]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[OrderQty]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[OrderQty]))  ELSE  CAST(0 AS money) END AS [OrderQty]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPrice]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPrice]))  ELSE  CAST(0 AS money) END AS [UnitPrice]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPriceDiscount]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPriceDiscount]))  ELSE  CAST(0 AS money) END AS [UnitPriceDiscount]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[LineTotal]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[LineTotal]))  ELSE 'N/A' END AS [LineTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[ModifiedDate]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[ModifiedDate]))  ELSE 'N/A' END AS [ModifiedDate]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductID]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductID]))  ELSE -999 END AS [ProductID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[Name]))), 0) != 0 THEN LTRIM(RTRIM(prod.[Name]))  ELSE 'N/A' END AS [ProdName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductNumber]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductNumber]))  ELSE 'N/A' END AS [ProductNumber]

FROM HumanResources.Employee ee 
INNER JOIN HumanResources.EmployeeDepartmentHistory 
	ON ee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID 
INNER JOIN HumanResources.Department hr_dept
	ON HumanResources.EmployeeDepartmentHistory.DepartmentID = hr_dept.DepartmentID 
INNER JOIN Person.Person per
	ON ee.BusinessEntityID = per.BusinessEntityID 
INNER JOIN Sales.SalesPerson salespers
	ON ee.BusinessEntityID = salespers.BusinessEntityID 
INNER JOIN Sales.SalesOrderHeader ordhdr
	ON salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
    AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND salespers.BusinessEntityID = ordhdr.SalesPersonID 
INNER JOIN Sales.SalesOrderDetail orddet
	ON ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
INNER JOIN Production.Product prod
	ON orddet.ProductID = prod.ProductID
WHERE
	orddet.ModifiedDate	> '01/01/2000'

 I will use the same SSIS package I used in Part 1 to implement this technique.  All that is required is an Execute SQL task that will execute and capture the results of the stored procedure.  The steps required to set up this task are shown below.

 

Step 1 – Create a stored procedure to dynamically generate and return the T-SQL statement.  In this example I am using an output parameter to pass the T-SQL statement.

 

USE [Stage_ExpressionLimit]
GO

/****** Object:  StoredProcedure [dbo].[usp_AdWorksSourceQuery]    Script Date: 12/28/2011 09:25:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_AdWorksSourceQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_AdWorksSourceQuery]
GO

CREATE PROC usp_AdWorksSourceQuery

@ModDate varchar(20)
,@SQLOut nvarchar(max) OUTPUT

AS 

SET NOCOUNT ON

DECLARE @SQL varchar(max)

SET @SQL = 

'
SELECT 
	CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BusinessEntityID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BusinessEntityID]))  ELSE -999 END AS [BusinessEntityID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[NationalIDNumber]))), 0) != 0 THEN LTRIM(RTRIM(ee.[NationalIDNumber]))  ELSE ''N/A'' END AS [NationalIDNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[LoginID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[LoginID]))  ELSE ''N/A'' END AS [LoginID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[JobTitle]))), 0) != 0 THEN LTRIM(RTRIM(ee.[JobTitle]))  ELSE ''N/A'' END AS [JobTitle]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BirthDate]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BirthDate]))  ELSE ''N/A'' END AS [BirthDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[MaritalStatus]))), 0) != 0 THEN LTRIM(RTRIM(ee.[MaritalStatus]))  ELSE ''N/A'' END AS [MaritalStatus]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[Gender]))), 0) != 0 THEN LTRIM(RTRIM(ee.[Gender]))  ELSE ''N/A'' END AS [Gender]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Title]))), 0) != 0 THEN LTRIM(RTRIM(per.[Title]))  ELSE ''N/A'' END AS [Title]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[FirstName]))), 0) != 0 THEN LTRIM(RTRIM(per.[FirstName]))  ELSE ''N/A'' END AS [FirstName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[MiddleName]))), 0) != 0 THEN LTRIM(RTRIM(per.[MiddleName]))  ELSE ''N/A'' END AS [MiddleName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[LastName]))), 0) != 0 THEN LTRIM(RTRIM(per.[LastName]))  ELSE ''N/A'' END AS [LastName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Suffix]))), 0) != 0 THEN LTRIM(RTRIM(per.[Suffix]))  ELSE ''N/A'' END AS [Suffix]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[DepartmentID]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[DepartmentID]))  ELSE -999 END AS [DepartmentID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[Name]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[Name]))  ELSE ''N/A'' END AS [DeptName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[GroupName]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[GroupName]))  ELSE ''N/A'' END AS [GroupName]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(salespers.[TerritoryID]))), 0) != 0 THEN LTRIM(RTRIM(salespers.[TerritoryID]))  ELSE -999 END AS [TerritoryID]
	,salespers.SalesQuota
	,salespers.Bonus
	,salespers.CommissionPct
	,salespers.SalesYTD
	,salespers.SalesLastYear

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[OrderDate]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[OrderDate]))  ELSE ''N/A'' END AS [OrderDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))  ELSE ''N/A'' END AS [SalesOrderNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[AccountNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[AccountNumber]))  ELSE ''N/A'' END AS [AccountNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SubTotal]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SubTotal]))  ELSE CAST(0 AS money) END AS [SubTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TaxAmt]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TaxAmt]))  ELSE CAST(0 AS money) END AS [TaxAmt]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Freight]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Freight]))  ELSE CAST(0 AS money) END AS [Freight]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TotalDue]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TotalDue]))  ELSE CAST(0 AS money) END AS [TotalDue]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Comment]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Comment]))  ELSE ''N/A'' END AS [Comment]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderID]))  ELSE -999 END AS [SalesOrderID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderDetailID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderDetailID]))  ELSE -999 END AS [SalesOrderDetailID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))  ELSE ''N/A'' END AS [CarrierTrackingNumber]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[OrderQty]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[OrderQty]))  ELSE  CAST(0 AS money) END AS [OrderQty]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPrice]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPrice]))  ELSE  CAST(0 AS money) END AS [UnitPrice]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPriceDiscount]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPriceDiscount]))  ELSE  CAST(0 AS money) END AS [UnitPriceDiscount]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[LineTotal]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[LineTotal]))  ELSE ''N/A'' END AS [LineTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[ModifiedDate]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[ModifiedDate]))  ELSE ''N/A'' END AS [ModifiedDate]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductID]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductID]))  ELSE -999 END AS [ProductID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[Name]))), 0) != 0 THEN LTRIM(RTRIM(prod.[Name]))  ELSE ''N/A'' END AS [ProdName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductNumber]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductNumber]))  ELSE ''N/A'' END AS [ProductNumber]

FROM HumanResources.Employee ee 
INNER JOIN HumanResources.EmployeeDepartmentHistory 
	ON ee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID 
INNER JOIN HumanResources.Department hr_dept
	ON HumanResources.EmployeeDepartmentHistory.DepartmentID = hr_dept.DepartmentID 
INNER JOIN Person.Person per
	ON ee.BusinessEntityID = per.BusinessEntityID 
INNER JOIN Sales.SalesPerson salespers
	ON ee.BusinessEntityID = salespers.BusinessEntityID 
INNER JOIN Sales.SalesOrderHeader ordhdr
	ON salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
    AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND salespers.BusinessEntityID = ordhdr.SalesPersonID 
INNER JOIN Sales.SalesOrderDetail orddet
	ON ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
INNER JOIN Production.Product prod
	ON orddet.ProductID = prod.ProductID
WHERE
	orddet.ModifiedDate	> ''' + @ModDate +  ''''

SET @SQLOut = @SQL

Step 2– Use package configuration to retrieve LastLoadDate value.  This is identical to the technique used in Part 1 of this blog posting.

 

Configuration Table

Step 3 – Use an Execute SQL Task in the Control Flow task to execute the stored procedure created in Step 1 and store the output parameter in the package variable called SQL_AdWorks_SELECT.  This variable will be used by the Data Flow Task and it is the same variable used in Part 1 of this blog post.  The settings of the Execute SQL task a shown below:

Execute SQL Task

Execute SQL Task - General

Execute SQL Task - Parameters

The remainder of the package is unchanged! This technique was used to great advantage in a project I was involved with recently.  The real beauty is that completely frees the ETL developer from this limitation, maintains the benefits of directly querying the source database and does not place unrealistic expectations for where the database(s) will be hosted.

In the third blog posting in this series, I will demonstrate how this ETL solution looks in SQL Server 2012 without the worry of the 4000 character limitation.

By Craig Love

Why Use Config Files?

You have created 10 packages in BIDS on your Development laptop and now you need to deploy them to your development server. From there they will be deployed to QA and then on to Production. Your connection managers currently point to the localhost on your machine. You could deploy them to the first server then go into each package and point all the connection managers to the data source and destinations on that new server. Depending on the number of packages and connections this could take some time. You would then have to repeat it each time you move these packages to a new server. You may ask is there a better way?

This is where configuration files can make your life easier. Configuration files allow you to set properties for a package that will be saved outside of BIDS. You are able to save values for all the properties of the package such as connection managers, variables, task properties, just about any property. When the package is run the system will check for the configuration files and will replace the values listed in the package with those in the configuration file then run. If it cannot find the configuration file it responds differently depending on the version of SQL Server. In SQL Server 2005 the package will fail due to error if it cannot find the configuration files. In SQL Server 2008 the system will try using the default settings in the package. If they work the package will not fail.

If you set up a configuration file for each of your connections you can change the server in each of your configuration files when you move packages. You will want to move the configuration files along with the packages. If multiple packages use the same connection they can use the same configuration file which would cut down on the number of configuration files you need and number of places you would need to make a change.

Download Whitepaper 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property.

  • TIP #1: Edit Package Configuration and Data Source after upgrading
  • TIP #2: Convert to project deployment model using Project Conversion Wizard
  • TIP #3: Update Execute Package Task to use project reference and use parameter to pass data from parent package to child package
  • TIP #4: Parameterize PackageName property of Execute Package Task to dynamically configure which child package to run at execution time
  • TIP #5: Convert package configuration to parameter when possible

Download Whitepaper Download Whitepaper 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

Link:http://blog.sqlauthority.com/2011/12/03/sqlauthority-news-download-whitepaper-5-tips-for-a-smooth-ssis-upgrade-to-sql-server-2012/

Categorias:Version Upgrade

Debbuging SSIS Variables – The Locals Window

 

Link:http://sqlserverpedia.com/blog/sql-server-bloggers/debugging-ssis-variables-part-two%E2%80%93the-locals-window/

I had a blog post several weeks ago about debugging the values of SSIS Variables. I shared the method I have always used, which was simply (actually maybe it’s not simple at all) adding a script task into the control flow and placing a message box in the control flow, displaying the value of the variable.

Eric Wisdahl commented that another way of seeing the value of your variables (and in truth a less labor intensive way at that), is to use the locals window from the DEBUG (emphasis mine) menu in the BIDS designer.  I responded in typical change-fearing fashion: saying that once I had found my old way, I just stopped looking for alternatives.  A classic example of “we’ve always done it this way”.   Now in the grand scheme of things is this completely horrible?  No, because I am “getting it done” , but as you’ll see, my method takes approximately 20 mouse clicks and keystrokes, and as I self-deprecatingly pointed out, almost always involves a Google search.  The locals window takes … many fewer keystrokes, fewer mouse clicks, less brow furrowing, and fewer memory (and ultimately Google) searches.

Did I feel a little stupid?  Yeah.  But I’m willing to share all of the this as an example of why it is important to interact with your fellow SQL folks.  If I were not a social person, inclined to share what I know and seek out what I do not,  I would have continued debugging with this method and not grown.  Again, is this particular oddity in my debug arsenal the worst thing I could do regarding my career?  No of course not.  But a pattern of resisting improvement certainly can.  So anyway back to the matter at hand:  Debugging your SSIS Variables Using the Locals Window.

Begin Lift from Previous Blog Post: (Feel free to do the Wayne’s World Flashback jingle in your head……….) —>

I’m in SSIS.  I am using a File System Task to rename this file.

Figure 1

The value of the file I am renaming is getting supplied by the values in this table:

Figure 2

Which I’m supplying to SSIS via the variable named vFullDirectory which is populated by the Execute SQL Task:

(here is the variable configuration)

Figure 3

(here is the view of the configuration of the File System Task)

Figure 4

(and here is the configuration of the Execute SQL Task)

Figure 5

(there is a bit more to populating the variable than what is seen in this screen shot, but that will be covered in later posts).

Ha ha ClientFile.txt !  Your days with that name are numbered!

Here is what I see when I run my package:

Figure 6

The errors are not entirely helpful:

Figure 7

So here is what I do:

<———–  End Lift .

Begin Improved Method —>

What we’re going to do is add a breakpoint to the control flow task which is erroring out:

Figure 8

Select the file system task and right click.  Select Edit Breakpoints.

Figure 9

Select the box beside the break condition “OnPreExecute” event.  (by the way for more on using the breakpoint editor, see John Welch’s post here).

Close the Set Breakpoints window. Here is what the task looks like with its eagerly waiting to be helpful breakpoint:

Figure 10

(I’m a breakpoint, I can’t wait to make something “break”)

Now let’s start the package again and see the breakpoint work its magic:

Figure 11

The breakpoint shows us that it is stopped with the yellow arrow.  It didn’t error out.  It’s waiting for us to tell it to continue.

Now since we know the problem is most likely the value of that variable we are populating with the Execute SQL task, let’s go take a look at its value using the Locals window:

Go to the Debug Menu and click Debug>Windows>Locals

Figure 12

The Locals window will appear somewhere (where depends on your settings) mine usually appears in the bottom of my screen.  Here is a first glance:

Figure 13

Click on that plus sign beside the word Variables to see all of the variables and their values.

Figure 14

So looking at the value for the pesky variable (circled in red) we have been troubleshooting I can see that the problem is that I do not have the slash between my directory name and my file name.  Now I can go in and fix the problem in the Execute SQL task, take the breakpoint out and be done. (BTW If I wanted to continue the execution of the package I would hit F5 or select Continue from the Debug Menu.)

But one thing I did notice which happens using the Locals Window, which may be the only reason a person would want to use the message box method instead of or in addition to this method is the part that I highlighted in yellow in the above shot.  Notice that the Locals window puts in the Escape characters beside all of the slashes?  If a person were dealing with many file type values in his/her variables and specifically troubleshooting them, looking at the locals window with its many double slashes could get confusing.  I found at least one person who thought so here.  Nevertheless, I think the Locals window is much better for day to day SSIS debugging.

Watch SSIS Variable Value inside BIDS during execution

Link:http://pragmaticworks.com/forum/yaf_postsm57_How-to-watch-variable-values-during-SSIS-package-execution.aspx#post57

When you are running package in BIDS sometimes you may want to find out what is the value of SSIS Variables before or after execution of certain task. Perform the following steps.

Lets say you want to watch variable called varCustomerID before Step3 is executed.

1. Right click on Step3 Task
2. Select Edit BreakPoints
3. Check OnPreExecute option (Or OnPostExecute if you want to watch value after task is completed) and hit OK.

4. Now execute package. When breakpoint is hit you can bring variable watch window by opening “Locals” window (See below)

SSIS Watch Variable

Check this URL for more information on various troubleshooting techniques.

http://www.bidn.com/blog…ent-troubleshooting-tips

Passing Variables to and from an SSIS task

Link:http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

So, in the spirit of building up a knowledge base, I have constructed an example to demonstrate passing variables into and out of anExecute SQL Task and Script Task

The two tasks do fundamentally the same thing, which is to try and date-stamp a file.  The final variable value “FullPath” could then be easily used by a File System Task to copy/move or delete a file perhaps.

I suppose most SQL Server developers would be more comfortable knocking up this fairly trivial code in SQL, but the difficulty is in passing and catching the input variables in the task.   This example demonstrates the problem.

I have set up a package with three String variables called

  • FileName, which has a data type of String and an initial value of “Import.txt”
  • FolderName, which has a data type of String and an initial value of “c:\”
  • FullPath, which has a data type of String and no initial value

… and an ‘Execute SQL Task and a Script’  Task.

The package is called, rather imaginatively, “Package3”. The scope of the variables is at the package level.  One thing to note when you set up variables (choose SSIS-VARIABLES from the top menu) is to make sure you have clicked on the package and not a task when you create or add a variable.  If you create a variable while being clicked on a task (therefore with task scope) then the variable will disappear from the list when you click up to the package.  Sorry to be an old dog but I initially found this a bit confusing.

The simplest way to inspect your variables is to set a break-point on the task (right click on the task and choose EDIT BREAKPOINTS) for the OnPostExecute event of the task.  This will then allow you to  inspect the value of the variable after the task has completed its execution.  The red dots on the tasks indicate that there are already breakpoints set up on the task.

Doing it the ‘Execute SQL Task’ way

In the ‘Execute SQL Task Editor’ in the ‘Parameter Mapping’ section, (double-click on the task and choose Parameter mapping), I have set it up so that the two variables i.e. User::FolderName and User::FileName are added to the dialogue box.  Each has a Direction of “Input” which seems logical and a data type of VARCHAR.  The parameter names that I have used are just the ordinal positions of 0 and 1, which is what the context help suggests.  In other words, the value of User::FolderName is the first input parameter and User::FileName is the second input parameter.  The parameter lengths are 50. In other words, we are dealing with a varchar(50) parameter.  The initial default values for these, when you set them up, are -1 which tells me nothing I am afraid.

For the Result Set options on the Execute SQL Task, I have put in the aliased name of the output column from my query, which, giving my poetic instincts full rein, I have called FullPathFromQuery, and I want to pass this to my variable User::FullPath.

‘So what about the query?’ you may ask.  Well, if we go back to the General option on the Execute SQL Task Editor, you can see the code and I will list it again here

DECLARE @YesterdaysDate varchar(8)

DECLARE @Filename varchar(50)

DECLARE @Folder varchar(50)

DECLARE @Etc varchar(50)

SET @Folder = ?

SET @Filename = ?

–SET @Etc = ?

SELECT @YesterdaysDate = CONVERT(varchar(8),DATEADD(dd,-1,getdate()),112)

SELECT @Folder + SUBSTRING(@Filename,1,CHARINDEX(‘.txt’,@Filename)-1) + ‘_’ + @YesterdaysDate + ‘.txt’ AS FullPathFromQuery

For such trivial code you would not want to set up a stored procedure I suspect, so the option of passing parameters to a stored procedure is not really there.

The only way to pick up these input variable values is to use question marks “?” in the order that they are passed.  This query as it stands will clearly not parse in query analyser window so you can only really test your code by running it in the task: This is not ideal.

You must also set the ResultSet option to be “Single row”.

If you run this to the break point one can see that the variable User::FullPath has been assigned the value  C:\\Import_200870805.txt ( I ran this on 6th Aug  2008) so the code must be working.

Categorias:Parameters