Use SQL Server Integration Services to Add HubSpot Contacts to MailChimp Lists (2021-09-03)

SQLServerCentral Newsletter for September 3, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Use SQL Server Integration Services to Add HubSpot Contacts to MailChimp Lists]( - [Register Now For PASS Data Community Summit 2021]( - [Create, Populate, and Subset a Pandas Dataframe from a CSV File]( - [From the SQL Server Central Blogs - Get the month end dates of last N years]( - [From the SQL Server Central Blogs - Work requests: It’s broke. Fix it.]( Question of the Day - [Database Compression Settings]( The Voice of the DBA  Daily Coping Tip Have a friendly chat with someone you don’t know well I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Enhancing Client Experiences with Data and Analysis I love sports. The idea of competing and testing yourself against others (and against yourself) is something I've enjoyed for most of my life. Often it hasn't even been important who wins; it's the experience of competing. I don't compete much anymore, but I enjoy watching and coaching sports. Teaching a group of younger people to work, train, win, lose, and enjoy sports is very fulfilling. I take statistics when I coach, and I provide those to my athletes regularly. The data is helpful for them to understand their performance and how they compare to others. The numbers don't drive my decisions, or theirs, but they do provide feedback for them about overall performance. Sometimes stats help me keep perspective; sometimes they remind me of something I've forgotten. Sometimes they help me realize a mistake is an outlier and not where I ought to focus. I ran across [an article on how data is being used in more professional sports](. This post isn't about the numbers of the game, but rather the ways in which data enhance the experience for fans. From resource needs to marketing to predictions, data is important to success in large events. The data can also make the fan experience better. Most of us are data professionals, and we know the power of data. Many business people know that data can be helpful, after all, they usually give us a never-ending queue reports to build. I think many of these business professionals can struggle with thinking more widely about how to proactively use data in planning, preparation, and prediction. These are things that data professionals have been doing for years, before the current AI/ML fad. We often used past data to help us prepare for the future. I'm not against AI/ML, but I don't think they are necessary for all predictions. Data professionals know that we can use data in many areas of life outside of sports to enhance software for the client experience. I know I enjoy seeing some of the technology advancements that add to the experience from software, and I appreciate those that provide excitement to an experience, as well as those that just ensure things run smoothly. I look forward to seeing what the future might bring as some of the incredibly creative people out there begin to use data and technology to try out new ideas. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Use SQL Server Integration Services to Add HubSpot Contacts to MailChimp Lists]( JerodJ from SQLServerCentral SSIS stands for SQL Server Integration Services and is a solution for building data integrations and workflow applications that integrate with SQL Server databases. As data integration processes increase in importance, organizations are looking for ways to simplify integration and manage workflows. In this article, we'll walk you through how to design a data flow that adds new HubSpot contacts to a MailChimp list using CData SSIS Components for HubSpot and MailChimp. Connecting to HubSpot and MailChimp The […] [Register Now For PASS Data Community Summit 2021]( Additional Articles from Redgate Registration is now open for our free and online data platform conference. Join 1000s of your data peers at PASS Data Community Summit and get access to up to 5 days of training, sessions, networking activities, and events. This year's program has been carefully designed to help you achieve your goals, wherever you are in your data career. [Create, Populate, and Subset a Pandas Dataframe from a CSV File]( Additional Articles from MSSQLTips.com In this article we demonstrate how to transfer data via a CSV file from a SQL Server database to a Pandas dataframe and then subset the dataframe in Python. From the SQL Server Central Blogs - [Get the month end dates of last N years]( Brahmanand Shukla from SQL Server Carpenter Here is a simple T-SQL script that may come handy if you need the month end dates of last N years. N is the number of years. The output... From the SQL Server Central Blogs - [Work requests: It’s broke. Fix it.]( Kenneth.Fisher from SQLStudies Many years ago I was given a work request that literally just said: It’s broke. Fix it. I’m sure you ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Database Compression Settings I have a new SQL Server 2019 database. I want to ensure that all indexes in any database are compressed using SQL Server data compression. What should I do? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Python Universal Newlines I am using the open() method in Python to load a text file. Which parameter do I add to enable universal newlines for the file? Answer: newline=None Explanation: When using None for the newline parameter, universal newlines are enabled. Ref: Python open() - [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 - Administration [What other useful DBA reports do you suggest?]( - What other useful DBA reports do you suggest? Maybe something we would run every month or so to check that our environment is configured/running optimally. We have these so far: see screenshot. I also already have (adding soon) certain reports on block leaders and Waits report based on sp_whoisactive, report on 'Worst of the Worst […] [Availability Group Listener]( - I am working on creating listener however, the subnet is full so I can't get the ip address as server IP range. What other options I have? SQL Server 2017 - Development [Speeding up Query]( - Hello, I am new to sql and learning my way around writing views for our company. Any tips and tricks to make this query a little faster or streamlined? SELECT TOP (100) PERCENT ord.orderid, ord.lt_orderid AS LTOrderID, ord.datetimecreated, ord.BookedByDT AS DateTimeBooked, us.firstname + ' ' + us.lastname AS BookedByStaffer, ord.VoidDT AS DateTimeVoided, ord.CancelledByWho, ord.voidreason AS […] [Need help sorting based on 2 different column DateTime]( - I've this table and data as following, CreatedDate CANNOT NULL. LatestRepliedDate can NULL CREATE TABLE [dbo].[IncidentMasterSimulation]( [Id] [int] NOT NULL, [IncidentDate] [datetime] NOT NULL, [IncidentDateDisplay] [nvarchar](100) NOT NULL, [HowLong] [nvarchar](100) NOT NULL, [IncidentNo] [nvarchar](100) NOT NULL, [ProjectName] [nvarchar](100) NOT NULL, [Categories] [nvarchar](100) NOT NULL, [PriorityLevel] [nvarchar](100) NOT NULL, [IncidentDescription] [nvarchar](100) NOT NULL, [CreatedBy] [nvarchar](100) NOT […] SQL Server 2016 - Administration [How to get a SQL/CSV list of SCOM alerts and rules]( - Hello experts, I've been tasked with tuning SCOM alerts for our database environment. Getting the alerts in emails is a little overwhelming and hard to manage. I don't see a way to export a list from the SCOM console, where I have to click through alerts one by one. Does anyone know how I can […] [Bulk Copy Program (BCP) from the ODBC layer]( - I am trying to locate some documentation, some where, any where, about ASYNC_NETWORK_IO with BCP inserting rows. the wait appears in the server monitor of a customer, we have attempted to explain the wait is normal while the client is using bcp_sendrow and before the bcp_batch or bcp_end functions are called.  the server has bcp […] SQL Server 2016 - Development and T-SQL [Dictionary Lookup]( - Hi I have hundred million ID, NAME pairs stored in SQL Server as a table that is accessed by C# code, which has a responsibility to lookup NAMEs corresponding to million IDs at a time.  C# code has these million IDs stored in an array, before invoking SQL module. Columns of the SQL table: […] [SQL - SUBSTRING combined with CHARINDEX not returning desired results.]( - Hello Friends, I'm in need of some help here please. I'm trying to extract a certain part of a string. I can extract the desired part from first example below by using the using. RIGHT(SUBSTRING((ORGPATHTXT),CHARINDEX('/',ORGPATHTXT) - 8, LEN(ORGPATHTXT) - CHARINDEX('/',ORGPATHTXT )- CHARINDEX('/',ORGPATHTXT ) - CHARINDEX('/',REVERSE(ORGPATHTXT))),8) The problem is that if there is a location before […] Development - SQL Server 2014 [how to get quantity from yesterday 6pm till today 6pm]( - Hi, I am looking to create a case statement that gets different quantities. For example, I would like to see how many orders were created between 6pm yesterday till 6pm today, how much created between 3pm yesterday till 3pm today, and how much was dispatched between 3pm yesterday till 3pm today. I created this case […] SQL 2012 - General [Database Integrity Issues]( - To avoid a lot of contention during peak business hours we separated our integrity checks into weekday and a weekend run. During the week we do a PHYSICAL only and on weekends when activity is lesser, we run a full blown integrity check. While it appears to be inconsistent, sometimes we get errors for a […] [Rounding problem (?) using DATEDIFF (minutes)]( - I have a query that is using DATEDIFF to calculate the number of minutes, of course as an integer, but I may need the extra precision of showing this as a decimal (?). Also I'm looking for a way of summing the time based on "EmpID" (using CTE?): SELECT T1.[EmpID], Convert(Datetime,T1.[Time]) AS [Time], MIN(Convert(Datetime,T2.[Time])) AS […] [Data Access in DBMS and RDBMS]( - Hello All, I want to know which one is better for Data Access point of view between DBMS and RDBMS? Yesterday I found this source and according to it In DBMS, you can access only a single file from a single database but not an idea about DBMS? Can anyone explain me with some examples? SQL Server Newbies [Performance degradation with a particular stored procedure]( - Hello, I'm attempting to make sense out of a performance issue I'm seeing with one of our applications. Basically, there's a screen in the app that displays equipment records, and a search filter that our users can make use of. Throughout the day, several users are running searches on this equipment screen. Some of these […] SQL Azure - Administration [Enable Automatic tuning sur instance Azure]( - Hello , Is the activation of the Automatic tuning function on an Azure SQL manager instance equivalent to the recompile operation If I activate it SQL will calculate the execution plan for all the ad-hoc queries suddenly will generate more consumption on the CPU side Is it recommended to activate this function on the Azure […] SQLServerCentral.com Website Issues [Send private message to another site member]( - I wish to send a private message to another member of this site...been away for a few years and have forgotten how to do this. All advice is appreciated. Thanks JLS   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -