SQL SERVER – Cloud Based Data Integration Made Easy – A Real World Scenario

If you are a DBA, once in a while, you will have a situation where you end up with some tasks which will be boring and annoying. Trust me in my life, I often come across similar scenarios quite often. Here is one such tasks I came across a few days ago.

A Task, I would like to Automate

Just the other day I was assigned a task where I had to take a CSV file from my network and insert into SQL Server which was hosted in remote locations. Well, you may think it as a pretty easy task and I agree with you this is very easy task. The challenge is not about its difficulty, but the challenge was rather about the entire process and my interest. The part which annoyed me the most was that I have to do this every day at 4 PM.

This means, every day I must be at my desk at 4 PM and take a file from the network and upload to remote SQL Server. What about weekends? What about when I have to step away from my desk at 4 PM? What about the situation, when I am busy doing something much more important than this task? Well, as I said, more than task, I have been just one-place with the routine which was associated with it. In simple words, this was an ETL task which needed to be automated, but I can’t depend on my machine always. I have to find a solution which was cloud based and runs efficiently.

Skyvia at Rescue

I was sitting miffed in office and suddenly I remembered that last year I blogged about the tool Skyvia. Here is the blog post Integrate Your Data with Skyvia – Cloud ETL Solution. I quickly referred to my own blog, post and realized I should give Skyvia a try.

What is Skyvia?

Skyvia is a powerful solution for cloud data integration (ETL) and backup. It is a complete online solution and does not require any local software installed except for a web browser. In Skyvia we can create integration packages that define the operations and then we can run them or schedule for automatic execution. An integrated package is a set of data ETL operations that can be stored for future use, executed, modified, or deleted. Skyvia provides several kinds of packages for different integration scenarios. They are Import, Export, Replication, and Synchronization packages.

How did I do it?

Well, here are few of the screenshots of the task which I was assigned.

First, I checked if the table where I have to export data exists or not. As the table was already created, I quickly checked if it contained data or not. The table contained no data.

Next we will open the Skyvia web interface. It is pretty simple and it will list three options on the left bar. We will click Integration there.

In the Integration section, click Create Now under Data Import.

In the data integration screen we will be presented with various options. We will load the CSV file from an FTP server, so we select source as a CSV from FTP and target as SQL Server.

As we will be connecting SQL Server for the very first time we will be creating new connection and that is pretty straight forward procedure.

Then we will configure an FTP connection

Next we will configure CSV options. Here will be providing various options, but in our case all the default options were good enough for us to move next.

Right after that we will select the target table. In our case the target table is actor table.

The next screen will present mapping and we will one more time review various mapping options. We will make sure that all the source and target columns maps correctly.

When we click finish it will bring up the following screen.

Click on Save and now we are back on the following screen. Over here we can execute our task and see if it works or not. Click on the RUN button on the right side of the screen.

In my case the task ran successfully and it shows that it has inserted 200 rows successfully. The time taken to complete this entire task was 35 seconds and it depends on my network connection to the destination server.

We can execute the same select statement which we had executed earlier and see if the table contains the valid data.

Once we commit that our task has worked successfully, we can create a daily schedule.

That’s it! I am done.

Now every day at specific time the task will automatically execute and will log history.

Next Action Items

Team Devart has created Skyvia a feature rich service. One has to experiment with various different options to fully see the vast capability of this amazing product. Here are few things you can consider doing it. Here is the link where you can sign up for Skyvia for totally FREE. Next I will be trying out Skyvia with Salesforce. Skyvia is an all-in-one cloud solution for various Salesforce data integration scenarios. In addition to the standard Salesforce data loader functionality – data import and data export – it offers powerful data replication and synchronization tools and backup functionality with preview and export of backed up data, data search, viewing data changes between backups, and one-click restore.

Sign up for Skyvia for totally FREE.

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

SQL SERVER – Quiz with DATEADD Function

How many of us use some sort of date or datetime function when working with SQL Server? I cannot think of a single developer who would ever say they don’t work with datetime conversions in their application. Almost every application needs some manipulation of datetime datatypes. There are a number of pitfalls that can get into which we might not be aware. Here is a simple blog that I would love to hear your views on what the output would be:

Quick Trivia

Make your guess to what is the value for the below query? Guess before executing the same on SQL Server Management Studio.

SELECT DATEADD(MONTH, 1, '2015-01-28')
SELECT DATEADD(MONTH, 1, '2015-01-29')
SELECT DATEADD(MONTH, 1, '2015-01-30')
SELECT DATEADD(MONTH, 1, '2015-01-31')

Do you see something strange in the output? Will you be able to explain why we are getting this output? Why are the values so similar? I am sure once you execute the query in SSMS – the answer will be easy.

If the output is still confusing, hang on. Now what would be the values for the below query?

SELECT DATEADD(MONTH, 1, '2016-01-28')
SELECT DATEADD(MONTH, 1, '2016-01-29')
SELECT DATEADD(MONTH, -1, '2015-03-30')
SELECT DATEADD(MONTH, -1, '2015-03-31')

It is important to note is that DATEADD can either use a positive or negative integer as part of adding value. I am sure, I did trick you with the above query. Now the explanation becomes easy. Write your explanation in the comments and I will try to give you a special prize of one month free subscription to Pluralsight for five correct valid comments to this question.

Part 2

As I wrap up this blog, I would like to show how one of my friends made a small mistake while using the DATEADD function and how they got unexpected results. While using DATEADD, they accidentally wrote a YEAR function instead of “month”.

SELECT DATEADD(YEAR, 8000, '2015-01-31');

If you execute the above query, we will be presented with an Error. This is part II of the trivia. This will be an added bonus if you can explain.

Msg 517, Level 16, State 1, Line 9
Adding a value to a ‘datetime’ column caused an overflow.

Call to Action

1) Write the answer to first quiz – Why specific outcome when you execute queries with DateAdd and month?

2) Write the answer to second quiz – Why specific error on the screen?

Leave answer before February 20th, 2015 in comment section to eligible for price of free Pluralsight subscription.

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

SQL SERVER – Installation Error – INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory

One of my blog reader contacted me for assistance. Here is his email:

Hello Pinal,
I am trying to install SQL Server on my laptop but getting below errors:

  • SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Folder C:\Program Files\Microsoft SQL Server has an unsupported attribute (Compressed) set. Please resolve this issue by removing the unsupported attribute from the folder using folder properties dialog.
  • SqlServer.Configuration.SetupExtension.CompressedDirException: The specified directory, “C:\Program Files\Microsoft SQL Server\”, for the INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory. Specify a directory that is not compressed.
  • SqlServer.Configuration.SetupExtension.CompressedDirException: The specified directory, “C:\Program Files (x86)\Microsoft SQL Server\”, for the INSTALLSHAREDWOWDIR parameter is not valid because this directory is compressed or is in a compressed directory. Specify a directory that is not compressed.

Can you help me in understanding the error and the fix of the issue?

Since the error messages were talking about compression so I checked MSDN to find whether SQL can be installed on compressed directory or not. And I found https://msdn.microsoft.com/en-us/library/ms143506.aspx (Hardware and Software Requirements for Installing SQL Server). This says “SQL Server Setup will block installations on read-only, mapped, or compressed drives”

Having said all this, in this era of faster disks and infinite storage. I am still wondering how many of you out there is using compression on your disks for files stored by applications? If we read the message it, the solution is simple. Here are the steps to un-compress a folder.

  • On the desired folder (in our case “C:\Program Files”) right click and go to properties.
  • Click on “Advanced” button.
  • Uncheck the box “Compress the drive to save space”
  • Click Apply and then Ok.
  • Let the process finish.

Later I got message from the reader that he was able to install SQL without any further issues. As I say always, most of the error messages are self-explanatory. All we need is to understand how the system is being used. In this context it is worth to know there are few KB Articles (231347)

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

Interview Question of the Week #007 – How to Reindex Every Table of the Database?

Some questions are extremely popular questions and they never get old. Here is one such question which I see very often asked to DBAs in their early career.

Question: How to re-index every table of the database?

Answer: Well, The answer of this question can be only given in the form of the script.

For SQL Server 2014 and later version

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
WHERE is_memory_optimized = 0
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

(Remember that alter index will fail on in-memory table, hence they needs to be excluded)

For SQL Server 2005, 2008 and 2012 versions

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

For SQL Server 2000 version

DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT
table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE
myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO

Well, there are many different methods and many different variations out there for this script, however, above script has always worked for me and I trust them.

Here are few related blog posts one should refer for further information.

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

SQL SERVER – Msg 8152, Level 16, State 14 – String or binary data would be truncated

Earlier this week, I have blogged about how to suppress Warning: Null value is eliminated by an aggregate or other SET operation SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation.

If you read that blog, I mentioned during closure that this setting might cause unexpected behavior if not used properly.  First, let’s understand the error which I am talking about:

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

I am sure that many developer might have seen this error at least once in their lifetime. This particular error message is raised by SQL Server when we try to insert long literal sting is longer than the defined table field datatype.  For example, if we try to insert a varchar with more than 100 characters into a varchar(50) field, we will get the following error. Here is an example script to reproduce the error:

USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO

I have put the same column name as the value which I am inserting so that you can easily see the cause of the error. Since I am inserting ‘Pinal’ in varchar(3) and ‘SQLAuthority’ in varchar (10), we are getting the error.

