Monday, March 4, 2019

Access Denied error when running job in SQL Server Agent

This is most common error, I have seen in SQL Server Agent. This error occurs when you try to run a job/program through SQL Server Agent with arguments (as an Operating system CmdExec job step)
This is because in the most cases, SQL Server Agent uses an NT Service account to run the jobs. So, we have to make sure that we should provide all the permissions to this account to avoid any failure.
In other cases, you can configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

By default, members of these database roles can create their own job steps that run as themselves. If these non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts.

If you are trying to access data from shared drive or trying to call a utility from the shared drive then you may be get this error such as Access Denied.
The main reason is that when you try to run the SSIS package in your development environment then the user who is created that package has the full access of the files/utilities path. Hence, package runs without any issues.
But the same package is deployed on the server and run through a job under SQL Server Agent account then it turned out that the SQL Agent account did not have permissions on the Tools directory where sqlcmd.exe or raw data file is located. As a result, we get Access Denied error when it runs under SQL Server Agent account because the Agent account doesn't really know that the file doesn't exist, just that it can't reach such a file.
Troubleshooting: One simple step you can take while troubleshooting is to run the exact command in a command window. Does it work as expected? How about if you log in using the Agent account?

First you'll need to make sure that XP_CMDSHELL is allowed.

-- Enable XP_cmdshell

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

To solve this error, we have to give Read & Execute permissions to the SQL Agent Account on that Tools directory where sqlcmd.exe or raw data file is located.

Friday, February 8, 2019

SSIS - Remove duplicate by Sort Transformation

In this tutorial, we will learn How to remove duplicate records from a file by using Sort Transformation in your SSIS package.
There are multiple ways to remove duplicate records in SQL Server. We can eliminate duplicate rows by using Sort and aggregate transformation in our SSIS package.  
Duplicate rows are rows with the same sort key values. The sort key value is generated based on the string comparison options being used, which means that different literal strings may have the same sort key values. The transformation identifies rows in the input columns that have different values but the same sort key as duplicates.

As we know that Sort Transformation in SSIS is used to sort the source data in either Ascending or Descending order, which is similar to T-SQL command ORDER BY statement.

There's an important thing to understand about using the "remove duplicates" feature of the Sort transform - the results are arbitrary. For example, take the following dataset:

ID  |   Name
3    |   Mervyn
2    |   Bharani
1    |   Nitesh
2    |   Jamie

Now, if you use the Sort transform to sort on ID and then remove duplicates then you *might* end up with the following result:

ID  |   Name
1    |   Nitesh
2    |   Bharani
3    |   Mervyn

or, you *might* end up with this:

ID  |   Name
1    |   Nitesh
2    |   Jamie
3    |   Mervyn

Notice how it arbitrarily picks either "Jamie" or "Bharani" because they both have the same ID. I've never seen any requirement, ever, that says "arbitrarily pick a result, I don't care which one I get".

Note: If you want to delete the duplicate records from the file then you should have to select all columns in Sort Transformation. If you try to remove duplicate columns only based on some Key columns that means you are loosing most of the data which can be required to store in your database.

The Sort transformation sorts input data in ascending or descending order and copies the sorted data to the transformation output.
Drag the Sort transformation from SSIS Tool Bar –> Common –> Sort.
Check the box at the bottom of the Window in Sort Transformation Editor that says ‘Remove rows with duplicate sort values’
Watch demo
Data Validation- for data validation prospecctive, you will use a Data viewer in order to facilitate a quick preview of sorting results. To accomplish this, right click on Data Flow Path between the Sort transformation and Destination and select Data Viewers from its context sensitive menu.
Conclusion - The Sort Transformation in SSIS is used to sort the source data in either Ascending or Descending order, which is similar to T-SQL command ORDER BY statement. 

Tuesday, February 5, 2019

SSIS - How to read file header columns in Script Task

In this tutorial, we will learn How to read header columns of a file in script task in your SSIS package. For validation of incoming record from flat files, we need to read the first row and validate with our destination table rows if it is matched then only we need to load the values otherwise we need to send the error email to concern person.

It is very use full for validating before file load otherwise if we load with different records our package will get fails and again we need to debug the package and find the issue and it will go long process.

This tutorial is using very basic steps to read your header columns in script task. To learn in details, please watch the below video -

Tuesday, January 15, 2019

Industry-Specific Database Platforms Terms

A server is a physical or virtual machine hosting SQL Server software complete with an operating system.

A cluster is a set of servers grouped together to provide redundancy. The cluster manager monitors the health and responsiveness of the cluster members (nodes). On Windows Server, the manager is called the WSFC (Windows Server Failover Cluster [manager]). Linux distributions use Pacemaker as a cluster manager. A cluster is managed at the operating system level.

A node is a member of a failover cluster. Each node in the cluster has the same capability to answer requests and has access to the same data as every other node in the cluster. Each node must be available to answer requests in a timely manner to be considered “healthy” by the cluster manager.

A database is a data structure that stores application data and metadata in physical files on disk.

An instance is a collection of SQL Server databases, jobs, and so forth run by a single SQL Server service that runs in memory on a specific computer at a specific time. An instance is accessed using a single IP address and all requests are sent to that IP. The source of the request doesn’t know which physical location the response it receives is coming from.

A replica (or database replica) is a set of two or more SQL Server instances that are grouped together in a failover configuration. In other words, a replica is—at the database level—what a cluster is at the operating system level.

Popular Posts

Get Sponsored by Big Brands