BumbleBI launched a new service provider called:
SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions.
Our servers are hosted in EasyNet’s datacenter at Schiphol-Rijk (Amsterdam, The Netherlands) which is directly connected to one of the world’s fastest internet hubs: Amsterdam Internet Exchange (AMS-IX).
Ideal for smaller databases without paying the expensive licensing costs.
Or for the large corporations for world-wide database access for free usage of Excel, PowerPivot, Power View and/or tablet apps.
Our Microsoft Business Intelligence (BI) consultants can support deploying your data to this cloud-environment (near realtime of by a daily import job).
The databases of these software suites can be made available in our SQL Hotel environment for analysis, reporting, data mining and more:
sys.tables provides one row for each table in a database. This does include user tables and system tables that exist in each database. There is a column within sys.tables: [is_ms_shipped] that identifies system tables. This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.
sys.all_columns offers a row for each column for every object in a database. Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.
sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties. The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]
First a little background information.
The project is called ‘DLT Containment’, here’s a short quote from the PID: DLT stands for Die Level Traceability – this system contains all genealogy for our LED’s from EPI Reactor run through the LED location in the end-product we ship to a customer (full traceability).
I made a SSIS package that processes the logging files…BUT…they contain headers (see below).
It’s actually based upon the T-SQL code from my previous post.
First I’ll show the Destination table design:
Nothing that difficult:
– FST_Lot_ID = (believe it or not) WO NO
– FST_SubLot_ID = LOT NO
– FST_Date – Current datetime (smalldatetime..Grrrr)
I was struggling for quite a time on how I should do it.
After minutes (maybe an hour), I got an idea and worked on it.
Here’s the result of my brainstorm session: