Andrus Development

0.00000000000 as Text Error when exporting to Excel from SSRS.

2016-06-02T09:56:00.004-07:00

Just a quick tip/fix for this problem. When exporting a report to excel, sometimes you'll end up getting a long text version of the number zero instead of the actual number -- causing issue and/or time correcting the issue for users.

The fix is simple, in this case, I just cast the numbers as decimal in my query forcing it to treat it as such. (ex: CAST(PCT AS DECIMAL) AS PCT )

Running specialized CSV in SSRS with different device settings.

2015-08-05T12:57:00.000-07:00

So this will be useful if you want to change the default settings for a single report, and not all reports, that are exported to CSV. An example, making semi-colon or tab delimited values.

A good read on bypassing the "CSV Device Information Settings" used on the reporting server.

http://blogs.infosupport.com/modify-reporting-services-export-to-csv-behavior/

Here are a list of the settings that can be changed in the url:
https://msdn.microsoft.com/en-us/library/ms155365%28v=sql.105%29.aspx

So instead of using the standard user-friendly interface, you will be using the file system interface, found in /ReportServer/Pages/ReportViewer.aspx?

and adding on your commands, just like you would do with parameters, to override the settings:

&rs:Command=Render&rs:Format=CSV&rc:ExcelMode=true&rc:Qualifier=%22&rc:NoHeader=false&rc:FieldDelimiter=,

Note:
This however, still leaves the problem with the qualifier. I was told that a csv file being sent to a third-party had to have quotation marks around each value (e.g. "First Name"). This doesn't seem possible in SSRS. The qualifier will only put quotes around a field if there was already a quote in the value (e.g. William "Big Bill" Andrus => "William "Big Bill" Andrus") which would also give incorrect usage of quotes if you hard code them (e.g. "First Name" => ""First Name""). And, there seems to be no way to force the qualifier to display.

Mocking a user

2015-06-11T13:45:00.001-07:00

After lots of searching and testing, I finally ended up with piece of code to mock a user for unit testing.

            this.Principal = new Mock<IPrincipal>();

            List<Claim> claims = new List<Claim>{
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", username), 
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier", userid)
            };

            var fakeIdentity = new GenericIdentity(username);
            //fakeIdentity.RemoveClaim(new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", ""));
            fakeIdentity.AddClaims(claims);

            var fakePrincipal = new GenericPrincipal(fakeIdentity, roles);
            Principal.Setup(n => n.Identity).Returns(fakeIdentity);

            Http.Setup(x => x.User).Returns(fakePrincipal);
            Http.Setup(x => x.User.Identity.Name).Returns(username);
            Http.Setup(x => x.User.Identity).Returns(fakeIdentity);

Find a field in SQL Server database

2015-06-09T08:32:00.002-07:00

Sometimes you need to find a field and don't know what table or view it might be coming from. A very useful script to pull out:

SELECT b.name, c.name
FROM sys.schemas a              
JOIN sys.all_objects b ON b.schema_id = a.schema_id 
JOIN sys.columns c ON c.object_id = b.object_id 
WHERE c.NAME LIKE '%Find Field%'

Search for fields on ReportServer

2015-06-09T08:26:00.000-07:00

/*
 * Get a list of reports in a specific path/folder
 */
;WITH ItemContentBinaries AS
(
 SELECT
 ItemID
 ,Name
 ,[Type]
    ,CASE [Type]
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
 FROM ReportServer.dbo.[Catalog] with (nolock)
 WHERE [PATH] LIKE '/Department Level/%'
)
---ADD WORDS, USED IN FIELDS, TO SEARCH FOR 
,WordSearchCTE AS
(
 SELECT 'SSN' AS Word
 UNION ALL
 SELECT 'AGE'
 UNION ALL
 SELECT 'DOB'
 UNION ALL
 SELECT 'EPMP'
 UNION ALL
 SELECT 'PAY'
 UNION ALL
 SELECT 'COMP'
)
--This CTE strips off the BOM if it exists...
,ItemContentNoBOM AS
(
 SELECT
    ItemID
 ,Name
 ,[Type]
 ,TypeDescription
    ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
          ELSE Content
     END AS Content
 FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
 SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
--and only select from those that contain the search terms 
SELECT * FROM
(
 SELECT 
 ItemID,Name,[Type],TypeDescription,ContentXML
 ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
 ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
 FROM ItemContentXML
 --Get all the Query elements (The "*:" ignores any xml namespaces)
 CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
)x
JOIN 
(
 select '%'+Word+'%' as pattern
 from WordSearchCTE
) w on CommandText like w.pattern

Powershell Profile Setup and Resize script on startup

2014-11-09T17:12:00.001-08:00

1)Setup your profile for powershell:


if (!(test-path $profile )) 
{new-item -type file -path $profile -force}

2)Open up the profile:

Notepad $profile

3)Cut and paste resize into notepad and save:

##
## Author   : Roman Kuzmin
## Synopsis : Resize console window/buffer using arrow keys
##

function Size($w, $h)
{
    New-Object System.Management.Automation.Host.Size($w, $h)
}

function resize()
{
Write-Host '[Arrows] resize  [Esc] exit ...'
$ErrorActionPreference = 'SilentlyContinue'
for($ui = $Host.UI.RawUI;;) {
    $b = $ui.BufferSize
    $w = $ui.WindowSize
    switch($ui.ReadKey(6).VirtualKeyCode) {
        37 {
            $w = Size ($w.width - 1) $w.height
            $ui.WindowSize = $w
            $ui.BufferSize = Size $w.width $b.height
            break
        }
        39 {
            $w = Size ($w.width + 1) $w.height
            $ui.BufferSize = Size $w.width $b.height
            $ui.WindowSize = $w
            break
        }
        38 {
            $ui.WindowSize = Size $w.width ($w.height - 1)
            break
        }
        40 {
            $w = Size $w.width ($w.height + 1)
            if ($w.height -gt $b.height) {
                $ui.BufferSize = Size $b.width $w.height
            }
            $ui.WindowSize = $w
            break
        }
        27 {
            return
        }
    }
  }
}

4) Change policy for the profile, exit powershell and open a new version "Run as Administrator":

Set-ExecutionPolicy bypass -Scope CurrentUser

5) Close powershell and open a new version, type resize.

SSRS: Setting a value dynamically for an unknown count of distinct values -- AND -- having a legend outside of a chart

2014-10-23T12:05:00.003-07:00

One of the tricks that I do, is removing the legend out of charts into a table. To do this, I need to explicitly set the colors and/or markers for each category. In this example, to do this dynamically, I use this basic statement with a list of colors -- where the number of possible results back maybe be up to 4 distinct values.

=Choose(RunningValue(Fields!Unknown.Value,CountDistinct,Nothing),
"#0000e7",
"#626297",
"CornflowerBlue",
"#2f52a6")

So, in this example I put a sparkline in the table with no category or series group -- forcing it to only display one color marker. The logic that I used in the chart's marker and fill color are then also used in the sparkline, giving a legend outside of the chart.

If need be, you can just create a chart with the name and color/marker sparkline and move your legend anywhere you want.

Change Username for ClaimsIdentity in C#

2014-10-23T08:46:00.001-07:00

I needed a way to change the authentication's username in a website. This basically applies the change, and then signs the user out, change session info and cookie info, and then back in -- so that username change is displayed right away.

[HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> ChangeUserName(ManageUsernameViewModel model)
        {
            bool hasPassword = HasPassword();

            if (hasPassword)
            {
                if (ModelState.IsValid)
                {

                    //Get UserID
                    var userID = User.Identity.GetUserId();
                    var currentUserName = User.Identity.GetUserName();
                    var newUserName = model.NewUserName;

                    //Check if new username already exisits
                    if (UserManager.FindByName(model.NewUserName) != null)
                    {
                        ModelState.AddModelError(string.Empty, "That username already exist.");
                        return RedirectToAction("Manage", new { Message = ManageMessageId.DuplicateUserName });
                    }

                    var user = UserManager.Find(model.OldUserName, model.Password);
                    if (user != null)
                    {
                        //Change username
                        if (DataAccess.userDataAccess.updateUserName(model.NewUserName, userID))
                        {

                            AuthenticationManager.SignOut();
                            await SignInAsync(user, false);

                            var identity = new ClaimsIdentity(User.Identity);
                            identity.RemoveClaim(identity.FindFirst(identity.NameClaimType));
                            identity.AddClaim(new Claim(identity.NameClaimType, model.NewUserName));
                            AuthenticationManager.AuthenticationResponseGrant = new AuthenticationResponseGrant(new ClaimsPrincipal(identity), new AuthenticationProperties { IsPersistent = false });                            

                            return RedirectToAction("Manage", new { message = ManageMessageId.ChangeUserNameSuccess });
                        }
                        else
                        {
                            AddErrors(new IdentityResult("Failed updating your username."));
                        }
                    }
                    else
                    {
                        ModelState.AddModelError("", "Invalid username and password.");
                    }
                }
            }

            // If we got this far, something failed, redisplay form
            return View();
        }

Optimizing SSRS Rendering and Performance

2014-10-21T10:03:00.001-07:00

I often run into the situation of SSRS taking too long to render or a long delay before processing starts. Here are 3 links provided by Microsoft that might help in solving those inefficiencies.

Troubleshooting Reports: Report Performance

http://msdn.microsoft.com/en-us/library/bb522806.aspx

Exporting Reports

http://msdn.microsoft.com/en-us/library/ms157153.aspx

Understanding Rendering Behaviors

http://msdn.microsoft.com/en-us/library/bb677573.aspx

Somewhat Dynamic Pivot in C#

2014-10-11T12:07:00.001-07:00

I started working on a dynamic pivot to be used in Kendo UI, where the user could select the datapoints to be shown in the graph. Didn't quite finish, but here is some of the code, didn't want to waste/lose it; since, the concept was interesting.

Code:

public class StageDataPoints
    {
        public List<StageDataPoint> dataSeries { get; set; }

        public List<ExpandoObject> pivot()
        {
            var datapoints = dataSeries.Select(n => n.DataPoint).Distinct().ToList();

            var sdpGroup = from d in dataSeries
                           group d by new { d.DateTimeGrainValue, d.Stage, d.SubStage }
                               into grp
                               select new
                               {
                                   Key = grp.Key,
                                   Dict = grp.ToDictionary(n => n.DataPoint, n => n.DecimalValueAvg)
                               };

            List<ExpandoObject> defactoObjs = new List<ExpandoObject>();

            foreach (var row in sdpGroup)
            {
                dynamic r = new ExpandoObject();
                r.DateTime = row.Key.DateTimeGrainValue.ToString("M/d/yy h:mm");
                r.Stage = row.Key.Stage;
                r.SubStage = row.Key.SubStage;

                double value = double.NaN;
                string tempDP = "";

                foreach (var dp in datapoints)
                {
                    tempDP = dp;

                    foreach (var idp in row.Dict)
                    {
                        if (idp.Key == dp)
                        {
                            value = idp.Value;
                            break;
                        }
                    }
                    //Console.Write(dp + " " + value.ToString());
                    ((IDictionary<string, object>)r).Add(dp, value);
                }
                defactoObjs.Add(r);
            }
            return defactoObjs;
        }

        public List<StageDataPointMinify> rpt()
        {
            List<StageDataPointMinify> ret = new List<StageDataPointMinify>();
            foreach (var x in dataSeries)
            {
                ret.Add(new StageDataPointMinify()
                {
                    //Convert datetime to javascript verion
                    DateTimeGrainValueStr = x.DateTimeGrainValue.ToString("M/d/yy H:mm"),
                    DateTimeGrainValue = x.DateTimeGrainValue,
                    DataPoint = x.DataPoint,
                    DecimalValueAvg = x.DecimalValueAvg
                });
            }
            return ret;
        }
    }

    public class StageDataPoint
    {
        //public DateTime CalendarGrainValue { get; set; }
        //public string ClockGrainValue { get; set; }
        public DateTime DateTimeGrainValue { get; set; }
        public string Stage { get; set; }
        public string SubStage { get; set; }
        public string DataPoint { get; set; }
        //public Guid CJobGuid { get; set; }
        public string JobName { get; set; }
        public double DecimalValueAvg { get; set; }
        //public double DecimalValueMin { get; set; }
        //public double DecimalValueMax { get; set; }
    }

Pull what reports were ran and by whom.

2014-03-18T14:07:00.000-07:00

A very basic query to look at the ReportServer database to pull what reports there are, who has ran it, and how many times.

Code Snippet

SELECT 
c.NAME,
el.UserName,
el.LATEST_RUN_DATE,
el.NUM_TIMES_RAN,
c.[path]
FROM
DBO.[CATALOG] AS c
LEFT JOIN
(
    SELECT
    EL.REPORTID
    ,EL.UserName
    ,COUNT(EL.TIMESTART) NUM_TIMES_RAN
    ,MAX(EL.TIMESTART) AS LATEST_RUN_DATE
    FROM
    dbo.EXECUTIONLOG AS el
    GROUP BY
    el.REPORTID, EL.UserName
)el on el.ReportID = c.ItemID
WHERE
C.[TYPE] = 2
ORDER BY c.NAME, el.UserName

SSRS Report Width Limit Error

2014-02-04T12:13:00.001-08:00

Well, you can filed this error in the "probably won't ever happen to me" section, but this happen to me today. In SSRS there is a Width limit of 455 inches for the report section.

The error:

The value of the Width property for the report section 'ReportSection0' is "476.32297in", which is out of range. It must be between 0in and 455in.

This report, that I'm creating is very unique, in that the user can select from a list of views, and then select fields from those views. The report lists out all the fields, but only displays the selected fields. It's an interesting concept of allowing the user to access data via SSRS, mainly since the department isn't allowed to have cubes, data marts, etc... this ends up being the best solution, for now.

Query to help create RDL

2014-01-28T20:38:00.000-08:00

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

@view varchar(50)

)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--DECLARE @view varchar(50)

--SET @view = 'V_EVALUATIONS'

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME + '">

' + c.NAME + '

' +

CASE t.NAME

WHEN 'varchar' THEN 'System.String'

WHEN 'int' THEN 'System.Int32'

ELSE 'true'

END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'

FROM sys.schemas a

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id AND a.NAME = 'hrr'

INNER JOIN sys.columns c ON c.object_id = b.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

---COPY & PASTE INTO COLUMN SECTION --

SELECT

1.5in

' as 'COPY & PASTE INTO COLUMN SECTION -- '

FROM sys.schemas a

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id AND a.NAME = 'hrr'

INNER JOIN sys.columns c ON c.object_id = b.object_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

+ c.NAME + '">

true

' + REPLACE(c.NAME,'_',' ') + '

Textbox' + c.NAME + '

SteelBlue

2pt

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '

FROM sys.schemas a

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id AND a.NAME = 'hrr'

INNER JOIN sys.columns c ON c.object_id = b.object_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

+c.NAME+'">

true

=Fields!'+ c.NAME + '.Value

2pt

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '

FROM sys.schemas a

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id AND a.NAME = 'hrr'

INNER JOIN sys.columns c ON c.object_id = b.object_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

=IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '

FROM sys.schemas a

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id AND a.NAME = 'hrr'

INNER JOIN sys.columns c ON c.object_id = b.object_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

Query storage size on your tables or tables in the database.

2014-01-27T20:23:00.003-08:00

A helpful query on finding the size of your specific tables in SQL Server. Just replace with your string list of tables.

Code Snippet

SELECT
sum(RowCounts) as RowCounts,
SUM(TotalSpaceKB) AS TotalSpaceKB, 
SUM(UsedSpaceKB) AS UsedSpaceKB, 
SUM(UnusedSpaceKB) AS UnusedSpaceKB
FROM
(
    SELECT 
        sum(p.rows) AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    WHERE 
        t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255 
        AND t.NAME IN ( <TABLE_NAMES> )
    GROUP BY 
       p.Rows
)x

Or to get a list of all tables and their sizes in the database:

Code Snippet

SELECT 
    t.NAME as TableName,
    sum(p.rows) AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
   t.Name, p.Rows

TSQL Function: Count Work Days from two dates.

2014-01-22T14:19:00.000-08:00

A T-SQL function to calculate number of business days (work days) given two dates.
It uses CTE recursive to quickly get a row number count.

CREATE FUNCTION [hrr].[usf_GetWeekdayCount]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
BEGIN

IF(@StartDate IS NULL)
RETURN 1

IF(@EndDate IS NULL)
RETURN 1

DECLARE @FlipBitFlag bit
SET @FlipBitFlag = 0

IF(@StartDate > @EndDate)
BEGIN   DECLARE @Temp datetime
   SET @Temp = @StartDate
   SET @StartDate = @EndDate
   SET @EndDate = @Temp
   SET @FlipBitFlag = 1
END
DECLARE @WEEKDAYS_DAY AS INT
---Count up to 2^16 = 65536 real fast!!
;WITH N1 (n) AS
(
  SELECT 1

UNION ALL
  SELECT 1
),
N2 (n) AS
(
   SELECT 1 FROM N1 AS X, N1 AS Y
),
N3 (n) AS
(
   SELECT 1 FROM N2 AS X, N2 AS Y
),
N4 (n) AS
(
   SELECT 1 FROM N3 AS X, N3 AS Y
),
N5 (n) AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N4 AS X, N4 AS Y
)


SELECT @WEEKDAYS_DAY=SUM(1)
FROM N5
WHERE DATEADD(day,n-1,@startdate)<=@enddate
AND DATENAME(dw,DATEADD(day,n-1,@startdate)) NOT IN ('SATURDAY','SUNDAY')

IF(@FlipBitFlag = 1)
RETURN -1 * @WEEKDAYS_DAY

RETURN @WEEKDAYS_DAY

END
GO

--EDITED: Added check for nulls and reverse dates will give negative value.

IIF vs CASE in SQL SERVER 2012

2014-01-16T09:13:00.000-08:00

I've been told that behind the scenes, SQL Server 2012 converts IIF to CASE expressions. I didn't think that there wouldn't be that much of a performance issue. What I found in one of my queries is that if I ran the IIF first then the CASE statement. Each time I ran the IIF statement it ranged from 40 - 60 in the client processing time; meanwhile, the CASE expression would run in 10 - 30.

(Click on images to expand)

SSRS: Hide this column function

2013-11-13T19:40:00.000-08:00

A bit of code from SQL Server Magazine on handling column specific security based on userid that is found in SSRS.

Function HideThisColumnFrom(strUserID as String) as Boolean
   Select Case strUserID
      Case "domain\user1" : Return False
      Case "domain\user2" : Return False
      Case Else : Return True
   End Select
End Function

Not ideal, and it is suggested using SQL Server to house a table of users, etc....

I think it would be best to write a sql scalar function that accepts a userid and based on permissions/roles send back either a bit-string or a string of roles separated by a delimiter which then can be deciphered by a function on the SSRS side. The function then would determine by sending in the roles into the function and return the True/False.

An error occured while attempting to start the report preview worker process.

2013-11-06T11:57:00.000-08:00

"An error occured while attempting to start the report preview worker process."

Ran into this problem when using runas with SSDT. As a consultant, the runas is useful for connecting to a customer's domain and sending the necessary credentials without setting the laptop to be on the customer's domain.

The workaround, is to instead, right-click the report and select "run".

Source:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f717c592-5022-43e3-9872-c10c1cf7f653/ssdt-2012-report-preview-not-working?forum=sqlreportingservices&prof=required

Helpful Resources on SQL & Development

2013-09-20T12:56:00.000-07:00

Going old school (Early 2000-ish) with a list of links on a page. Oh, Yeah!!
(No particular order -- will build on)

SQL:

SQL Bits (Video) http://sqlbits.com/
Brent Ozar Unlimited (Video & Blog) http://www.brentozar.com/
Strictly SQL(Blog) http://strictlysql.blogspot.com/
SQL Blog (Blog) http://sqlblog.com/
BI SQL Pass http://bi.sqlpass.org/
SQL Server Central http://www.sqlservercentral.com/
DBA Stack Exchange http://dba.stackexchange.com/
Katie & Emil (Video & Blog) http://www.katieandemil.com/
SQL Server Pro http://sqlmag.com/
SQL Pass Session Recordings (Videos)
http://www.sqlpass.org/LearningCenter/SessionRecordings.aspx
List of SQL MVP Blogs: http://technet.microsoft.com/en-us/sqlserver/bb671052.aspx

.Net

Channel 9 (Video) http://channel9.msdn.com/

Microsoft Virtual Academy (Video) http://www.microsoftvirtualacademy.com/

Scott Hanselman http://www.hanselman.com/blog/
Stack Overflow http://stackoverflow.com/

Comic Relief:

Midnight DBAs (Video) http://midnightdba.itbookworm.com/
The DailyWTF http://thedailywtf.com/
XKCD http://xkcd.com/
Average Data Miner (My Tumblr Account) http://avgdataminer.tumblr.com/
DBA Reaction http://dbareactions.tumblr.com/
Dev Ops Reaction http://devopsreactions.tumblr.com/
TSql Jokes http://tsqljokes.tumblr.com/

MISC

Free School Courses Online https://www.coursera.org/

MIT Free Courses Online: http://ocw.mit.edu/courses/find-by-topic/

Stanford Free Online Courses: http://online.stanford.edu/courses/

An auto generating SQL Merge stored procedure for Type 2 SCD

2013-09-11T10:31:00.002-07:00

This could shave off some time in generating SQL Merge statement by hand. This helpful stored proc. will auto generate your merge statement on tables that use Type 0,1, and 2 SCD. The dim table just needs to be setup to use extended properties.

He uses the schema dim and etl to represent the dimension table and staging table, respectfully. The field IsRowCurrent is used to represent the current updated row.

http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/
http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd/

Code Snippet

/***************************************************************
*                                                              *
*   Script for use with blog post                              *
*     "Automating T-SQL Merge to load Dimensions (SCD)"        *
*     http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd
*                                                              *   
*   Posted: 6th April 2012                                     *
*                                                              *
*   By: Alex Whittles - Purple Frog Business Intelligence      *
*       www.PurpleFrogSystems.com                              *
*                                                              *
*   All code samples are provided “AS IS” without warranty of  *
*   any kind, either express or implied, including but not     *
*   limited to the implied warranties of merchantability       *
*   and/or fitness for a particular purpose.                   *
*                                                              *
***************************************************************/
 
--The generate merge proc
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateMerge]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenerateMerge]
GO
 
CREATE PROCEDURE [dbo].[GenerateMerge]
    @Dimension  varchar(50),
    @Schema     varchar(50)='dim',
    @ETLSchema  varchar(50)='etl',
    @Execute    bit=0  --Should the resulting merge be returned or executed
AS
BEGIN
    SET NOCOUNT ON;
 
    --Create Carriage return variable to help format the resulting query
    DECLARE @crlf char(2)
    SET @crlf = CHAR(13)
 
 
    --Find out which Audit fields are used
    DECLARE @UseIsInferred bit
    DECLARE @UseValidFrom bit
    DECLARE @UseValidTo bit
    DECLARE @UseIsRowCurrent bit
    DECLARE @UseLastUpdated bit
    
    SET @UseIsInferred = ISNULL((SELECT MAX(1) 
    FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE       s.name = @Schema
            AND t.name = @Dimension
            AND c.name = 'IsInferred'
    ),0)
 
    SET @UseValidFrom= ISNULL((SELECT MAX(1) 
    FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE       s.name = @Schema
            AND t.name = @Dimension
            AND c.name = 'ValidFrom'
    ),0)
 
    SET @UseValidTo = ISNULL((SELECT MAX(1) 
    FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE       s.name = @Schema
            AND t.name = @Dimension
            AND c.name = 'ValidTo'
    ),0)
 
    SET @UseIsRowCurrent = ISNULL((SELECT MAX(1) 
    FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE       s.name = @Schema
            AND t.name = @Dimension
            AND c.name = 'IsRowCurrent'
    ),0)
 
    SET @UseLastUpdated = ISNULL((SELECT MAX(1) 
    FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE       s.name = @Schema
            AND t.name = @Dimension
            AND c.name = 'LastUpdated'
    ),0)
 
 
    --Identify the business key column(s)
    --Also define what the null replacement should be
    DECLARE myCurBK Cursor FOR 
    SELECT c.name
        , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
            WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
            WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
            ELSE 'NULL' END AS NullRep
    FROM sys.columns c
        INNER JOIN sys.tables t on c.object_id = t.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
        INNER JOIN sys.extended_properties ep 
             ON t.object_id=ep.major_id
            AND c.column_id=ep.minor_id
            AND ep.class=1
            AND ep.name='SCD'
    WHERE s.name = @Schema
        AND t.name = @Dimension
        AND ep.value = 'BK'
    ORDER BY c.column_id
    
    --Identify all fields to be merged (Exclude Type 0)
    DECLARE myCurType1 Cursor
    FOR SELECT c.name
        , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
            WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
            WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
            ELSE 'NULL' END AS NullRep
        FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
            LEFT JOIN sys.extended_properties ep 
                 ON t.object_id=ep.major_id
                AND c.column_id=ep.minor_id
                AND ep.class=1
                AND ep.name='SCD'
        WHERE s.name = @Schema
            AND t.name = @Dimension
            AND c.is_identity=0
            AND ISNULL(ep.value,'1') NOT IN ('0', 'Audit', 'BK')
        ORDER BY c.column_id ASC
 
    --Identify all fields for insert
    DECLARE myCurAll Cursor
    FOR SELECT c.name
        , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
            WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
            WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
            ELSE 'NULL' END AS NullRep
        FROM sys.columns c
            INNER JOIN sys.tables t on c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
        WHERE s.name = @Schema
            AND t.name = @Dimension
            AND c.name NOT IN ('LastUpdated', 'IsInferred', 'ValidFrom', 'ValidTo', 'IsRowCurrent')
            AND c.is_identity=0
        ORDER BY c.column_id ASC
 
    DECLARE @Field varchar(255)
    DECLARE @NullRep varchar(20)
    
    DECLARE @SQL varchar(max)
    DECLARE @SQL2 varchar(max)
    DECLARE @SQL3 varchar(max)
 
    --Now start building up the dynamic SQL
    
    SET @SQL =                'MERGE      [' + @Schema + '].[' + @Dimension + '] AS Target'
    SET @SQL = @SQL + @crlf + '    USING [' + @ETLSchema + '].[' + @Dimension + '] AS Source'
    
    OPEN myCurBK
    FETCH NEXT FROM myCurBK INTO @Field, @NullRep
    IF (@@FETCH_STATUS>=0)
        BEGIN
            SET @SQL = @SQL + @crlf + '       ON Target.' + @Field + ' = Source.' + @Field
            FETCH NEXT FROM myCurBK INTO @Field, @NullRep
        END
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF (@@FETCH_STATUS<>-2)
            SET @SQL = @SQL + @crlf + '       AND Target.' + @Field + ' = Source.' + @Field
        FETCH NEXT FROM myCurBK INTO @Field, @NullRep
    END
    CLOSE myCurBK
 
    IF @UseIsRowCurrent>0   SET @SQL = @SQL + @crlf + '       AND Target.IsRowCurrent=1'
    
    SET @SQL = @SQL + @crlf + '    WHEN MATCHED'
    
    OPEN myCurType1
    FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
    
    IF (@@FETCH_STATUS>=0)
        BEGIN
            SET @SQL = @SQL + @crlf + '       AND (ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
            FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
        END
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF (@@FETCH_STATUS<>-2)
            SET @SQL = @SQL + @crlf + '       OR ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
        FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
    END
    CLOSE myCurType1
    
    SET @SQL = @SQL + @crlf + '      )'
    SET @SQL2 = '    THEN UPDATE SET'
 
    OPEN myCurType1
    FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
    IF (@@FETCH_STATUS>=0)
        BEGIN
            SET @SQL2 = @SQL2 + @crlf + '       ' + @Field + ' = Source.' + @Field
            FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
        END
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF (@@FETCH_STATUS<>-2)
            SET @SQL2 = @SQL2 + @crlf + '      ,' + @Field + ' = Source.' + @Field
        FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
    END
    CLOSE myCurType1
    
    IF @UseLastUpdated>0   SET @SQL2 = @SQL2 + @crlf + '      ,LastUpdated = GetDate()'
    
    SET @SQL3 = '    WHEN NOT MATCHED THEN'
    SET @SQL3 = @SQL3 + @crlf + '         INSERT ('
    
    OPEN myCurAll
    FETCH NEXT FROM myCurAll INTO @Field, @NullRep
    IF (@@FETCH_STATUS>=0)
        BEGIN
            SET @SQL3 = @SQL3 + @crlf + '           ' + @Field
            FETCH NEXT FROM myCurAll INTO @Field, @NullRep
        END
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF (@@FETCH_STATUS<>-2)
            SET @SQL3 = @SQL3 + @crlf + '           ,' + @Field
        FETCH NEXT FROM myCurAll INTO @Field, @NullRep
    END
    CLOSE myCurAll
    
     
    IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,IsInferred'
    IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,ValidFrom'
    IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,ValidTo'
    IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,IsRowCurrent'
    IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,LastUpdated'
    SET @SQL3 = @SQL3 + @crlf + '         ) VALUES ('
    
    OPEN myCurAll
    FETCH NEXT FROM myCurAll INTO @Field, @NullRep
    IF (@@FETCH_STATUS>=0)
        BEGIN
            SET @SQL3 = @SQL3 + @crlf + '            Source.' + @Field
            FETCH NEXT FROM myCurAll INTO @Field, @NullRep
        END
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF (@@FETCH_STATUS<>-2)
            SET @SQL3 = @SQL3 + @crlf + '           ,Source.' + @Field
        FETCH NEXT FROM myCurAll INTO @Field, @NullRep
    END
    CLOSE myCurAll    
    
    IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,0'
    IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
    IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,NULL'
    IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,1'
    IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
    SET @SQL3 = @SQL3 + @crlf + '         );'
    
    --clean up
    DEALLOCATE myCurType1
    DEALLOCATE myCurAll
    DEALLOCATE myCurBK
    
    IF @Execute = 1
    BEGIN
        EXEC(@SQL + @SQL2 + @SQL3)           
    END
    ELSE
    BEGIN    
        PRINT @SQL
        PRINT @SQL2
        PRINT @SQL3
    END
    
END
 
GO

An example of the extended properties being set through sql statement:

Code Snippet

EXEC sys.sp_addextendedproperty @level2name=N'CustomerKey',  @value=N'PK' ,    
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'Email',        @value=N'BK' ,    
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'FirstName',    @value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'LastName',     @value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'DoB',          @value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'Sex',          @value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'MaritalStatus',@value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'FirstCreated', @value=N'1' ,     
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'ValidFrom',    @value=N'Audit' , 
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'ValidTo',      @value=N'Audit' , 
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'IsRowCurrent', @value=N'Audit' , 
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
EXEC sys.sp_addextendedproperty @level2name=N'LastUpdated',  @value=N'Audit' , 
    @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim', 
    @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'

An example of printing the script:

Code Snippet

EXEC dbo.GenerateMerge
    @Dimension = 'Customer',
    @Schema = 'dim',
    @ETLSchema = 'etl',
    @Execute = 0

Quick Reminder: How to find a field in a database, within SQL Server

2013-07-02T08:27:00.004-07:00

select
t.name as table_name,
SCHEMA_NAME(schema_id) as schema_name,
c.name as column_name
from sys.tables as t
inner join sys.columns c on t.object_id = c.object_id
where c.name like '%Some Field Name%'
order by schema_name, table_name;

Problems of not having query hints allowed in Views

2013-06-28T09:58:00.000-07:00

So, working with Tableau, you're not given the option to use stored procedures, and I don't like to put queries inline within the code, so I create views for the reports to pull data from. The problem I have is that the query I'm running, is doing a count, which ends up doing in parallel; giving erroneous totals. No problem, not the first time I ran into this problem, just use OPTION (MAXDOP 1).

The problem with that, is Views are not allowed to have query hints. So the next possible solution would be to call the option with the view:

SELECT * FROM [dbo].[View1]
OPTION (MAXDOP 1);

This would work, but since I'm using Tableau; it just errors out when I try to add the option.

So, is there another path? The next possible, semi-working option is to create a "plan guide" for the query. (More info found at: http://msdn.microsoft.com/en-us/library/ms179880.aspx)

Example:
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT * FROM [dbo].[View]',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';

This works, if the query executed is the exact same as in the statement. So, if there is an extra space, or different formatting on the query this guide would not be called. Luckily, I'm the only one reports developer, this should be fine -- as long as no one messes with the query and that I have ALTER permissions on the databases that this will be placed on.

There are problems with this solution, but first to see the plan guides, you can use this query:

SELECT * FROM sys.plan_guides

Since, only 1 plan guide is allowed to be enabled for a query, and worst of all -- the possible future errors:
"Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error."

So, if you ended up using the Object type, instead of the Sql type you will surely need to know how to drop a plan:

EXEC sp_control_plan_guide N'DROP', N'Guide1'

I still have a feeling in my bones that there is an easier way, or at least there should be. It shouldn't have to be this ugly.

Exporting to Excel in SSIS

2013-03-29T14:36:00.000-07:00

How to export data to excel. I did follow other blogs, but they leave out vital information to get it working, so here is my attempt.

Preview of what's to come from Control Flow view:

You're going to need to first create a Execute SQL Task, because this is going to be used to create your sheet in excel. The syntax is similar to SQL Server's Create Table, except that it uses the accent quote ` instead of squaring the names in brackets i.e.: [name]. It also seems to have a problem with numeric and character cell size limits.

In this case, I'm creating an excel sheet called Process:

CREATE TABLE `Process`(
`SuperClientVendorID` INT,
`LoanNumber` varchar(25),
`OpenIndicator` char(3),
`CloseIndicator` char(3),
`UpdateIndicator` char(3),
`ParentRefID` INT,
`Open_RailDescription` varchar(200),
`AssignedVendorID` INT ,
`CloseReason` varchar(200) ,
`RefID` numeric(18, 0),
`CloseDate` date ,
`InheritedAttribute` varchar(3) ,
`ProcessorCd` varchar(50) ,
`ProcessStartDate` date ,
`ReOpenIndicator` varchar(3) ,
`NoteType` varchar(255) ,
`Note` text
)

Now, lets create a Excel Connection Manager to dynamically create our file. You're going to need a dummy file to initially create an connection instance. In this dummy file, I had my first rows of names, but I'm not sure if this is really needed.

You're going to point the connection to the excel file for now. Right click on the newly created connection and select properties. In this window, under expressions you're going to create a ExcelFilePath expression:

My text, I use a variable called Processed to represent the file path and folder. I then add a datetime string to the end of the file.

@[User::Processed] + "\\FileName " + RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) +
RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2) + (DT_STR,4,1252)DATEPART("YYYY" ,GETDATE()) + "_" + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" + Right("0" + (DT_STR,4,1252) DatePart("n",getdate()),2) +""+ ".xls"

I had a problem with the connection at this point, I took the ExcelFilePath name from properties and moved my dummy excel there and renamed it. This won't be a problem when it runs, since the name will change given the time. If you need to edit the file, you will need to recreate/rename your dummy file.

Now create a Data Flow Task and inside that task, create your source file, where you will be pulling the data from. Setup your query to pull the data, etc.... And create your destination excel file.

Point it to your dummy excel file and dummy sheet name.

Run the program, and hope for no errors.

Helpful resources on the same thing:
http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/
http://jandho.blogspot.com/2012/03/ssis-package-to-export-to-new-excel.html

Excel Error in SSIS - 64bit runtime error

2013-03-29T09:26:00.002-07:00

Running a very simple SSIS 2012; which pulls data from an excel file. I've done this a million times, but all of a sudden I ran into this tidbit when running on a new machine:

Errors:

[Excel Source [39]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[SSIS.Pipeline] Error: Excel Source failed validation and returned error code 0xC020801C.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
[Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

On the blogs, people say to download the 64-bit version of the drivers at: http://www.microsoft.com/en-us/download/details.aspx?id=13255
I still had trouble getting it to work, so I changed it to run "not" in 64-bit. Just go to Project/[Name of Project] Properties/Configuration Properties/Debugging under Debug Options and change "Run64BitRuntime" from default value of true to false.

This also means, if you are going to run it in 32-bit, you also have to tell the SQL Agent to run in 32-bit as well. Edit your job and then edit the right job step. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this job step.

Quick Reminder: Add 3rd PartyJavascript API Intellisense to Visual Studio

2013-03-22T09:38:00.002-07:00

For third party JavaScript api, it helps to have intellisense. In this case I was using Tableau Software's JavaScript API.

1) Download the JavaScript files and place them locally.
2) Add reference at top of your JavaScript file
/// <reference path="tableau_v8.debug.js" />
Before:

After:

More info on creating intellisense can be found here: http://msdn.microsoft.com/en-US/library/vstudio/hh874692.aspx