Loading Non Duplicated Data Using SSIS (SQLServerCentral 1/31/2017)

[SQLServerCentral - www.sqlservercentral.com] A community of more than 1,600,000 database professionals and growing Featured Contents - [Loading Non Duplicated Data Using SSIS] - [SQL Server v.Next : STRING_AGG Performance, Part 2] - [How to Count the Total Number of Rows Across Multiple Tables] (From the SQLServerCentral Blogs) - [Power BI Custom Visuals Class (Module 35 – Box and Whiskers (Jan Pieter))] (From the SQLServerCentral Blogs) Featured Script - [AdventureWorks2016CTP3] The Voice of the DBA A Wide Variation in Skill I was helping someone recently clear up some confusion about the encryption structure of their TDE databases. This individual wasn't sure about how the various keys were used, which ones needed to be backed up, and which keys needed to be restored. As this was an online discussion, anyone could read the posts and add their own thoughts. There was a point where one person chimed in that the process was really easy and anyone should be able to complete it. I do think that working with TDE is easy, but I also think that its easy for me because I have experience and practice with the technology. There are nuances to undestanding how the piece fit together and what needs to happen in order to ensure your system operates smoothly during both deployment and in disaster recovery situations. Since mistakes can result in lost data, I understand why people might feel intimidated or nervous, but once they practice a few skills and gain knowledge, I find they get the confidence to proceed. Their fear is often because of ignorance, which is understandable. All of us have things to learn, especially in technology where the rate of change is quite high. Before we learn something, or even while we're trying to understand concepts, various tasks might seem quite difficult, or even complex. These might be simple tasks, such as restoring a certificate or sending a set of objects through a Powershell pipeline or writing a unit test. Just because you can do something doesn't mean everyone can. Just because one person finds a task easy doesn't mean others won't find it complex. The knowledge that each of us gathers over time isn't consistent or easily mapped. We all learn different skills at different times, in varying orders and to fluctuating depths. One of the very frustrating things about technology is that there isn't a defined path for a particular technology, or even job. Someone that begins working with SQL Server might learn T-SQL first, or backups, and not get to the user/role/GRANT security model for some time. Even then someone might easily add logins and users to roles, granting permissions, but being unaware of the difference between REVOKE and DENY, or be confused by the WITH GRANT option. I am not excusing the inability of someone to be competent at their job, but I do think that ignorance deserves some understanding and tolerance. While you might be annoyed that someone doesn't know how to take a COPY_ONLY backup, I would hope you would treat them as a colleague that needs to learn a new skill and not chastise them if this isn't something they've every dealth with before. If they can't learn this skill and make mistakes over and over, then that's a different situation and perhaps they aren't competent. We should understand ignorance once or twice, but after that the person might need to find a new job. I see plenty of people that are constantly working to improve their skills and get better at their jobs. I also see plenty of people content to continue their career with the same level of knowledge they've had for a long time, unable or unwilling to change and produce better code or faster systems. I also see plenty of skilled individuals treat both groups of people with the same level of scorn, contempt, or shame. I saw a comment posted recently about how Linux supporters will often mock people using Windows and then deride the same people for asking questions when they switch to Linux. I would hope that our SQL community would treat others better than that. 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] ( 4.5MB) podcast or subscribe to the feed at [iTunes] and [Libsyn]. [feed] The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [Help your whole team WRITE BETTER, SHAREABLE SQL FASTER] Featured Contents  [Loading Non Duplicated Data Using SSIS] Rahul Gupta from [SQLServerCentral.com] Learn how you can easily load data from a file that isn't duplicated, discarding those rows that are.[More »] ---------------------------------------------------------------  [SQL Server v.Next : STRING_AGG Performance, Part 2] Additional Articles from [SQLPerformance.com] Aaron Bertrand starts digging deeper into the performance of string concatenation methods STRING_AGG and FOR XML PATH.[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [How to Count the Total Number of Rows Across Multiple Tables] David Postlethwaite from [SQLServerCentral Blogs] I have been working on SQL Server Consulting assignment,  a SQL Server 2014 migration for a new client over the...[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [Power BI Custom Visuals Class (Module 35 – Box and Whiskers (Jan Pieter))] Devin Knight from [SQLServerCentral Blogs] In this module you will learn how to use the Box and Whiskers (Jan Pieter) Power BI Custom Visual. This...[More »] Question of the Day Today's Question (by Steve Jones): What are the complex built-in datatypes for U-SQL? (choose 2) 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: U-SQL. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center]. ADVERTISEMENT [Professional Microsoft SQL Server 2014 Integration Services] Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. [Get your copy from Amazon today]. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): What are the modes I can choose for transactions commiting (availability mode) between Availability Groups in SQL Server 2016 in the ALTER AVAILABILITY GROUP command? Answer: SYNCHRONOUS_COMMIT and ASYNCHRONOUS_COMMIT Explanation: There are only two settings for the availability mode: synchronous and asynchronous. Ref: ALTER AVAILABILITY GROUP - [ --------------------------------------------------------------- [» Discuss this question and answer on the forums] Featured Script [AdventureWorks2016CTP3] Mick Rust from [SQLServerCentral.com] With the JSON sample scripts supplied with AdventureWorks2016CTP3 the code: SELECT SalesOrderNumber, OrderDate, ShipDate, Status, AccountNumber, TotalDue, JSON_VALUE(Info, '$.ShippingInfo.Province') as [Shipping Province], JSON_VALUE(Info, '$.ShippingInfo.Method') as [Shipping Method], JSON_VALUE(Info, '$.ShippingInfo.ShipRate') as ShipRate, JSON_VALUE(Info, '$.BillingInfo.Address') as [Billing Address], JSON_VALUE(Info, '$.SalesPerson.Name') as [Sales Person], JSON_VALUE(Info, '$.Customer.Name') as Customer FROM Sales.SalesOrder_json --WHERE JSON_VALUE(Info, '$.Customer.Name') = 'Edwin Shen' Only returns NULL If you set the JSON to be strict JSON_VALUE(Info, '$.ShippingInfo.Province') as [Shipping Province] Msg 13608, Level 16, State 5, Line 77 Property cannot be found on the specified JSON path. The code when generated is an array so to access it you need to add [0] to retrieve the values. SELECT info,SalesOrderNumber, OrderDate, ShipDate, Status, AccountNumber, TotalDue, JSON_VALUE(Info, '$[0].ShippingInfo.Province') as [Shipping Province], JSON_VALUE(Info, '$[0].ShippingInfo.Method') as [Shipping Method], JSON_VALUE(Info, '$[0].ShippingInfo.ShipRate') as ShipRate, JSON_VALUE(Info, '$[0].BillingInfo.Address') as [Billing Address], JSON_VALUE(Info, '$[0].SalesPerson.Name') as [Sales Person]--, -- JSON_VALUE(Info, '$[0].Customer.Name') as Customer, * FROM Sales.SalesOrder_json WHERE JSON_VALUE(Info, '$[0].SalesPerson.Name') = 'David Campbell' Hope this helps someone. [More »] 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 2016] : [SQL Server 2016 - Administration] [Log shipping VS Mirroring] - Hello All, What is the main different between those HADR solution: 1) Log shipping 2) Mirroring Why should i choose Log shipping over Mirroring? Thanks... [SQL Server 2016 Express Installation Error] - I have been trying to install SQL Server 2016 Express on my computer with OS Win 10 Home and am... --------------------------------------------------------------- [SQL Server 2014] : [Administration - SQL Server 2014] [SQL Server Setup Failure] - We are using SQL Server 2014 and I am getting the error "SQL Server Setup has encountered the following error: The... [Execution plan estimates Join hint spilling.] - Hi, I've been troubleshooting a performance problem with a stored procedure where depending on the parameters the row count on two... [Cost threshold for parallelism] - Hi guys, I have a question that I have read and heard differing opinions on in the past. Cost threshold for parallelism... --------------------------------------------------------------- [SQL Server 2014] : [Development - SQL Server 2014] [How to get MIN,MAX and AVG time] - I have a logic in my Stored procedure ,where I want to check the timings of min,max and avg timings. If (@ID... --------------------------------------------------------------- [SQL Server 2012] : [SQL 2012 - General] [SSIS Deployment error Need help] - Hello all, I have an SQL 2012 standard ED. and also have SSDT 2015 installed.  I have an SSIS package that... [Latency issues in Transactional Replication] - Hi, We have two SQL server 2012 bidirectional transactional replications working correctly. Instance A (active) <-> Instance B (passive - not in use) Instance... [SQL Server 2012 Upgrade Advisor?] - Has anyone downloaded or know where I can get SQL Server 2012 upgrade advisor? The link on Microsoft site is... --------------------------------------------------------------- [SQL Server 2012] : [SQL Server 2012 - T-SQL] [Age as of January 1st] - Hello all, I'm really strugling on this age calcualtion. I need to find the age of a patient based off January... [Restore database with move] - Hi everybody, I have a script to restore a database with move. I do not have a database yet for this... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server 2008 - General] [How can I prevent a record from appearing based on two columns] - Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the... [Need help with bcp] - I have created a job to export the results of a stored procedure to a folder on our shared drive... [SQL Server 2008 R2 slow query performance to Oracle 11g using Openquery] - Gang, I'm completely perplexed on this one. I am running a query against an Oracle 11g database. It looks something like... --------------------------------------------------------------- [SQL Server 2008] : [T-SQL (SS2K8)] [Help on query based on condition] - Hello friends hopefully you can assist me In my query, I need to frame a condition like Count of activities of activity category... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server 2008 Administration] [Log not available error 9001] - Came in to work this morning to face a bunch of alerts for severity 21 errors. "DESCRIPTION: The log for database 'SpotlightManagementFramework'... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server 2008 Performance Tuning] [Reclaiming space after dropping index] - Hi All, I've identifying duplicate index on one of the database using this query. WITH indexcols AS (SELECT object_id AS id,     ... --------------------------------------------------------------- [Data Warehousing] : [Integration Services] [Protection Level] - So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt... --------------------------------------------------------------- [SQLServerCentral.com] : [SQLServerCentral.com Website Issues] [Code pasting still not 100%] - When pasting code into the forums, it's still really not where it used to be. The cursor seems to go... --------------------------------------------------------------- [SQL Server 7,2000] : [General] [Server Audit] - Hi all I've been given the task of auditing our entire SQL estate as no-one is keeping track of the servers. I've... This email has been sent to [{EMAIL}]. 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]. --------------------------------------------------------------- This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: [webmaster@sqlservercentral.com]