How to create dynamic connection manager in SSIS

 STEP-1

• Create variables for User Id and Password as shown below.

STEP-2

• On the connection Manager right click and create a New connection
• After Creating, right click on the created connection > Properties
• On Properties click the 3 dots at the right end as shown below.
  
  
  
  
  
  
• Now select the Properties as shown on the left and assign variables to the properties which you created as shown below in the right

• Once the connection is created, you will see the fx before the connection as shown below

Update a certain rows in a column to NULL in Python data frame

 You can achieve this by using the pandas library in Python. Below is the code to update the USL and LSL columns to null when the SpecTypeId is not equal to 5.

import pandas as pd
import numpy as np

# Assuming df is your data frame
partspec_insert = df[['PartId', 'PartRev', 'TestCodeId', 'SpecTypeId', 'Nominal', 'USL', 'LSL', 'TMD', 'CustomerCoA']]

# Update USL and LSL to null when SpecTypeId is not equal to 5
partspec_insert.loc[partspec_insert['SpecTypeId'] != 5, ['USL', 'LSL']] = np.nan

# Display the updated data frame
print(partspec_insert)

Explanation:

1. partspec_insert is created by selecting the specified columns from the original data frame df.
2. The loc method is used to locate rows where SpecTypeId is not equal to 5 and update the USL and LSL columns to null (represented by np.nan in pandas).

Adding Parameters in SQL Query & Passing Dynamically

References: https://learn.microsoft.com/en-us/shows/mvp-azure/pass-parameter-to-sql-queries-statement-using-power-bi

https://www.youtube.com/watch?app=desktop&v=3wvnjHPGNQQ&t=0s

Power BI Quick Tip: Using Dynamic M Query Parameters with SQL​​

Step-1 : Create Parameter in Power Bi

Create a Parameter in power Query Under Manage Parameter > New Parameter

Step-2 : Change values to parameter in where clause of the query

• Go to "Home" > "Transform Data" to open Power Query Editor.
• Select the table or query you want to modify.
• Click on the "Advanced Editor" and modify the query to include the parameters.

let

    // Convert the parameters to text
    P_YearText = Number.ToText(P_Year),
    P_SupplierCodeText = Text.From(P_SupplierCode),

    // Define the SQL query with the parameters
    Source = Sql.Database("dcazd365dtmigsql01.database.windows.net", "SET", [Query="SELECT * FROM [LSQD].[vwGsqaCoaDataDownload] WHERE [ShipmentYear] = '" & P_YearText & "' AND [SupplierCode] = '" & P_SupplierCodeText & "'"])
in
    Source

Step-3 : Bind the Parameter to the columns of the Table

• Go to Data Model ->Select the column you want to bind > Properties > Advanced > Bind to Parameter > Select the Parameter you want to bind

Column Store Index vs Row Store Index

 References: 

https://www.youtube.com/watch?v=k9DpO91W76o

Syntax

Example

• You can create only one column store index, Either Clustered or non-clustered

Storage comparison

Optimizing SQL Queries

References:

1) https://www.youtube.com/watch?v=Iyr7KQUCb0M

2) https://assets.red-gate.com/community/books/inside-the-sql-server-query-optimizer.pdf

FILTER DATA

•  Avoid Using Unnecessary DISTINCT & ORDER BY
• Create Non-clustered index on frequently used columns in WHERE Clause
• Avoid applying Functions to columns in Where Clause

• Avoid Leading Wildcards as they prevent index usage

• Ensure Columns used in ON clause are Indexed

FILTER DATA

• Filter before Joining

• Aggregate before joining
  
• use UNION instead of OR in JOINS

• Use SQL HINTS when you are joining small table with big table

• Use UNION ALL instead of using UNION when duplicates are acceptable
• use UNION ALL +DISTINCT when duplicates are not acceptable
  

AGGREGATING DATA

• Use COLUMNSTORE INDEX for aggregations on large tables.

• Pre-aggregate the data and store it in new table for reporting

SUBQUERIES

• Don't use IN opeartor instead use JOIN or EXISTS
• EXISTS is better that JOIN

CREATING TABLES

• Avoid data types VARCHAR & TEXT
• Use NOT NULL constraint where applicable
• Ensure all your table have a Clustered Primary Key
• Create a non-clustered index for foreign keys that are used frequently.

INDEXING

• Avoid over indexing
• Drop unused Indexes
• Update Statistics (Weekly)
• Reorganize & Rebuild Indexes (Weekly)
• Partition your tables if you data is large. & Do column store index to enhance performance.

How to update Identity Column in SQL Server

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(UserId, otherCol) VALUES(13,'myValue')
GO

--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF