Showing posts with label TSQL2sday. Show all posts
Showing posts with label TSQL2sday. Show all posts

Tuesday, April 10, 2018

TSQL2SDAY 101: Essential SQL Server Tools

I've decided to jump onto the bandwagon and submit and article for this T-SQL Tuesday. This month, Jens Vestergaard (b|t) is hosting, and he's asked us to post about "The Essential SQL Server Tools in my Stack". These are the tools in addition to SSMS and Visual Studio Data Tools which we use for every day SQL activities.

In case you're new to T-SQL Tuesday, it is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for a very long time - we're currently on #101.

LadyRuna's Essential SQL Server Tools

As a consultant, I am often in the situation where I cannot make changes to the system I am using, so I frequently have to work with whatever they provide. Sometimes I'm lucky and they provide a few of my favorite tools, and other times not so much. Therefore, the tools I'm listing are the ones most frequently available to me in my consulting gigs.

Notepad ++


I hear you laughing, but think about it: you're tasked to create an SSIS package to import a flat file into your SQL server. Before you do anything in SSIS, you have to look a the file so you know whether the package will require any data transformations prior to loading the content into the SQL table.

If you open the file in Excel, it may automatically change the data types incorrectly (a good example is changing an account number that begins with 00 to an integer. That action removes the leading zeros, essentially modifying the account number's value), or it may reformat values in an undesirable format (changing phone number to scientific notation). If you open it in Notepad++, you will see exactly what the source system output and you will be able to accurately map all of the data correctly the first time. 

Notepad++ also offers context highlighting for various programming languages, a spell-checker, and prompts for frequently typed words (the list seems to grow the more you use it). Notepad++ highlights pairs of parenthesis, brackets, and braces; toggles comments; gracefully handles indents; and has fantastic searching capabilities. I like it enough that I find I use Notepad++ more than SSMS for many coding activities.

Poor Man's T-SQL Formatter


This is a free open-source downloadable T-SQL formatter available for SSMS, Visual Studio, and Notepad++ . It provides enough formatting options to satisfy most of the T-SQL formatting standards I've seen. If you haven't tried it yet, please download it and see for yourself.

Poor Man's SQL Formatter in Notepad++

Poor SQL


This is a free, online SQL formatter. Since there is nothing to download or install, I use this to format my SQL when I'm working in an environment where I don't have access to any other formatter and I want my code to be formatted consistently and / or in compliance with the customer's style. I have not yet encountered a situation where this option was blocked.

Task Factory


Some of the Task Factory Components
Until I started working for Pragmatic Works, I'd never used Task Factory when developing SSIS packages, but now that I do, I've found it actually makes working with SSIS pleasant.

Task Factory has over 60 SSIS components which are optimized to run faster than the native SSIS components. The Task Factory components handle many tasks which previously could only be handled using custom script components - allowing users to avoid the nightmare of debugging and rewriting script components for different environments.

BI xPress


Yet another Pragmatic Works product that I wish I knew about years ago is BI xPress. It includes auditing and notification frameworks which can easily be applied to any SSIS package to provide consistent and efficient auditing of SSIS package runs.

Once the auditing framework has been applied to a package, the SSIS Monitoring shows details about each run - including the values of variables at each component and, if the package is still running, which step it is currently running. I've found it's great for debugging packages that "worked on my machine" but aren't working properly in the SSISDB. I can see everything about the package in the SSIS Monitor in a view similar to what I'd see when running the package in Visual Studio.

There are more features to BI xPress than that, but in my experience those features are the ones I go to most often.




DOC xPress


One more Pragmatic Works product I wish the companies I worked for years ago had is DOC xPress. Its main function is to create documentation of SQL Server, SSAS, SSIS, SSRS, Excel, Oracle, and other database-based systems. It does this by pulling metadata about each of the objects and compiling it into a single cohesive unit. The documentation can be output into CHM help file, HTML, RTF or Word Documents.

I've found this is a fantastic device for learning about a new database system because all of the information is available in one spot. If a change occurs on a database that has been documented, after taking a new snapshot, the "before" version of the database can be compared to the "current" version to see what has changed.

DOC xPress even has a "lineage" feature which allows users to answer the question "Where did this field originate?". This means you can potentially pick a field in a table in the SSAS cube, find the SSIS package that loaded the cube, and trace that field back to the source database where it originated. Tracing the "lineage" with this tool is many times more efficient than the old fashioned method of getting a pre-dawn phone call about a process failure because someone made a change and didn't find all of the downstream effects of that change.



Monday, February 19, 2018

T-SQL Tuesday #99 - A Passion for Sewing

This post is part of T-SQL Tuesday, a monthly blog party on the second Tuesday of each month which was started back in 2009. This month's host is Aaron Bertrand (B|T), who presented us with a choice between two possible topics - something non-technical you're passionate about or t-SQL bad habits. I know I'm a week late, but I didn't see the topic notification until today, so I thought I'd create a post anyway because I needed to resurrect my blog and this post was a good excuse for reviving it.
Honor Harrington

My passion outside of the SQL server community
is sewing. Much of my earlier sewing was costumes for myself which I'd wear to Science Fiction, Anime, and Steampunk conventions. Over the years, I've entered - and won - numerous costuming awards at the conventions for my creations.
Katamari!
Sesshoumaru

I soon realized that the reason I preferred wearing costumes wasn't simply because they were unusual garments but because they fit much better than the clothing I could purchase at stores. This led me
Neo and Trinity
down the road of making nearly every garment I have in my closet. I've made some shirts for my husband, sewn curtains for the house, recovered the sofa cushions, made pillows, sewn purses and totes, and made quilts.
Pumpkin Scissors
SQL Cruise formal dress



I also collect antique and modern sewing machines. Currently I have 15 machines, including a 15-needle embroidery machine. Sewing is rather addictive - once you find out how easy it is, you can let your creativity fly and make anything you desire.
Redline 1501
Singer 201
Willcox & Gibbs

Tuesday, January 11, 2011

t-sql-tuesday-resolutions


TSQL2sday Linkback

Happy TSQL Tuesday Everyone!

This week's TSQL2day is hosted by the lovely Jen McCown (blog | twitter ), the female half of the dynamic duo of SQL MVPs known as the MidnightDBAs. The theme is to post about our technical resolutions for 2011 – what we hope to accomplish and why.
It seems that a lot of us are making similar resolutions for 2011, so if you think you've seen this list before, you probably have. I'm not copying other people's lists, it's just that many of us in the SQL Server Community are seeking to accomplish similar things this year (birds of a feather and all that).

RESOLUTION: BLOG at least once a Month on SQL topics

I started my blog in July 2010, and have been rather lazy about updating it. In 2010, I posted 15 articles, of which only 1 was 100% about SQL. I did have 6 others I tagged with "SQL" or "SQL PASS", but they were not focused specifically on SQL. That's not a particularly good record, especially since the purpose of starting this blog was to write about SQL and NOT about sewing, random thoughts or my goats, even though those are excellent topics for blogging. The good news is that by posting this article, I can check off January's SQL post...a good start to any set of resolutions. Of course, I DO have at least 11 other SQL blog posts that I must create.

RESOLUTION: READ Books on SQL Server 2008
AND
POST reviews of the SQL Server 2008 Books on my BLOG

This is a dual resolution, thanks to Brent Ozar (blog | twitter), who very kindly sent me a copy of "Professional SQL Server 2008 Internals and Troubleshooting"  which he co-authored with 6 other people. As a condition for giving it to me, he asked that I write a review of it. (Brent: I'm reading it now - really!!). Reading books on SQL will certainly help me better understand the inner workings of SQL and hopefully advance my career. Blogging about what I read will not only ensure that I absorb the information I read about but also will fulfill my blogging resolution.

RESOLUTION: WRITE a PRESENTATION on a SQL Topic
AND
Volunteer to PRESENT on a SQL Topic

As a SQL professional who has been working with SQL server since the days of SQL 6.5, I really ought to volunteer to present at a SQL-related event - be it a SQL Saturday, SQL User Group meeting, 24 Hours of PASS, or PASS Summit itself. This resolution is a two part resolution since the second part won't happen unless I actually have something prepared to present. I've been hiding in the background whenever calls have gone out for volunteers to speak because I "have nothing to say" and "have no idea what I can present about." Anyone who has heard me talk about sewing and costuming at the Science Fiction and Anime conventions that I've been attending knows that I have plenty to say (well, plenty to say about sewing, costuming, and a few other hobby-related topics) and don't fear getting up in front of 50 or so people and talking their ears off.

RESOLUTION: Become a SQL MVP

This is likely the hardest one for me to accomplish - especially since it requires others to recommend me for the award. However, if I work to exceed the other goals listed above, I might actually have that within reach.
 
What do you think? What are YOUR resolutions for 2011?

Resoved: Error Creating Storage Event Trigger in Azure Synapse

My client receives external files from a vendor and wants to ingest them into the Data Lake using the integration pipelines in Synapse (Syna...