Configuring an SSIS ForEach Loop for Multiple File Types (2023-12-18)

SQLServerCentral Newsletter for December 18, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Configuring an SSIS ForEach Loop for Multiple File Types]( - [Source Control with GIT, Power BI and Microsoft Fabric]( - [From the SQL Server Central Blogs - Microsoft Fabric – the great unifier]( - [From the SQL Server Central Blogs - Set-Based vs Row-Based Database Code]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Statistics Sampling Options]( The Voice of the DBA  Migrations are Worth the Effort I work with a lot of customers that are trying to find better ways to build and deploy database code. Some of them use a state or model method with SQL Compare or another Redgate product, and some want to use Flyway and its hybrid migration-based framework. It's always interesting to me how customers lean one way or the other, for various reasons. No matter which choice people make, we know that using migrations is more effort than a state-based approach. However, I think that it's a better approach, especially because no matter how simple or smooth you think your development process is, at some time there will be a situation that needs a more complex approach. a migrations-based framework allows you to handle the complexity smoothly, without depending on a person to sort out the issues. It's not just me, but many developers feel the same way. There's [an interesting article]( from a developer that gives some examples of why using migrations work well in different situations. It also acknowledges that the effort is high, but by using migrations you can accomplish complex tasks across time. something I try to get more developers to do. Don't big-bang-deploy-everything, but break things into chunks. It's not just a simple change. There are a few reasons that migrations are hard, one of which I loved. The author compares migrations to working with live electrical wires. Imagine hanging a ceiling lamp and wiring it up without turning off the power (note: don't try that). One of the best reasons I have for migrations is that lots of work isn't done at the same time, especially in a team. If each team or each piece of work is broken into separate migrations, choosing which ones to move over from developer is much easier than if all the changes are captured in a state-ful model. You still have to be aware of dependencies between migrations, but having them all separate at the beginning (and hopefully tagged to pieces of work) makes this easier. The other reason to choose migrations is that I can stage future changes. Imagine I need to make a change to add something in one migration and remove it later. Suppose we add a new tax column and then after Jan 1, we remove the old tax column. With migrations I can pre-write the removal and create a pull request with the "Tax Removal - DO NOT MERGE UNTIL 2024 JAN 1" title, which should be obvious to anyone. I like keeping things simple, which helps when I never know who might need to handle this work in the future. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Configuring an SSIS ForEach Loop for Multiple File Types]( Louis Bravo from SQLServerCentral The ForEach loop works with only one file type at a time by default. Learn how you can add code to your packages to work with multiple types of files. [Technical Article]( [Source Control with GIT, Power BI and Microsoft Fabric]( Additional Articles from SQLServerCentral Source control is fundamental when dealing with projects and sharing code between multiple developers. Power BI present some challenges related to source control. But it’s finally providing us with a solution to these challenges. Let’s analyse this piece-by-piece. [Blog Post]( From the SQL Server Central Blogs - [Microsoft Fabric – the great unifier]( James Serra from James Serra's Blog I’m seeing a lot of excitement from customers over Microsoft Fabric, now that it GA’d a few weeks ago. One thing that is generating a lot of that excitement... [Blog Post]( From the SQL Server Central Blogs - [Set-Based vs Row-Based Database Code]( Kevin3NF from Dallas DBAs In SQL Server, the terms “set-based” and “row-based” refer to different approaches or styles of writing SQL code to manipulate data. These styles have implications for performance, readability, and... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Statistics Sampling Options I want to create statistics on a column that I know will be used for querying filtering. However, this is a large table and I don't want to sample the entire table. What are my options for setting a limit on how much data is examined to create statistics? 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) Finding Database File Growths I have a SQL Server 2022 instance that I set up with all the defaults for a US installation. I am wondering when the data file for one of my databases grew. Where can I find this information? Answer: In the default trace Explanation: The default trace contains information about file growths, and this is enabled by default. The system_health session does not. Ref: - system health session - [ - default trace setting - [ - Finding File Growths - [( [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 - Development [Query]( - ServerName DatabaseName DatabaseSize(MB) Date Server DBName Size Date A DB 110 6/1/2023 A DB 113 7/1/2023 A DB 118 8/1/2023 A DB 130 9/1/2023 A DB 120 10/1/2023 A DB 140 11/1/2023 A DB 143 12/1/2023 A DB2 1110 6/1/2023 A DB2 1113 7/1/2023 A DB2 1118 8/1/2023 A DB2 1130 9/1/2023 A DB2 1120 […] SQL Server 2016 - Development and T-SQL [send csv file or txt file to sftp server]( - Hello Can someone help me with code to send file to sftp server please: Here is my code: # SQL Server query $Query = "SELECT name from tblName" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=CWR-PP02;Database=testdb;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = […] SQL Server 2019 - Development [Improving Code Readability]( - Below is a code i wanted to improve some readability and functionality: GO /****** Object: View [Prod]. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [Prod]. AS --------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT MAN_ADJ_ALL.[FY] ,MAN_ADJ_ALL.[Period] ,MAN_ADJ_ALL.[Group] --ADJUST THIRD PARTY TO ALLOCATED PLANT ,CASE WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Plant] ELSE MAN_ADJ_ALL.[Plant] END AS [Plant] […] [Selecting from a view, base table has a DENY on a column]( - This is something I've never seen before and I can't think of the right way to search properly for this, so I'd like to throw it out to this group. We have an audit table that stores the old password value when someone changes it. There is a DENY on this column for all but […] [JSON data with Pivoted]( - Hi All, I need some assistance and not sure how to achieve the expected output. I have a JSON input parameter which is a nvarchar(max) passed from my application as per my sample code below. I've started to break up the JSON into a table output and am a bit stuck on getting how I […] SQL Server 2008 - General [Merge Replication SQL 2008 (10.0.6556) - downloads slow and behind]( - Merge Replication SQL 2008 (10.0.6556) - downloads slow and behind I am working on newly virtualized sql cluster with sql 2008 and merge replication with replication of many tables on a large database (multiple TB data file). Recently, the merge replication of one of two pubs for it started to appear to only perform uploads […] Azure Data Lake [Lake Database Performance Optimization]( - I recently created a synapse link for Dataverse. The resultant Lake Database in Synapse is powering a number of Power BI reports, however the performance of the lake database is a slower than we had expected. What steps can I implement to improve the performance of the lake database? Analysis Services [Calculated Time Periods Hierarchy]( - Please help me understand why I am getting the following error: "A set has been detected that can't contain calculated members." I have Attributes "Dates" and Hierarchies "Calendar" (YearID/QuarterId/MonthId/dtDate). I've created  Calculated Member a "Current Month" for the parent hierarchy «Dates.Calendar.[all]». CREATE MEMBER CURRENTCUBE.[Dates].[Calendar].[All].[Current Month] AS StrToMember("[Dates].[Calendar].[Month Id].&[" + Format(now(), "yyyyMM") + "]"), VISIBLE = […] [Calculated Time Periods Hierarchy]( - please help me figure out why I'm getting the following error: "A set has been encountered that cannot contain calculated members."  I have Attributes "Dates" and Hierarchies "Calendar" (YearID/QuarterId/MonthId/dtDate) I created Calculater Member "Current Month" for Parent hierarchy "Dates.Calendar.[all]" CREATE MEMBER CURRENTCUBE.[Dates].[Calendar].[All].[Current Month] AS StrToMember("[Dates].[Calendar].[Month Id].&[" + Format(now(), "yyyyMM") + "]"), VISIBLE = 1 […] [Relationship between dimension and measures]( - There is a Fact Table (.....,MenuID, MOption, CallTime) There is also a Dimension "Menu" Table (MenuId,OptionID) Relationship between these tables (MenuID and MOption) In the Dimension table, I created another field - keyField (string(MenuID+MOption) as OptionId2) when creating Dimension, I use the new key (OptionId2) and build the hierarchy accordingly -MenuId -OptionId2 (ключ)  but […] SQL Server 2022 - Administration [Moving database servers - IP address change - listeners]( - Hi, We will be moving our physical database servers to a new location. Prior to the move, new IP address for all the SQL servers will be changed/updated.   Are the IP address listed above for the cluster server? After the servers are moved will I need to change the IP address for the […] [how to install smo in an offline machine]( - hi,  how do I install smo in a offline machine Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 170.18.0  PS C:\Users\Administrator> Find-Module -Repository smo WARNING: The file extension 'C:\Packages\nuget.exe' is not valid. The required file extension is '.nupkg'. Version Name Repository Description ------- ---- ---------- ----------- 21.0.17224 SqlServer SMO This module allows SQL Server developers, admin  […] SQL Server 2022 - Development [Retrieving First Word, First + Second Word, First + Second + Third Word, First]( - Hi All, I have the company names in one column. Need a sql server query to fetch in separate column like Example: ABC private limited company First Word ABC First + Second Word ABC private First + Second + Third Word ABC private limited First + Second + Third + Fourth Word ABC private limited […] [Why don't these two queries return the same data?]( - I have a table-value function that returns data for a report. However, it's not returning the correct data, so I've reworked it and it's now returning what I'd expect ... the thing is, to my obviously unseeing eyes the two queries should be functionally identical. Please can someone help? This query doesn't work: SELECT Grades.[Name] […] [SQL Challenge: Employee Salary Analytics]( - onsider an employees table: employee_id (int) employee_name (varchar) department (varchar) joining_date (date) salary (decimal) Highest Earner by Department: Find the employee with the highest salary in each department. Average Salary by Department and Year: Calculate the average salary for each department per year. Longest Serving Employee: Identify the employee with the longest tenure. Please provide […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -