A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Brush Up on Your ETL Skills

Many of us that work with data will find requests and demands to import or export data at some point. Plenty of us have regular processes that perform these actions, and we may regularly troubleshoot or enhance these activities. In fact, I know some people have a full time, or nearly full time, position just dealing with ETL operations.

Working with data in disparate formats and the myriad of inconsistencies even when formats are known is a challenge. Integration Services is a useful tool, but many us find that we need to pre or post process data separate from a simple import or export. Some of us may prefer using T-SQL or other languages, such as R or Python, to process data rather than programming SSIS. It seems that I often find that every client wants a slightly different format or change to their data that a simple query export won't handle.

These days, as we add in Machine Learning and other downstream processing activities, it seems that there is more and more of a need to process data beyond imports and exports. After all, it seems that the majority of the time in any ML project is spent preparing and transforming data. In addition, in Article 15 of the GDPR, there is language that notes a data subject has the right to request a copy of the data relating to them when it is being processed by an organization. I don't know how often someone will want to get data about themselves or their organization, but I'm sure it will happen more than it happens today.

I think this means I'll need to brush up on ETL skills, perhaps to ensure I can easily extract out a copy of an individual's data. In fact, I probably should compile some scripts now to ensure I can let someone know what we information keep at SQLServerCentral that would fall under GDPR. I think it's just email addresses, but I could be wrong.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Continuous Delivery for SQL Server Databases

Spend less time managing deployment pain and more time adding value. Find out how with database DevOps

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

Featured Contents

Stairway to Database Design Level 9: Normalization

Joe Celko from SQLServerCentral.com

In the final step of Database Design, Joe Celko gives a simple but effective explanation of the normalization process and why it is important. More »

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. More »

SQL Server Reporting Services 2017 Tutorial

Additional Articles from MSSQLTips.com

SQL Server Reporting Services has been the go to reporting solution for SQL Server for almost a decade now. In many organizations, the ease of development, web front end and security make SSRS the tool of choice. More »

From the SQLServerCentral Blogs - 4 Rules of Right When Running a Query

SQLRUs from SQLServerCentral Blogs

Last month in January, I participated in the Idera #sqlchat. This is a monthly chat meeting that is organized by... More »

From the SQLServerCentral Blogs - Power BI with different Network Visualizations

Rayis Imayev from SQLServerCentral Blogs

(2018-Feb-10) A few days ago Microsoft provided another update for its desktop version of the Power BI data analytical tool... More »

Question of the Day

Today's Question (by Steve Jones):

I've got this data set:

 rank player.name year2017 yards2017 1 1 Tom Brady 2017 4577 2 2 Philip Rivers 2017 4515 3 3 Matthew Stafford 2017 4446 4 4 Drew Brees 2017 4334 5 5 Ben Roethlisberger 2017 4251

I want to add a column to track how many yards each person is trailing the leader. How can I add a column to this data set and populate it with the number of yards behind the leader?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: R Language.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I want to set some session key-value pairs using sp_set_session_context. Since this might be used in a number of places in my application, I'm concerned that developers might set too many pairs. What is the maximum amount of data I can store with this feature?

Answer: 256kb

Explanation:

The maximum size of all session context is 256kb.

Ref: sp_set_session_context - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

DBMirroring SQL2017 - SQL2017 not starting !?! - Testing SQL2017 I've come to DBMirroring I know AG is the "new" way of handling that desire, but DBMirroring is...

SQL Server 2017 : SQL Server 2017 - Development

5 years or older - Guys, if i have a list of dates in a table how do i query how many of the dates...

SQL Server 2016 : SQL Server 2016 - Administration

Transaction Log Growth - Hi, I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have...

Showplan - Started a new role, have limited (read) rights to the server, I have asked for showplan rights but have been...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Triggers or best option - Hi, This is a little hard to explain, but I'll have a go :) We have several tables in 2 databases that...

Performance improvement following a disable indexes, shrink, enable indexes - but why? - I'm stumped and need the assistance of the wider community to understand what is going on. The database in question has...

SQL Server 2014 : Administration - SQL Server 2014

Adding multiple triggers to same table - Recommendations/Best Practices - Hi Folks, I have a requirement to add additional triggers(INSERT/UPDATE) for a table which already have INSERT/UPDATE/DELETE triggers. This is for...

SQL Server 2014 : Development - SQL Server 2014

Need a SQL Query to find numeric values - Hi Experts, Can you all help me out to find a query in which i can find all the tables in...

Looking to sort two queries by Date, Time - SELECT LTRIM(RIGHT(CONVERT(varchar(6), .,100),7)) AS --, FORMAT(., 'MM-dd-yyyy') AS , CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), .,101),'/','-')) AS , LTRIM(RIGHT(CONVERT(varchar(20),

SQL Server 2012 : SQL 2012 - General

How to import a Excel file into SQL table directly? - How to import a Excel file into SQL table directly? Before, I used vb.net to import Excel file into a datatable...

SQL Server 2012 : SQL Server 2012 - T-SQL

finding the right index - We use a program for EDI in our company. One of the screens shows us a list of sent documents. ...

Need to get consecutive duplicate rows - I need a query that can get me (the ID of) consecutive duplicate rows, where I am grouping on some...

SQL Server 2008 : SQL Server 2008 - General

Replication not working but replication monitor says all ok!! - Hi, Earlier on today one of our DBs started to fall behind and isn't replicating anymore - normally opening up Replication Monitor...

Cloud Computing : SQL Azure - Administration

Issue with using Ola Hallengren Backup script - Hi, I am using OH's excellent scripts, however I have found and issue when it comes to backing up to...

Reporting Services : Reporting Services

SSRS Reportserver parallel testing - Is there a way to run all reports on server 1 and server 2 and compare the results. So run...

Data Warehousing : Integration Services

Error uploading jgp - i have an SSIS package that takes a image in my database and uploads it into a network location that...

Need idea on exporting column data to separate file -ssis - Hi all, I need a help to generate XML file from a table output and Create main folder and subfolder...

The Microsoft Data Warehouse Toolkit -- are either of these books still relevant to learn? - Hi, I would like to learn how to design and build a full, end-to-end Data Warehouse and Analytical solution using the...

SQL Server 2005 : SQL Server 2005 General Discussion

SSIS Package Problem - Sybase Connection - Hi guys, We are in the process of upgrading to 2005 from 2000. We have a lot of legacy DTS packages...

Microsoft Access : Microsoft Access

Migrated DB to 2016, having ODBC Access problem - Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.