SQL SERVER – The Basics of the File System Task, Part 1 – Notes from the Field #071

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS File System Task, shown in Figure 1:

Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:

Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:

Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:

Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:

Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:

Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. For the sake of simplicity, we’re not going to use SSIS Variables to define the source and destination paths in this article. Reset the IsSourcePathVariable and IsDestinationPathVariable properties to False. Click the SourceConnection property dropdown, and click “<New connection…>” as shown in Figure 7:

Figure 7: Selecting a New Connection for the SSIS File System Task SourceConnection Property

The File Connection Manager Editor displays with the “Usage type” property set to “Existing file” as shown in Figure 8:

Figure 8: File Connection Manager Editor

Click the “Browse…” button and select a flat file you wish to move, as shown in Figure 9:

Figure 9: Selecting the Source File

Once the File Connection Manager Editor is configured, it should appear similar to that shown in Figure 10:

Figure 10: A Configured File Connection Manager Editor

Click the OK button to close the File Connection Manager Editor. Next, click the DestinationConnection property in the SSIS File System Task Editor. As with the SourceConnection property, click the dropdown and select “<New connection…>”. When the File Connection Manager Editor displays, select the “Existing folder” Usage type, as shown in Figure 11:

Figure 11: Configuring the Destination File Connection Manager

Note the warning at the bottom of the File Connection Manager Editor window: “Folder name must be specified.” Click the “Browse…” button and navigate to the directory where you wish to archive the source file as shown in Figure 12:

Figure 12: Configuring the Location of the DestinationConnection File Connection

Click the OK button in the “Browse For Folder” dialog to return to the File Connection Manager Editor, as shown in Figure 13:

Figure 13: A File Connection Manager Configured for the DestinationConnection Property

Click the OK button to close the File Connection Manager Editor and return to the File System Task Editor, as shown in Figure 14:

Figure 14: An SSIS File System Task Configured to Archive a File

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 15:

Figure 15: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 16:

Figure 16: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated one way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction

In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO 10
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO 10
-- Look at the trancount?
SELECT @@TRANCOUNT;
GO

In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

Or the below command works:

-- Works?
ROLLBACK TRAN;
GO

And it is so strange that this above commands worked. If you try to query the table, it will have no rows.

-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)

Life gives you learning lessons from unexpected times, here is one such experience that I felt is worth sharing. It may be a surprise to my blog readers that I also have SQL Server Analysis Services installed on my primary machine. I use it occasionally because most of the time I work with the SQL Server Database engine. One fine day, I opened SQL Server Management Studio and instead of database engine, I selected analysis services (this was a mistake) and pressed on the connect button. Below is the error which I received:

TITLE: Connect to Server
——————————
Cannot connect to BIGPINAL
——————————
ADDITIONAL INFORMATION:
A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)
——————————
No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)
——————————
BUTTONS:
OK
——————————

This is the first time I saw such error while connecting to SQL Server, but soon I realized that I was trying to connect to SQL Server Analysis Services. I could have left it there because I don’t use it, but I did not. Since there was a little time, I thought it is worth solving this challenge in front of me. Here is the step by step resolution of what I did:

  1. Made sure that SQL Server Analysis Service was up and running. This got confirmed from my SQL Server Configuration Manager.
  2. As I do always, read the error message again and verified that the SQL Browser service was up and running.
  3. Since this was a local connection, I turned off the firewall as well.

None of the above solved the problem. The Port resolution is done by the SQL browser service in case of SQL Server connectivity. So I thought that it could be a permission issues – just a guess based on the error message.

I noticed that SQL Browser was running with the local system account. I found on internet that changing account helped many. So I used SQL Server Configuration Manager to change service account for the SQL Server Browser. I have used like below.

Here are the steps:

  • Open SQL Server Configuration Manager
  • Right click “SQL Server Browser” and choose “Properties”
  • In the “Log On” Tab select “Local System” under “Built-In” account.

Have you ever seen such error and found some other way to fix it? Please comment and let others also know. The best way to learn something is by sharing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Add User Across Databases

In this era of standards, many organizations want to be using some of the cutting edge technologies in their organizations. In many of these interactions I was lucky enough to meet few DBA’s who give me interesting challenges to meet within their organizations. I still vividly remember sharing a script with a DBA who reached out to me to know how we can add a user to all the databases within their SQL Server instance.

On first look, I asked “why?” The answer was simple. Their organization data gets audited once a year by the auditors and they need to be given db_reader privileges during the time of audit. So they wanted to make sure this is done in an automated fashion. I was immediate to give them recommendation to my Plurasight Course. But they were not on the latest version and wanted something in previous versions.

So here is the script that I gave them:

EXEC sp_MSforeachdb '
DECLARE @name VARCHAR(500)
SELECT @name = ''?''
IF ''?'' not in (''tempdb'')
BEGIN
USE [?]
IF DATABASEPROPERTYEX(''?'',''Status'') =''ONLINE'' AND DATABASEPROPERTYEX(''?'',''Updateability'')=''READ_WRITE''
BEGIN
IF NOT EXISTS(Select * from sys.sysusers where name = ''SQLAuth_Auditor'')
BEGIN
CREATE USER [SQLAuth_Auditor] FOR LOGIN [SQLAuth_Auditor]
PRINT ''Added User for ?''
END
EXEC sp_addrolemember ''db_datareader'', ''SQLAuth_Auditor''
PRINT ''Added db_datareader for ?''
END
ELSE
PRINT ''SKIPPED the ? database''
END '

The script uses an undocumented command sp_msforeachdb which I have used before in earlier blogs too to achieve some interesting requirements.

Here in the script, we check of SQLAuth_Auditor user existence in all the databases. If it is not available, we go ahead and add the db_datareader role to the database.

My ask: How many of you have used sp_MSforeachdb procedure in your environment? What are the use cases where you have successfully used the same.

Quick Quiz:

To achieve the above task, can’t we just add the user to Model database and all databases created will automatically get the user added? Is there any catch provided there are no databases currently in a given instance? Do we still need a script like this? Let me know your thoughts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #010 – What is the Difference Between Primary Key Constraints and Unique Key Constraints?

I often see people getting confused between Primary Key and Unique Key. I see where people get confused about the same. Let us discuss.

Question: “What is the Difference Between Primary Key Constraints and Unique Key Constraints?”

Answer: “Primary Key (PK) Constraints enforce uniqueness just like Unique Key (UK) Constraints, however, PK does not allow  NULL value where as UK allows one NULL value.”

Well, the answer is as simple as above statement. You can read more about this over here: Primary Key Constraints and Unique Key Constraints.

I am very sure after reading above statement there will be few readers, who is going to comment and write email that when we create Primary Key Constraint it also creates clustered index on that column but Unique Key Constraint does not create clustered index. Well, this is the specific (and default) behavior in SQL Server where clustered index is automatically created, however user can create Primary Key Constraint without creating a clustered index as well and you can read about this over here Primary Key and NonClustered Index in Simple Words.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Buffer Pool Extension with In-Memory OLTP?

I am always amazed to see the questions asked by my blog reader which lead me to learn more about SQL Server.  Here is the question which took me little time to figure out the answer.

If you are new to SQL Server 2014 new features which I have in subject, I would recommend you to go through below blogs first.

SQL SERVER – Beginning In-Memory OLTP with Sample Example
Buffer Pool Extension in SQL Server 2014 Pluralsight Course

The blog reader asked me below question:

Hi Pinal,
I watched your Pluralsight course and was able to easily learned about two new features, In-Memory OLTP (Hekaton) and Buffer Pool Extension (BPE). Thanks for making such course. I have some follow-up question and I hope to get answer from you.

My In-Memory table would be 10 GB in size. As per your course, I would need at least 10 GB of RAM so that complete table can fit in memory. Can I use buffer pool extension and live with 5 GB RAM and 50 GB BPE file?

Regards,
<Name Hidden>

This is indeed an interesting question. If we go back to basics, we need to keep below in mind.

  1. In-Memory OLTP doesn’t uses Pages. It just has free flowing rows in memory, tied to each other via index.
  2. Buffer Pool is the area where SQL Server keeps every page, query plan.
  3. Buffer Pool Extension feature is to extend the buffer pool.

If we tie all three points together, we can easily conclude that Buffer Pool Extension cannot be used to hold data belonging to In-Memory tables.

Here is the quick image which can show what would happen if In-Memory table size grow

Hope this would help you in remembering that In-Memory Tables are not part of Buffer Pool and hence we can’t extended the data from those tables to extension file.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – ReadOnly Databases with ReadOnly File Attribute

As part of continued series of ReadOny databases, the earlier blog had an interesting comment that read as some people were getting errors of 5120 and others. This got me curious and was in the pending list of solutions in some way.

One of the readers had mailed me saying, they attached a database and the databases came up as ReadOnly. They didn’t know why this happened and when they try to mark the database as ReadWrite, it was giving them an error.

Here are the steps to reproduce the situation. I have the database files in a fileshare. We will try to attach the same using the below command:

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON
( FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'),
(
FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf')
FOR ATTACH
GO

Note: In your case, you can have database files on a different folder.

As soon as the command is executed, we can see in the object explorer the following:

We can see that the database is marked as Read-Only on attach. This was strange. So based on the blog, let us try to make the database as ReadWrite using the following command:

USE [master]
GO
ALTER DATABASE [ReadOnlyDB] SET  READ_WRITE WITH NO_WAIT
GO

Now this raised the following error:

Msg 5120, Level 16, State 101, Line 36
Unable to open the physical file “C:\ReadOnlyDB\ReadOnlyDB.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 5181, Level 16, State 5, Line 36
Could not restart database “ReadOnlyDB”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 36
ALTER DATABASE statement failed.

The catch to the error is hidden in the message. It says “Operating system error”. So let us go ahead and detach the DB to change the OS File attributes.

USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'ReadOnlyDB'
GO

Next get to the File system attribute for the database and Log files to check for the ReadOnly attribute. In our example, the mdf file had this attribute enabled.

Please go ahead and uncheck the “Read-only” attribute from the file. That is it. Now attach the database after the change.

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON
( FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'),
(
FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf')
FOR ATTACH
GO

This got us back to normal life as far as our ReadOnly databases is concerned. Hope you learnt something new as I am learning something new almost every single day. Do let me know if you got a chance to learn so much working with ReadOnly databases in the past.

Reference: Pinal Dave (http://blog.sqlauthority.com)