Including Current Date In a Flat File Name - SSIS

There was a thread on MSDN Forums, which paraphrased, asked "How can I include the current Year and Month in a file name when I create a Flat File in SSIS"

Although this is a relatively straight forward task, because SSIS is so visual, it can be difficult to explain how to do things in Forum threads, so I decided to produce this short walk through video. Hope you enjoy, and find it useful...

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Sexy SSIS In Denali - Usability Enhancements

Following the success of my last post on SSIS in Denali see here I have been wanting to do a post for some time, giving an overview of some of the nice new usability enhancements for Developers. Unfortunately, it is predominately visual, so I have been putting it off, as really it requires a webcast, and 1) I hate the sound of my own voice! 2) I do not have any professional screen capture software. Today, however, I bit the bullet and recorded a short piece, that gives an overview of some of my favourite new features for developers. Most of them seem like small changes, but together, they make the product much easier to use.

I hope you enjoy it, but please bare the previous two caveats in mind!!! ;-)

If you enjoy this, and would like to see more webcasts, then please leave a comment to let me know. It's always good to know how to spend my blogging time!



Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SSIS In Denali. Sexy! But Substance?

In Denali, SSIS has been given a face lift, and it looks pretty sexy, that's a given. But for my first post on the subject, I wanted to look instead at some of the new functionality to see how it performed, and one of the biggest new features in terms of functionality, is the Integration Services Database, so...what does it give us?

Well, I started by creating a simple SSIS package in the VS 2008 Shell. (We have been promised VS 2010 shell for RTM!) I used the new Source Assistant and Destination Assistant to create a data flow that just pumps rows from one table to another.

Then, in SSMS, I created an Integration Services Catalog, by right clicking on the Integration Services folder. Two points of note here were that it made me enable CLR, and it enforced a complex password...

 ...Then I created an organisational folder, under the catalog...

...I then tried to import the package from the context menu of the folder. It did not work, but to be fair, I am using CTP1! So I went back to BIDS and used the new Deployment Wizard (I'll blog about this soon, because it is quite good, and very, very different!) and deployed the project to the SSIS database. Denali uses the concept of deploying projects, much more rigorously than previous version, as many of the new features (such as parameters) are based at the project level.

After deployment, The project and package appeared under my folder. Right clicking the package allowed me to run a validation process, shown below. I could also have done this at the project level...

...Everything seemed Ok, so I ran the package. This gave me a jazzed up "Progress Window", which furnished me with status information in real-time...

...After package execution, a right click on the Catalog, allowed me to click the operations button, which gave me a history of all the actions that I had performed so far...

...I then found a whole raft of extra information in the SSISDB Database, that was automatically created when I created the catalog. Some of the most interesting tables included Executions table, the Catalog_Property table and the Data_Type_Mapping table, all shown below...

...Finally, a quick look at the Properties of the Project, revealed the Previous Versions Window. This allows us see, and easily roll back to a previous version of the package, in the event that you had not tested your code properly! (Not that would every happen of course!)...

...So in conclusion, there will be many more Denali SSIS posts to come, but I'm sure you can tell from this post alone, that DBAs are going to love it!!!

SQL Server 2011 - THROW

Just a quick one, as 3 posts on New Year's Eve is probably a little excessive, but I have just found a cool new (and long awaited) feature in Denali. The ability to Throw an error.

You can use this in your custom error handling to throw a custom error message, like RAISERROR but also (and probably more usefully) you can use it in a CATCH block, to essentially re-throw the error that caused the CATCH block to kick in. For example...

...To produce the following output...

BEGIN TRY
     SELECT 1/0
END TRY
BEGIN CATCH
     THROW
END CATCH

...There you go, very simple, but every liuttle helps!

(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

I am reliably informed that the final release will also include a FINALLY block, but that does not seem to work in CTP1, so I will post on that after I have had the chance to play with it.

HAPPY NEW YEAR!!!

SQL Server 2011 - EXECUTE Proc WITH RESULT SETS

Another new feature of Denali, is the ability to enforce that the results of a stored procedure meet your client app's data contract, without having to modify the stored procedure. This is pretty useful, as it will allow you to have one stored procedure, servicing multiple apps, without the need for custom formatting at the front-end.
It will allow you to perform implicit conversions, and even change the local of a column, with the EXECUTE statement. Cool, eh?

I created the following Proc in the AdventureWorks2008 Database...

CREATE PROCEDURE ResultSetsDemo
AS
BEGIN
     SELECT  FirstName
     ,       MiddleName
     ,       LastName
     ,       ModifiedDate
     FROM Person.Person
END

...Then executed the procedure with the following statement...

...This produced the following results...

...As you can see, it has CAST the modified date to a DATE column, and the Firstname column has also had it's collation changed.

I then altered the procedure as below...

...And executed it again with the following code...

...And it failed with the following, (slightly missleading) error...

Msg 8114, Level 16, State 2, Procedure ResultSetsDemo, Line 12
Error converting data type nvarchar to nvarchar.

..In actual fact, the failure is because there are NULL values in MiddleName, and I have specified NOT NULL. (I though it would be kind of nice if it implicitly filtered out the NULLs, but I can see why it doesn't!).

Running the EXECUTE statement again, with the NOT NULL constraint removed, produces the expected results...

EXEC ResultSetsDemo
WITH RESULT SETS
(
     (
     Firstname  VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName  CHAR(5),
     LastName  VARCHAR(50),
     ModifiedDate DATE
     ),
     (
     FirstName VARCHAR(50),
     MiddleName CHAR(50),
     LastName VARCHAR(50)
     )
)

EXEC ResultSetsDemo
WITH RESULT SETS
(
     (
     Firstname  VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName  CHAR(5),
     LastName  VARCHAR(50),
     ModifiedDate DATE
     ),
     (
     FirstName VARCHAR(50),
     MiddleName CHAR(50) NOT NULL,
     LastName VARCHAR(50)
     )
)

ALTER PROCEDURE ResultSetsDemo
AS
BEGIN
     SELECT  FirstName
     ,       MiddleName
     ,       LastName
     ,       ModifiedDate
     FROM Person.Person

     SELECT FirstName
     ,      MiddleName
     ,      LastName
     FROM Person.Person
END

EXEC ResultSetsDemo
WITH RESULT SETS
(
    (
     Firstname VARCHAR(50) COLLATE Albanian_BIN2,
     MiddleName CHAR(5),
     LastName VARCHAR(50),
     ModifiedDate DATE
     )
)

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SQL Server 2011 - SEQUENCE Part II

Following my first post of the new Sequence object in Denali, and possibly promoted by the disturbingly  "cursor-like" syntax, I decided to see how the feature performs, like for like, against the Identity column property.

To do this, I altered my original sequence by using the following syntax...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1
INCREMENT BY 1

...I then created a control table called dbo.Control, that contains one column, called col1, which holds the number 1 through 1000000. I then created 2 tables with the following code...

CREATE TABLE Sales.SalesOrdersSequence
(
EntID INT,
OrderNumber INT DEFAULT  (NEXT VALUE FOR Sales.SalesOrderNumbers)
)
CREATE TABLE Sales.SalesOrdersIdentityCol
(
EntID INT,
OrderNumber INT IDENTITY(1,1)
)

...Next, to ensure a fair test, I tore down the procedure and buffer caches with the following statements...

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

...and in Options, I turned on the options to record Time statistics. I then ran the following INSERT statement, and included the actual execution plan...

INSERT INTO Sales.SalesOrdersIdentityCol (EntID)
SELECT col1 FROM Controltbl

...I returned the following statistics and query plan...

 SQL Server Execution Times:
   CPU time = 6536 ms,  elapsed time = 17100 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

...I then tore down the caches once again, before running the following INSERT statement against the table using the sequence object...

INSERT INTO Sales.SalesOrdersSequence (EntID)
SELECT col1 FROM Controltbl

...Although the execution plan was (unsurprisingly) identical, the following stats were returned...

 SQL Server Execution Times:
   CPU time = 4758 ms,  elapsed time = 10098 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

...Frankly, I was very pleasantly surprised by the results. The second INSERT took only 59% of the time, and caused only 73% of the CPU overhead. This means that replacing your IDENTITY Columns with references to a sequence object could have a positive effect on Bulk Inserts.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SQL Server 2011 - Offset

One very simple, but very useful new feature of Denali, is the OFFSET clause, which can be used in conjunction with ORDER BY, to return just a specified number of rows from a table.

For example, if I run a SELECT * against the Sales.SalesOrderDetail table in the AdventureWorks2008 database, I return 121317 rows, starting with SalesOrderDetailID 1.

If however, I run the following query...

SELECT
     *
FROM
     Sales.SalesOrderDetail
ORDER BY
     SalesOrderDetailID
          OFFSET 10 ROWS

...I skip the first 10 rows, based on SalesOrderDetailID, and return the following results...

...As you can see, I have only returned 121307 rows, and have missed out the first 10 rows, based on SalesOrderDetailID.

I can also limit, how many rows will be returned after the offset. For example, if I run the following query...

SELECT
    *
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderDetailID
         OFFSET 100 ROWS
         FETCH NEXT 10 ROWS ONLY

..I return only 10 rows, with SalesOrderDetailIDs from 101 through to 110, as you can see below...

...One big bonus, is that the OFFSET statement can be fully parametrised, so for example, I could run the following query to return identical results...

DECLARE @Offset INT
DECLARE @Limit INT

SET @Offset = 100
SET @Limit = 10

SELECT
    *
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderDetailID
         OFFSET @Offset ROWS
         FETCH NEXT @Limit ROWS ONLY

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

SQL Server 2011 - SEQUENCE Part I

I have started playing with CTP1 of SQL Server 2011, (codename Denali). As you would expect, there are not a massive amount of new features included in this "first look", but over the Christmas period, I intend to post about some of the features that are available. The first of these is a long-awaited feature called a Sequence.

This is essentially a hugh extension to an identity, which only allowed you to specify a seed and an increment.

The first thing you notice about a sequence, is that it is not a column property, it is a separate, schema bound object. This means that you can run DDL statements against them, such as CREATE SEQUENCE, ALTER SEQUENCE, etc, and also means that you can run queries against them, to find out the next value, etc. Of course the biggest benefit, however, is that you can maintain a sequence across multiple tables.

The sequence object supports features, including Start with (equivalent of seed on an identity column), Min Value, Max Value, Increment (equivalent of Increment on an identity column)

I created a sequence object in the AdventureWorks2008 database, that mirrored a bog standard IDENTITY(1,1) column, to use as a starting point. To do this, I used the following statement...

CREATE SEQUENCE Sales.SalesOrderNumbers 
AS INT   
MINVALUE 1  
NO MAXVALUE   
START WITH 1

...I then used the following query, to pull back the first 3 numbers in the sequence...

SELECT NextOrderID=NEXT VALUE FOR Sales.SalesOrderNumbers
UNION ALL    
SELECT NEXT VALUE FOR Sales.SalesOrderNumbers
UNION ALL    
SELECT NEXT VALUE FOR Sales.SalesOrderNumbers

...And saw the following results...

..When I then ran the query again, it gave me the next 3 numbers...



...The next thing I tried, was resetting the sequence. To do this, I ran the following statement...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1

...Running the SELECT statement again, following this restart, produced the following output...

Next, I tried out the increment, by altering the sequence object with the following command...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 100
INCREMENT BY 10

...After this alteration, the SELECT statement produces the following results...

...I then changed the sequence object to...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 100
INCREMENT BY -10

...To produce the following results...

...Finally, I created a table with the following statement...

CREATE TABLE Sales.SalesOrders
(
EntID INT,
OrderNumber INT DEFAULT (NEXT VALUE FOR Sales.SalesOrderNumbers)
)

...and with the sequence altered as follows...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1
INCREMENT BY 10

...I ran the following INSERT statement...

INSERT INTO Sales.SalesOrders
 (
 EntID
 )
VALUES
 (1),
 (2),
 (3)

...A SELECT statement from Sales.SalesOrders table, then produced the following results...

...In summary, sequence seems easy to use, and adds some really useful functionality to SQL Server

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom