SQL Server starting with 2012 ship with a robust set of functions that can be used to make code perform faster, with fewer lines of code. The functions can be used in ETL Process to provide better error handling. A example of this would be the Try_Parse function that allows you to check if a value can be converted.
Another example would be using the FIRST_VALUE() and LAST_VALUE() functions which work against a set of data. These functions are very useful when looking for things like month over month averages and when doing calculations. The table below contain a list of function that are supported starting with 2012 along with some examples of how to use them.
Continue reading “SQL Server Functions”
In this post, I will use T-SQL and Microsoft C# to create a application to log file metadata into a SQL Server table.
Often times when processing data into and between data systems you need away to determine if all the data that is in the file is added to your tables. I like to store file information in the database and use this information as a way to true-up the data, in my data systems. When a file is stored on a file system it is stored with metadata that can provide very useful information. This information include file name, Creation time, Last Modified Time, File Path, File Size and you can all so get row count information. Collecting this information requires you to create a table inside the database to store the file metadata. You will also need to create an application to pull the metadata information from the files system folder directories. Continue reading “Import File Logging Application”
Sometime as a ETL developer or Database Administrator you will need to gain insight into SQL Agent job executions times. This insight can be used to proactively monitor the processing times of the various jobs running within your data environment. Continue reading “SQL Server Monthly Job Run Time Averages”