One of the problems with this message is that SQL Server doesn’t tell you which table or column has the problem. The correct way to fix the issue is to find the column causing error and correct the data or column length.

Coming back to set option, if we use ansi_warnings as OFF, the error would be suppressed and whatever can fit in the column, would be inserted, the rest would be truncated.

SET ansi_warnings OFF
GO
USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO
SELECT * FROM MyTable
GO

As we can see that the error was suppressed in the above code, but we lost part of the actual data which we were supposed to have on the table. So be cautious when working with such SET options.

Conclusion – Use ANSI_WARNING OFF with care, it might have effect on data insertion.

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

SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation

Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL.

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

In future blogs, I would try to explain another one called ANSI_WARNINGS. Let’s look at the various warnings which can be suppressed when working with NULL.

Warning: Null value is eliminated by an aggregate or other SET operation

Let us run below script in SQL Server Management Studio to see the effect.

SET NOCOUNT ON
GO
DECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULL
GO
SELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)
-- no warning
GO
SELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO
SELECT AVG(Qty) 'Avg' FROM @ItemSale WHERE ID IN (1,2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO

Here is the output

As the error says, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we can use “set ansi_warnings off” before the script. Here is the modified script.

SET ANSI_WARNINGS OFF
GO

Here is the output after adding the set option:

First of all we should remember that default value of the setting is OFF, which is actually good. In subsequent blogs, we would cover an error which can be suppressed using same set option. Tuning it ON can be dangerous as well. Stay tuned!

What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful? Let me know.

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

SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the Execute Package Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Learning how to configure and use the SQL Server Integration Services (SSIS) Execute Package Task gives you a great starting point for understanding SSIS package orchestration. I advocate writing small, functional SSIS packages that perform a unit of work. That’s a great idea for many reasons. But it begs the question: “How do I string together the execution of these packages?” 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 Execute Package Task, shown in Figure 1:


Figure 1: SSIS Execute Package Task

The Execute Package Task provides one way to implement an SSIS Design Pattern for SSIS package execution known as the Parent-Child pattern. When an SSIS package uses the Execute Package Task to start another SSIS package, the package with the Execute Package Task is called the Parent and the package started by the Execute Package Task is called the Child.

The Execute Package Task changed between SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The changes support the SSIS 2012 (and 2014) Catalog. There is a new property called ReferenceType which defaults to “Project Reference” in packages executing in Project Deployment Mode (the default mode for building SSIS 2012 and SSIS 2014 SSIS packages), as shown in Figure 2:


Figure 2: Options for the Execute Package Task ReferenceType Property

Project Reference is used to execute an SSIS package – a Child package – in the same SSIS project with the package that contains the Execute Package Task – the Parent package. When Project Reference is selected, the next property in the property grid is PackageNameFromProjectReference, a dropdown containing a list of all the SSIS package in the SSIS project.

Setting the ReferenceType property to External Reference is a way to execute SSIS packages that are stored in the file system or the msdb database. When External Reference is selected the next properties in the property grid change to reflect this backwards-compatible functionality, as shown in Figure 3:


Figure 3: Setting the ReferenceType Property to External Reference

The Location and Connection properties are used to specify an OLE DB Connection (to the msdb database) or a File Connection (to the location of the dtsx file). SQL Server locations also require the name of the SSIS package; the File System option does not because the SSIS package is the only thing in the file. The External Reference ReferenceType setting is useful for importing SSIS solutions that contain earlier versions of the Execute Package Task because External Reference behaves exactly like previous versions of the Execute Package Task.

The remaining properties in the property grid – Password and ExecuteOutOfProcess – are shared between ReferenceType options. Password is used if the SSIS package is password-protected (if the ProtectionLevel property of the SSIS package is set to either EncryptSensitiveWithPassword or EncryptAllWithPassword). ExecuteOutOfProcess is a setting that determines whether the package will be executed as part of the current process or a new process.

When executing SSIS packages in Project Deployment Mode (using the Project Reference ReferenceType), Parameter Bindings are enabled and allow values to be passed from the Parent package to the Child package, as shown in Figure 4:


Figure 4: Binding a Parent Package Parameter to a Child Package Parameter

Parameter Bindings are disabled when the ReferenceType property is set to External Reference.

Once configuration is complete, click the OK button to close the Execute Package Task Editor. You can test execution by pressing the F5 key or selecting “Start Debugging” from the SSIS dropdown menu. A successfully-executed Execute Package Task will appear as shown in Figure 5:


Figure 5: A Successful Execution!

The Execute Package Task drives a powerful data integration architecture pattern: Parent-Child execution. Using SSIS Precedent Constraints and Sequence Containers with the Execute Package Task, a data integration developer can develop SSIS “driver” packages that call Child SSIS packages in any combination of parallel and serial orders of execution.

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)