How to run SSIS Project as a SQL Job (SQLServerCentral 1/6/2017)

[SQLServerCentral - www.sqlservercentral.com] A community of more than 1,600,000 database professionals and growing Featured Contents - [How to run SSIS Project as a SQL Job] - [An Introduction to SQL Server Containers] - [SQL Server Ad Hoc Access to OLE DB Provider Has Been Denied Error] - [Checking Permissions for Keys–#SQLNewBlogger] (From the SQLServerCentral Blogs) - [Data Channel - Interview 02 - Hamid Fard on SQL Server Memory Capacity Planning] (From the SQLServerCentral Blogs) Featured Script - [Extracting string after and before a Character/Pattern] The Voice of the DBA Your 2017 Goals Last week I wrote some predictions for 2017, and plenty of you added your own thoughts. Overall, security is a concern for most people, with little hope things will improve. I certainly hope they do, but like most of you, I think expediency and profit will overwhelm any chance that organizations will make patching their systems a priority, much less improving their data security. As the new year came and went, I ran across a [great post from Buck Woody] that's a few years old. In the post, Buck notes that he likes to make goals in the new year with his family, not resolutions. That sounds like semantics, but I agree with Buck. There is power in making goals instead of open ended changes. Goals can help drive you, especially if they're measurable and time boxed. Having someone else keep you accountable also helps. This week, as we start a new year of our careers, I wanted to ask you what goals you might have. List a few and think about asking a friend, spouse, parent, child, someone else to help hold you accountable and check on your progress each month. If you have just a few goals, chances are that you will achieve some of them. Don't forget, you can always add a few more later in the year if you finish everything. Perhaps you want to learn something about a new technology. If that's the case, make a specific goal. Don't try to "learn Azure", but instead think about "building a database to track my own movie reviews in Azure and use it for a year." If you want to get better at a specific technology, like T-SQL, then think about challenging yourself with a specific set of problems, like [the T-SQL challenge]. Or complete something like the [Advent of Code] in T-SQL. Set aside some time to improve your career and take a step forward. Data professionals working with SQL Server have no shortage of new features, subsystems, and solutions to practice with as our platform grows wide and deeper each year. 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] ( 3.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 [SQL Compare] New Redgate SQL Compare 12 has landed! SQL Compare 12 has landed with a brand new user interface, support for SQL Server 2016, and a wealth of fixes and improvements. Check out this blog post from Redgate's Carly Meichen to hear more about what’s new, why the team have built it, and how. [Read now.] [DLM Dashboard] Track schema changes for free DLM Dashboard tracks SQL Server databases to show you exactly what schema changes have been made, by who, and when. You get a full history, with line-by-line differences, and a clear audit trail of your database moving from development to production. [Download free tool.] [SQL Source Control] How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more.] Featured Contents  [How to run SSIS Project as a SQL Job] Philip Horan from [SQLServerCentral.com] In this piece by Pilip Horan, learn how to create an SSIS catalog and run a project as a SQL Agent Job.[More »] ---------------------------------------------------------------  [An Introduction to SQL Server Containers] Paul Stanton from [SQLServerCentral.com] Get the basics of what a container is and how this can work with SQL Server.[More »] ---------------------------------------------------------------  [SQL Server Ad Hoc Access to OLE DB Provider Has Been Denied Error] Additional Articles from [MSSQLTips.com] I was trying to use OPENROWSET to query a remote database and I experienced the following error "Ad hoc access to OLE DB provider has been denied. You must access this provider through a linked server". When I ran this same query using a sysadmin account it worked fine. Why doesn't this work for other logins?[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [Checking Permissions for Keys–#SQLNewBlogger] Steve Jones from [SQLServerCentral Blogs] Another post for me that is simple and hopefully serves as an example for people trying to get blogging as...[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [Data Channel - Interview 02 - Hamid Fard on SQL Server Memory Capacity Planning] Dear All, Welcome to the second interview of Data Channel. Very happy and proud to have Mr Hamid Fard, MVP and...[More »] Question of the Day Today's Question (by Steve Jones): I try to drop a user in a database and get this error: Msg 15136, Level 16, State 1, Line 2 The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped. What is the issue here? 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: Security. 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 The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI - This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. [Get your copy from Amazon today]. Yesterday's Question of the Day Yesterday's Question (by Uwe Ricken): You host a vendor database on your Microsoft SQL Server 2016. Sometimes the users complain about performance degradation when a simple query with a JOIN between two tables is executing. They ask you for the best solution to resolve their problems. Scenario For demo purposes the scenario is simple. There are two tables, one table [dbo].[messages] contains all messages of the application. Another table [dbo].[languages] will be used to JOIN it against [dbo].[messages] on the language_id. Table definitions CREATE TABLE dbo.messages ( message_id INT, language_id INT, severity TINYINT, is_event_logged BIT, [text] CHAR(2048) ); GO CREATE TABLE dbo.languages ( language_id INT, language VARCHAR(10) ); GO The table [dbo].[messages] gets 8,000 records for english users and 100 records for german users. BEGIN TRANSACTION -- Insert 8,000 message lines in english INSERT INTO dbo.messages WITH (TABLOCK) SELECT TOP (8000) message_id, language_id, severity, is_event_logged, text FROM sys.messages WHERE language_id = 1033; GO -- Insert 100 message llines in german (deutsch) INSERT INTO dbo.messages WITH (TABLOCK) SELECT TOP (100) message_id, language_id, severity, is_event_logged, text FROM sys.messages WHERE language_id = 1031; GO -- Insert available languages for seletion INSERT INTO dbo.languages (language_id, language) VALUES (1033, 'english'), (1031, 'deutsch'); GO COMMIT TRANSACTION; GO -- when all data have been inserted a helping index on language_id will be created CREATE NONCLUSTERED INDEX nix_messages_language_id ON dbo.messages (language_id); GO Cross Check Queries Before you check the user queries you run a simple check that all statistics on [dbo].[messages] are ok. SELECT * FROM dbo.messages WHERE language_id = 1033; GO SELECT * FROM dbo.messages WHERE language_id = 1031; GO The picture shows a good and healthy condition on the statistics object for the index. User queries The users show you a typical query which they run against the database. SELECT m.message_id, m.text, l.language FROM dbo.messages AS M INNER JOIN dbo.languages AS L ON (M.language_id = L.language_id) WHERE L.language = 'english'; GO SELECT m.message_id, m.text, l.language FROM dbo.messages AS M INNER JOIN dbo.languages AS L ON (M.language_id = L.language_id) WHERE L.language = 'deutsch'; GO When you look at the execution plan you see a wrong estimate in both queries. All queries have an estimate of 4,050 records! What can you do to get a better execution plan? Answer: You create filtered statistics on [language_id] in [dbo].[languages] for each language Explanation: In the optimization phase of the query execution Microsoft SQL Server does not know what [language_id] will be returned for the predicate 'deutsch' or 'english'. For Microsoft SQL Server this is a guessing game! So Microsoft SQL Server does only know that 1 record will be returned from the outer table in the JOIN. This information is mandatory for the estimated rows coming from the inner table. Due to the fact that Microsoft SQL Server cannot use the histogram (we don't know the [language_id] at compile time!) it uses the density vector of the statistics. The inner table [dbo].[messages] does have 8,100 records with 2 different languages. The density is 1 / 2 = 0.50 = 50%. So Microsoft SQL Server will always estimate 4,050 records for the inner table. To assist Microsoft SQL Server with the "guessing game" you have to implement filtered statistics to support the query optimizer. You have to create filtered statistics on the [language_id] attribute in [dbo].[languages] with a filter for each predicate value (i.e. english, german) because this is the predicate what Microsoft SQL Server has to investigate BEFORE it compiles an execution plan! CREATE STATISTICS st_languages_id_english ON dbo.languages (language_Id) WHERE language = 'english'; GO CREATE STATISTICS st_languages_id_german ON dbo.languages (language_Id) WHERE language = 'deutsch'; GO Now Microsoft SQL Server will load the statistics and can determine the valid [Id] value for the JOIN. This technique makes it possible for the database engine to use the histogram of the index on dbo.messages (language_id). - A clustered index on the language_id in [dbo].[languages] will not solve the problem of wrong estimated rows. It might avoid a hash join but nothing else! - A nonclustered index on the [language] attribute will optimize the access strategy for the table. A nonclustered index on [language] will change a SCAN to a SEEK but it is useless for the correct estimates of rows. - sp_updatestats will not help because all statistics are up to date Thank you for your participation to my QotD. I hope you enjoyed it :) Useful links Statistics: [ Statistic Properties: [ Parse, Compile and Optimize: [ --------------------------------------------------------------- [» Discuss this question and answer on the forums] Featured Script [Extracting string after and before a Character/Pattern] Srikanth from [SQLServerCentral.com] Usually we see lof of codes flying around for this extraction.Most of them difficult to remember. An easy way is to get hold of the basics. Function used : SUBSTRING,CHARINDEX Substring syntax : SUBSTRING(string to search, position to start, length of characters to be extracted) CHARINDEX (character to search, string to search) returns the position of the character in the string. If we want to extract before the character you would put the charindex as the number of characters and start position as 0 in the substring function [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 - Development and T-SQL] [Any tips on speeding this SQL up would be most helpful] - Basic DB Schema (I am using MSSQL 2016 Web Edition) [quote]ub_master ------------------------ ub_master_id (int) (Primary Key) a_site_id (int) importversion (int) ub_batch_sub_number (int) date_discharged (date) ub_charge ------------------------- ub_charge_id (int) (Primary Key) ub_master_id... --------------------------------------------------------------- [SQL Server 2014] : [Administration - SQL Server 2014] [Log Shipping with Cluster Shared Volumes and SQL Virtual Name] - Hi, SQL 2014 STD Edition. We have been trying to set-up Log Shipping on a 2 node cluster which is using Clustered... --------------------------------------------------------------- [SQL Server 2014] : [Development - SQL Server 2014] [Renaming fields and combining them into one column] - I have a table with the following structure. Ind_Id | First_Name | Last_Name | FootBall | Soccer | Baseball | Basketball | Volleyball | Fuseball The sports columns have... [First occurrences] - Hi, Below is the table and results I am looking for......i am an SQL newbie. Game Play_Id Team Player EVENT 1 1 1 2 A ... [Overlapping datetime ranges.] - Overlap in ranges. I have a set of data,rows which a start_time and a end_time. Each row should get a New_starttime, so... --------------------------------------------------------------- [SQL Server 2012] : [SQL 2012 - General] [Deadlock Problem on a Heap Table] - I came across an older article, , while trying to find a solution to a deadlock problem I am seeing... [The SQL Server Network Interface library could not register the Service Principal Name (SPN) but KERBEROS connections are possible] - After rebooting the server running Windows Server 2012 and SQL Server 2012 there was are an entry in the error... --------------------------------------------------------------- [SQL Server 2012] : [SQL Server 2012 - T-SQL] [Split Function Usage for Multiple Multi-Select Parameters] - I have the task to convert our company reports from query embedded reports to stored procedures. Some of the reports require... [Non-alphanumeric Search Frustration] - I am trying to search a column in a table for non-alphanumeric characters (.,% etc) so I can report these issues... [replace null with another column] - How can I write code to replace null with another column Example: firstname is null .....replace with firstname1 if firstname1 is... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server 2008 - General] [How to update column value] - I have a sql code like below in my stored procedure: INSERT INTO PRIOR_DIST (_ID, [b]COSTS_DST[/b], IS_ACTIVE) SELECT @_id, [b]COSTS_DST[/b], 'Y' ... [continued entertainment with xp_cmdshell] - Previously, I changed the service acct to domain on a SQl 2008 R2 machine and received an SPN error. The machine... --------------------------------------------------------------- [SQL Server 2008] : [T-SQL (SS2K8)] [Username increment by 1 if already exists in table] - Hi All, I am having user table as below Create table #TEMP_USER ( ID int identity (1,1) , USERNAME nvarchar(50) ); I want to insert... --------------------------------------------------------------- [Reporting Services] : [Reporting Services] [SQL Query table relationships issue] - In the query below, the relationship between the tables tblDataPermit and tblDataFees is the application number. If the application number... --------------------------------------------------------------- [Programming] : [XML] [Help with XML Splitter De-entitization, please.] - I'm writing an article on performance testing and one of the functions I'm testing is an XML CSV splitter. Here's... --------------------------------------------------------------- [Data Warehousing] : [Integration Services] [script task being completely ignored] - I have a server with SQL Server 2016 on it, using SSDT 2015, and I have tried using project deployment... [Visual Studio 2015 and SQL Server 2014] - I've been doing some limited testing and it seems that packages developed in VS2015 can be successfully deployed and executed... --------------------------------------------------------------- [Data Warehousing] : [Strategies and Ideas] [Incremental refresh and error recovery question] - I am refreshing a warehouse table nightly. The refresh is designed so that only when all the tables are successfully... --------------------------------------------------------------- [SQL Server 2005] : [SQL Server 2005 Integration Services] [Change column order in Flat File Destination] - Hi there, I have several Flat File destinations and I need to change the output column order in each. I've opened... [Database column size increased but SSIS package not reflecting the change] - We have a package with a single Data Flow Task. This has an OLEDB source connector with a sql command... 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]