Category Archives: ETL and ELT

Create a Date Table in Oracle

Occasionally you may be working with non Microsoft data sources such as Oracle over front end tools like Power Pivot or Reporting Services.

Some options for a date table include creating a linked table in Power Pivot or generating a table in the data source. Generate a date table quickly using your Oracle data source with the following example which generates a virtual date table, one row from the sample date of 11th April 2013 until 720 days after…

SELECT
       TO_NUMBER (TO_CHAR (TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'), 'yyyymmdd')) AS date_key,
n AS Date_ID,
TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
FROM (
select level n
from dual
connect by level <= 720
);

SQL Server Data Compare

To download the admin install go to http://msdn.microsoft.com/en-us/data/hh297027, download the ssdtsetup executeable for SQL Server 2014, and also vs2010, vs2012, vs2013.  From the command prompt run ssdtsetup /layout <destination> where <destination> is the path of the USB stick / network / local drive you want to install to.

This may take a while to download the full install files.

If that’s all a bit to hard just smell this cool SSDT feature here http://msdn.microsoft.com/en-us/library/dn266029(v=vs.103).aspx.

The entry point is pretty simple, just right click your database and select Database Comparison.

datacompare

 

 

 

SSIS: VS 2012 One or more projects in the solution were not loaded correctly

The following workaround http://up10it.blogspot.com.au/2013/07/VS2012-projects-not-loading.html was tested successfully for SQL Server Data Tools for Visual Studio 2012.   You come across the error “One or more projects in the solution were not loaded correctly” on opening a SSIS solution file in SQL Server Data Tools for Visual Studio 2012 and then encounter a head scratching moment.

OneorMoreProjects

 

Steps taken to work around :

1. Close SQL Server Data Tools for Visual Studio 2012

2. Navigate to the C:\Users\<Your UserName>\AppData\Local\Microsoft\VisualStudio\11.0

3. Rename folder ComponentModelCache to ComponentModelCache.old

4. Re-open SQL Server Data Tools for Visual Studio 2012

5. Open the Solution again

SSIS: Execution History Catalog Dashboard To Go

The SSISDB contains a wealth of statistical data for your SSIS package executions and messages.   In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious.  For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables and two datasets.  The two datasets are outlined in more detail below.

Screen Shot 2014-08-07 at 10.23.02 pm

The first dataset takes the last 3 days worth of execution history for your packages as per the top table. Whilst this dataset returns one row per execution as displayed, adjusting the groupings in this query will give you interesting perspectives on Average Duration and Execution Count.

The second table contains the last 15 messages.  In a failure scenario the second dataset will typically hold the most recent failure if no other packages have been run subsequent to the failure.  However you may need to adjust the dataset to suit, where TOP is specified, or parameterise as required – add a little Dijon mustard 🙂

Execution Summary – Last 3 Days

SELECT        folder_name, COUNT(*) AS tally_executions, SUM(DATEDIFF(ms, start_time, end_time))/1000 AS total_duration_s, package_name,
                         CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly'
                          WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' WHEN 9 THEN 'Completed' END AS Status, start_time
FROM            catalog.executions AS e
WHERE start_time > getdate()-3
GROUP BY folder_name, package_name, status, start_time
ORDER BY start_time DESC

Message Detail – Last 15 Messages

SELECT        TOP (15) event_message_id, message, package_name, event_name, message_source_name
FROM            (SELECT        event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,
                                                    event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,
                                                    message_code
                          FROM            catalog.event_messages AS em
                          WHERE        (operation_id =
                                                        (SELECT        MAX(execution_id) AS Expr1
                                                          FROM            catalog.executions)) AND (event_name NOT LIKE '%Validate%')) AS q
ORDER BY message_time DESC​

Here’s a couple of chilly reads that go a little deeper.

http://sqlblog.com/blogs/jamie_thomson/archive/2012/10/17/querying-the-ssis-catalog-here-s-a-handy-query.aspx

http://www.rad.pasfu.com/index.php?/archives/79-SSIS-Catalog-Part-6-Operations.html

SSIS: Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed.

You may have come across this error if you try to program against web services in your SSIS packages.

“Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed”.   If your I.T. Department won’t let you install the SDK and you are out of Freddo frogs, follow these instructions to navigate to and disable the Generate Serialization Assembly option.

1. Open SQL Server Business Intelligence Development Studio or SQL Server Data Tools.
2. Open the project file or the solution file.
3. In Solution Explorer, double-click the SSIS Package to open the Package Designer.
4. Double-click Script Task in the Package Designer to open the Script Task Editor dialog box – Click Edit Script.

ScriptTask

6. In Project Explorer, right-click the project, and then click Properties.

ProjectProperties
7. Click the Build tab. In the Output area, select Off in the Generate serialization assembly list.

BuildTab
8. Make sure you Save Selected Items before you exit.

SaveSelectedItems

 

9. You should now be able to Build your code without error.

TSQL : Convert a String to Pad Leading Zeros

Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front of the raw product codes. e.g. the following table shows 3 different examples demonstrating how the different lengths of data in the raw code column require more or less leading zeros (maximum required code length of 4 characters) for the Required Code column :

Raw Code Required Code Number of Leading Zeros to Pad
3 0003 3
187 0187 1
5455 5455 0

The following snippet demonstrates how Jose managed a year’s supply of free Fajitas by padding leading zeros in front of the product codes using the REPLICATE function :

SELECT
REPLICATE('0', 4-LEN(a.[Raw Code])) + CAST(a.[Raw Code] as varchar(4)) as [Required Code]
FROM dbo.table;