Great topic! Thanks for making this video. I was telling my 6 year old daughter, who loves TH-cam Kids videos, that a TH-camr made a video for me to help me with work. She was jealous! Thanks again for all of the help.
Hi , I have 70 lakes records in 3 tables each . I need to archive values from those tables into ArchivalTable based on archival date and then delete from the main table . How to do this in SSIS ? Pls help
This won't be done in ssis instead you need to write sql queries to select data from one table and insert it into another table (of course you can use data flow task) for this. And then for deleting data you would need to write delete queries. I am not getting what issue you are facing here. Just use data flow task to copy data from one table to another table and once data is copied, then you can delete data from source table using delete query.
This video is awesome, but I was wondering if it will work for joint tables that only have 2 foreignkeys of 2 other tables. The issue is that the table doesn't have a one-column-primary-key to ntile by. Do you know what would be the workaround to that?
Hello , Can you tell how to purge data from a table later than 10 days using SSIS approach , also I should be able to change the days later based on the configuration done in another table so that there would be no need to change code later
In SSIS there is no task to purge the data. You would need to write sql queries those will delete the data and you can maybe create a stored procedure for this and use that inside an SSIS package. The input parameter to the stored procedure can be passed inside ssis package. So if required you can change the store procedure and there won't be any requirement of changing the ssis package.
I would also recommend anyone with this use case to look into using OFFSET and FETCH with an order by. This will ensure you get a deterministic result. Also adding an order by to a Top I believe is required for deterministic results. Also Ntile is not deterministic and neither is row number. I don't believe any window functions are deterministic. Just a few things to keep in mind if you require that level of precision.
Yes order by is a good option to pull the records in a sequence so that you know you data is being merged in an order. In one of SSIS package, I added a column as Status which was initially NULL and then I started pulling for example 10 k records from this table where Status is NULL order by Id and merge this data to another table. Once merged we can update status to 1 for top 10 k in source table order by Id, this way first we are merging the data to dest using an order by and then updating the status for same records in source table so that those records won't be merged again.
We are using the audit_log table here to know how many parts data have been moved so far. Thus you can use this table to get the last part merged and accordingly you can merge the next part.
Hi sir, your videos is so helpful, can you please tell me or make a video on, 1- how to load password protected Excel files 2- load many excel files in one table and every excel contains 3 sheets, we need to load all the the excels and load all the sheet in one table
Hi Suraj Yadav, this is the code in C# console app to import data from multiple excel files from multiple excel sheets and import the data into sql server table. I have made a video on "how to import multiple excel sheets to sql server table", below is the link th-cam.com/video/CMor3vY7Y-E/w-d-xo.html .So additionally we just need to add a foreach loop to loop through all files and pass the excel file forward. Below is the complete code and I will get back to you on loading data from password protected excel files. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.IO; using Microsoft.Office.Interop.Excel; using System.Data.OleDb; using System.Data.SqlClient; namespace ConsoleApp1 { internal static class Program { static void Main(string[] args) { string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string LogFolder = @"D:\Files\Logs"; string sqlConnectionString = @"Data Source=DESKTOP-EKJ1P64\SQL2019;Initial Catalog=Test;Integrated Security=True"; try { string folderPath = @"D:\Files\"; string[] excelFiles = Directory.GetFiles(folderPath, "*.xls*"); foreach (string filePath in excelFiles) { // Open the Excel file and retrieve the data Application excel = new Application(); Workbook wb = excel.Workbooks.Open(filePath); // Loop through all the sheets in the workbook for (int i = 1; i
If you right click on SQL server instance then you will find an option to restart it. Just click on restart and it will restart the SQL server instance as well as the SQL job.
@@TKJ using the command prompt you can stop the execution of sql server NET STOP MSSQL$SQL2019 you can create a batch file and execute it using windows tasks scheduler then you can start it using below command NET START MSSQL$SQL2019 In above line after the $ it is the instance name, you can get more information on this here www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/
@@TKJ both will restart. SQL agent restart type should be automatic not manual then after restart of SQL server , the SQL server agent should also get started.
Uso del NTILE, FOR, RecordInserted, For Loop Container, el (DT_WSTR,12) SQL command from variable con variable user, other transforms vs Row Count vs variable, execute SQL Task with SQLStatement Source and audit_log, is the best.
Great topic! Thanks for making this video. I was telling my 6 year old daughter, who loves TH-cam Kids videos, that a TH-camr made a video for me to help me with work. She was jealous! Thanks again for all of the help.
No problem Gerald. Lots of love to your 6 year old daughter
Hi , I have 70 lakes records in 3 tables each . I need to archive values from those tables into ArchivalTable based on archival date and then delete from the main table . How to do this in SSIS ? Pls help
This won't be done in ssis instead you need to write sql queries to select data from one table and insert it into another table (of course you can use data flow task) for this. And then for deleting data you would need to write delete queries.
I am not getting what issue you are facing here. Just use data flow task to copy data from one table to another table and once data is copied, then you can delete data from source table using delete query.
@@learnssis okay does data flow task allows to copy basesd on condition like copy only rows from a table with archival date < Getdate() ?
@@MythicFusions Yes you can do that. In the OLE DB source, you can write any select query.
This video is awesome, but I was wondering if it will work for joint tables that only have 2 foreignkeys of 2 other tables. The issue is that the table doesn't have a one-column-primary-key to ntile by. Do you know what would be the workaround to that?
Even if you don't have a one-column-primary-key, still you can create the ntile in your table and implement this method.
Hello , Can you tell how to purge data from a table later than 10 days using SSIS approach , also I should be able to change the days later based on the configuration done in another table so that there would be no need to change code later
In SSIS there is no task to purge the data. You would need to write sql queries those will delete the data and you can maybe create a stored procedure for this and use that inside an SSIS package. The input parameter to the stored procedure can be passed inside ssis package. So if required you can change the store procedure and there won't be any requirement of changing the ssis package.
Can you explain how to move data from one table to another table within the same database ?
you can write the sql query like below
insert into destinationtable
select column1, column2, column3 from sourcetable
I would also recommend anyone with this use case to look into using OFFSET and FETCH with an order by. This will ensure you get a deterministic result. Also adding an order by to a Top I believe is required for deterministic results. Also Ntile is not deterministic and neither is row number. I don't believe any window functions are deterministic. Just a few things to keep in mind if you require that level of precision.
Yes order by is a good option to pull the records in a sequence so that you know you data is being merged in an order. In one of SSIS package, I added a column as Status which was initially NULL and then I started pulling for example 10 k records from this table where Status is NULL order by Id and merge this data to another table. Once merged we can update status to 1 for top 10 k in source table order by Id, this way first we are merging the data to dest using an order by and then updating the status for same records in source table so that those records won't be merged again.
Thanks for making this great video.....but i just want to know ...how we can schedule the batch interval like after every one hour batch should run.
We are using the audit_log table here to know how many parts data have been moved so far. Thus you can use this table to get the last part merged and accordingly you can merge the next part.
Hi sir, your videos is so helpful, can you please tell me or make a video on,
1- how to load password protected Excel files
2- load many excel files in one table and every excel contains 3 sheets, we need to load all the the excels and load all the sheet in one table
Hi Suraj Yadav,
this is the code in C# console app to import data from multiple excel files from multiple excel sheets and import the data into sql server table. I have made a video on "how to import multiple excel sheets to sql server table", below is the link
th-cam.com/video/CMor3vY7Y-E/w-d-xo.html
.So additionally we just need to add a foreach loop to loop through all files and pass the excel file forward.
Below is the complete code and I will get back to you on loading data from password protected excel files.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ConsoleApp1
{
internal static class Program
{
static void Main(string[] args)
{
string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string LogFolder = @"D:\Files\Logs";
string sqlConnectionString = @"Data Source=DESKTOP-EKJ1P64\SQL2019;Initial Catalog=Test;Integrated Security=True";
try
{
string folderPath = @"D:\Files\";
string[] excelFiles = Directory.GetFiles(folderPath, "*.xls*");
foreach (string filePath in excelFiles)
{
// Open the Excel file and retrieve the data
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(filePath);
// Loop through all the sheets in the workbook
for (int i = 1; i
@@learnssis Thanks Ahmad sir, it's really helpful 😊
Hi brother,
How to restart sql server as well as job agent.
Because everytime sql consume memory and it does not release it.
If you right click on SQL server instance then you will find an option to restart it. Just click on restart and it will restart the SQL server instance as well as the SQL job.
@@learnssis I want to daily schedule restart activity
@@TKJ using the command prompt you can stop the execution of sql server
NET STOP MSSQL$SQL2019
you can create a batch file and execute it using windows tasks scheduler then you can start it using below command
NET START MSSQL$SQL2019
In above line after the $ it is the instance name, you can get more information on this here
www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/
@@learnssis after using batch file.
SQL job agent will also restart? Or only SQL server?
@@TKJ both will restart. SQL agent restart type should be automatic not manual then after restart of SQL server , the SQL server agent should also get started.
Uso del NTILE, FOR, RecordInserted, For Loop Container, el (DT_WSTR,12) SQL command from variable con variable user, other transforms vs Row Count vs variable, execute SQL Task with SQLStatement Source and audit_log, is the best.
Good Sir 👍😀
Thank you Naveen Raja.