Useful short reference Here, on the difference between DT_DATE, DT_DBDATE and DT_DBTIMESTAMP using Derived Column transformations, and visualised through SSIS Data Viewer… I’ll tell you what the effect is !
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 );
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.
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.
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
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.
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.
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.
6. In Project Explorer, right-click the project, and then click Properties.
9. You should now be able to Build your code without error.
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|
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;