A Fine Slice Of SQL Server
That SSIS change just won’t stick! There is a new business requirement, so now you have to change an existing SSIS package. You opened up a new project, imported the package, and made your changes.
But the changes just aren’t showing up. You back and look again and sure enough, the changes you made are there. You run your job (or whatever) and still the change aren’t taking effect. So what happened?
Read on for the cause and the solution.
Comments closedGinger Grant shows how to create a new SSIS or SSRS project for SQL Server 2016:
In this version of SQL Server Data Tools, Microsoft has finally addressed the common problem of needing to maintain multiple versions of SSIS packages for the different server versions. No longer do you need three different applications to maintain code for SQL Server 2012, 2014 and now 2016. All of these versions are supported with SSDT for Visual Studio 2015. SQL Server will detect which version the code was last saved in so that you don’t have to worry about accidently migrating code. You also have the ability to create an SSIS package in 2012, 2014 or 2016. To select the version you want, right click on the project and select Properties. Under Configuration Properties->General as shown in the picture, the TargetServerVersion, which defaults to SQL Server 2016, has a dropdown box making it possible to create a new package in Visual Studio 2015 for whatever version you need to support. Supporting the ability to write for different versions, is a great new feature and one which I am really happy is included in SSDT for Visual Studio 2015.
I’m also glad that Microsoft has made this move. It is no fun having two or three different versions of Visual Studio installed because some component requires an older version.
Comments closedThe MDX keywords in a cube script were not properly changing colors in the SQL Serve Data Tools (SSDT) Preview version for Visual Studio 2015. (14.0.60316.0). (See my previous post on this problem here.)
The different keyword colors make it easier to write, organize and read an MDX script inside a cube.
Looks like they squashed that bug.
Comments closedMatt Smith ran into an issue when trying to compare two databases using SQL Server Data Tools:
This appeared to work but didn’t display any results. When you look at the bottom status bar it reads
Comparison complete. No differences detected. Restricted comparison. See Error List for details.
I knew there we at least some differences. I then click on the Error List table below it which revealed
The reverse engineering operation cannot continue because you do not have View Definition permission on the ‘Warehouse’
The answer is pretty simple, so read on.
Comments closedEd Elliott continues to amaze me. This time, he’s got a code coverage tool for T-SQL code:
If we execute this stored procedure we can monitor and show a) how many statements there are in this and also b) which statements have been called but we can’t see which branches of the case statement were actually called. If it was a compiled language like c# where we have a profiler that can alter the assembly etc then we could find out exactly what was called but I personally think knowing which statements are called is way better than having no knowledge of what level of code coverage we have.
Yet another reason to grab the SSDT Dev Pack. By this point, I expect there to be a couple more reasons next week…
Comments closedEd Elliott has a three-part series on database projects in SQL Server Data Tools.
The SSOX or SQL Server Object Explorer is a cool utility that lets you connect to a live database and do things to it like debug stored procedures or update individual objects. It also lets you see a view of you projects after all references have been resolved so if you use “Same Database” references you can see how your end project will end up – really useful.
Using the DacServices via whatever method you want (schema compare, sqlpackage, powershell, something else?) really makes it simple to spend your time writing code and tests rather than manual migration steps. It constantly amazes me who well rounded the deployment side of things is. Every time I use something obscure, something other than a table or procedure I half expect the deployment to fail but it just always works.
Over the last couple of years I must have created hundreds if not thousands of builds all with their own release scripts across tens of databases in different environments and I haven’t yet been able to break the ssdt deployment bits without it acyually being my fault or something stupid like a merge that goes haywire (that’s one reason to have tests).
The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.
The second way it can be used is to take objects and create T-SQL.
I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn’t guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table’s deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.
This is a great series with a lot of informative links.
Comments closedEd Elliott has another update to SSDTDevPak:
How do I get it?
i have now pushed it to the visual studio gallery and will use this for all future updates so feel free to grab it from:
https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5b8a05dc7906.
Also check out T-SQL Clippy.
Comments closedEd Elliott has another nice tool in his SSDT Dev Pack:
This new tool for the SSDT Dev Pack adds a menu item (Tools–>SSDT Dev Pack –> Find Duplicate Indexes) what it does is scan all the projects in the solution (it doesn’t follow “this database” references to examine them, maybe a future version) and then print to the output window a list of all the duplicate indexes based on the table, the columns and included columns – I don’t check anything else so you might actually want a duplicate in some circumstances but these should be very few and far between.
If you double click on the index it will take you to the location in the code where it actually is so you can delete it 🙂
A very useful tool gets even more useful.
Comments closedEveryone knows you can use SHIFT + [Left/Right Arrow] to highlight text. But you can also use ALT + SHIFT + [Up/Down/Left/Right Arrow] to select a block of text or even make a vertical selection to insert a block of text on multiple lines. Or you can use ALT + [Mouse Drag] to make a block selection with your cursor.
Notepad++ works the same way. Every once in a while, I’ll run into a scenario in some tool which doesn’t implement Alt key functionality—especially certain non-Microsoft platform database products—and it will hurt a little bit inside.
Comments closedEd Elliott has just added another SSDT Dev Pack feature:
Open a document with a table valued function or procedure, click tools–> SSDT Dev Pack –> QuickDeploy or use the keyboard options in SSDT to make Tools.QuickDeploy to a key combination (I use Ctrl+Q, Ctrl+D but it is a little awkward) and your code will be deployed, any messages are added to the standard output window.
If the active document has anything other than stored procedures or table valued functions then they will not be deployed and also if you have more than one procedure of function, all of them in the active document will be deployed.
Ed is quickly becoming one of my favorite bloggers. It seems like every other day, he has a new tool available for us.
Comments closed