SQL Table/CTE for SQL Server, SSIS and Biml data types

Cathrine Wilhelmsen posted an easy lookup table for the data types within SQL Server, SSIS and Biml.

For loading the meta-data information based on source tables, I find it handy to provide all three data types in the result set of the query. So here’s an easy Common Table Expression / Table Value Constructor which might help you also.


WITH DataTypeMatrix (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
AS
(
SELECT *
FROM
(
VALUES
('bigint','DT_I8','Int64')
,('binary','DT_BYTES','Binary')
,('bit','DT_BOOL','Boolean')
,('char','DT_STR','AnsiStringFixedLength')
,('date','DT_DBDATE','Date')
,('datetime','DT_DBTIMESTAMP','DateTime')
,('datetime2','DT_DBTIMESTAMP2','DateTime2')
,('datetimeoffset','DT_DBTIMESTAMPOFFSET','DateTimeOffset')
,('decimal','DT_NUMERIC','Decimal')
,('float','DT_R8','Double')
,('geography','DT_IMAGE','Object')
,('geometry','DT_IMAGE','Object')
,('hierarchyid','DT_BYTES','Object')
,('image','DT_IMAGE','Binary')
,('int','DT_I4','Int32')
,('money','DT_CY','Currency')
,('nchar','DT_WSTR','StringFixedLength')
,('ntext','DT_NTEXT','String')
,('numeric','DT_NUMERIC','Decimal')
,('nvarchar','DT_WSTR','String')
,('real','DT_R4','Single')
,('rowversion','DT_BYTES','Binary')
,('smalldatetime','DT_DBTIMESTAMP','DateTime')
,('smallint','DT_I2','Int16')
,('smallmoney','DT_CY','Currency')
,('sql_variant','DT_WSTR','Object')
,('text','DT_TEXT','AnsiString')
,('time','DT_DBTIME2','Time')
,('timestamp','DT_BYTES','Binary')
,('tinyint','DT_UI1','Byte')
,('uniqueidentifier','DT_GUID','Guid')
,('varbinary','DT_BYTES','Binary')
,('varchar','DT_STR','AnsiString')
,('xml','DT_NTEXT','Xml')
) AS F (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
)

SELECT *
FROM DataTypeMatrix




Need to re-format the T-SQL code? Use the Notepad++ plugin ‘Poor Man’s T-Sql Formatter

 


 


 

Advertisements

MDS: How to create or deploy a package from a model

A few lines I need to remember for MDS 🙂

CREATE:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model “Biml meta-data” -version “VERSION_1” -package “C:\MDS\BimlMetaData_20170614.pkg” -includedata

DEPLOY:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy deployclone -package “C:\MDS\BimlMetaData_20170614.pkg” -service MDS1

MDS Create package from model

SSRS Drill-through: passing multiple values to another report (MDX list of values)

When I build large dashboards, I often fall back to basic stored procedures to combine datasets, but also to be able to manipulate parameter values (month, first week, previous week, previous year totals, etc.).
An annoying thing is that when you want to create a drill-though action to another report which uses MDX, you’ll need to magically join these values.

Well…here’s how you could achieve it.
Let’s say I have these three values (keys): XXX, YYY and ZZZ

MDX expects a prefix and a suffix (according to the dimension properties/levels).
So here’s what MDX expects:

[Dim Customer].[Customer Number].&[XXX]
[Dim Customer].[Customer Number].&[YYY]
[Dim Customer].[Customer Number].&[ZZZ]

Important: MDX expects different objects/rows, like you need to use separate rows in VS/SSDT for the default parameter values.

Your dataset returns the basic key values, first step is to join these (including the prefix and suffix):

="[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]"

The results is:

[Dim Customer].[Customer Number].&[XXX],[Dim Customer].[Customer Number].&[YYY],[Dim Customer].[Customer Number].&[ZZZ]

Which seems correct, but MDX isn’t expecting a multi-valued string (compared to SP’s).

Final thing, you’ll need to do is to split it again in order to provide a record/row per value:

=Split("[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]",",")








Prepare for your 70-461 exam?

Recently I passed the 70-461 exam and I must say this blog post helped me preparing for it:
Passed 70-461 Exam : Querying Microsoft SQL Server 2012

Here by this post I want to provide my readers some information and knowledge about this Exam, so that they can plan well for this and look forward to take up this exam.

–> Exam Highlights:

– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.

–> The Exam is divided into 4 modules:
1. Create Database Objects
2. Work with Data
3. Modify Data
4. Troubleshoot & Optimize



Want to find out what will be tested during the exam? Manoj posted a detailed break-down with all the subjects in the exam:
Passed 70-461 Exam : Querying Microsoft SQL Server 2012





How to determine the maximum length / value for each column in your database?

You might want to use this query (found it here) and made a few modifications. It’s an older query, but this may point you in the right direction.

DECLARE @Results table
(
ID uniqueidentifier,
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
NumericPrecision varchar(250),
DatetimePrecision varchar(250),
IsNullable varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @Results
SELECT
NEWID(),
t.TABLE_SCHEMA,
t.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE
WHEN DATA_TYPE NOT IN ('nvarchar','varchar') THEN 'N/A'
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
END,
NUMERIC_PRECISION,
DATETIME_PRECISION,
IS_NULLABLE,
'NA',
'SELECT Max(Len(' + COLUMN_NAME + ')) FROM ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
DECLARE @receiver table(theCount int)

DECLARE length_cursor CURSOR
FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)

UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE ID = @id

DELETE FROM @receiver

FETCH NEXT FROM length_cursor
INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
NumericPrecision,
DatetimePrecision,
IsNullable,
Longest
FROM
@Results