Відео

Thank you.

1. Sheet without $ (Normal name): This refers to a regular worksheet within the Excel workbook. For example, if you name your sheet "SalesData", it will appear in Excel without a dollar sign ($). 2. Sheet with $: This typically refers to a named range or a specific type of Excel object that can be accessed through a special syntax in Excel formulas or through other Excel features. For example, if you name your sheet "SalesData$" or "SalesData$A1 ", it could represent a named range (SalesData) or a structured reference within the workbook.

SSIS Tutorials: ua-cam.com/play/PL_YF--8vjjEVEXMf2hEFn0D5tEJV9kRqi.html SSIS real time scenarios examples: ua-cam.com/play/PL_YF--8vjjEU1Hdlqp2U_q0JNyFPYUIKY.html SSIS Interview questions and answers: ua-cam.com/play/PL_YF--8vjjEVYkoxioTCEKAkBxjLEUUB7.html

Winscp should be installed on the server on which you are deploying your ssis package. Winscp.exe file should present at the correct location on server whatever location is given in the Execute process task.

Winscp is installed on the server and also the file location is correct

Is the ssis package placed on the file system or you are referring the ssis package from ssis catalog in sql agent job ? Make sure to set the delay validation property of ssis package to true. Take a look at this video as well. ua-cam.com/video/fIhkb3P3Jss/v-deo.html

@@learnssis file system, let me test again because the database is busy and i'll try it again.

@@learnssis it show this error after an update i've made Message Executed as user: NT Service\SQLSERVERAGENT. NNECTIONMANAGER. The AcquireConnection method call to the connection manager "TDBP78WV.DWH_ALL_Billing.backup_user" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2024-06-28 11:44:41.86 Code: 0xC0047017 Source: Export data to csv SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2024-06-28 11:44:41.86 Code: 0xC004700C Source: Export data to csv SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2024-06-28 11:44:41.86 Code: 0xC0024107 Source: Export data to csv Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:44:36 AM Finished: 11:44:41 AM Elapsed: 5.328 seconds. The package execution failed. The step failed.

Glad it worked for you.

Do you have view in sql server ?

Sorry I don't remember about any such video.

Thank you.

Thank you Ahmed.

Ideally if a data flow task is successful then it will load all records from csv file to sql server table. However if you want to double check, then you can use C# to get the total number of records from the csv file and assign the value to an ssis variable. And then after loading the data to sql server table using data flow task, you can check the total number of records loaded today and assign the value to the ssis variable using execute sql task with single result set option. Later you can compare the values between both the ssis variables, if they will be same the all records loaded otherwise there is a difference between source file record count and records loaded today to the sql server table.

Thank you Jeff.

Thanks, Yes it will apply for sql server 2022 as well.

I have found out what was misleading and got mine fixed. If anyone interested then please let me know. in a nutshell, don't change the sheet name in the paramters, do it in the second DFT

also make sure of the DFT in the inner Loop has a delay validation set to true

This will be done using C#. Can you please ask chatgpt to write the code for this requirement. It can write the code for you. Copy the first row from the flat file and give to chatgpt and ask to write the program.

@@learnssis actually it’s a total row length that need to be validated

Can you try to get help from chatgpt ?

ha ha 😀. I was not sure if there is anything which is called as MIC

Can you check the Logs folder and see what error message you are getting in error_log files ?

The column can be added directly to Fact table using the sql query. But the SSIS package can't be changed automatically. it needs to be changed and redeployed.

@@learnssis Will there be null values for that column after the initial load or do we have to load the entire data adding that new column to the fact table. I highly appreciate your response.

@@sanjuladissanayake5295 If you already have data in the Fact table and you add a new column later to the Fact table then it will have NULL values for that column for all records. Now either you can fix the data manually to that column based on a join on source table, or you can map that column in the SSIS package and delete and reload the data to Fact table again.

Thank you so much for this❤️

I think I used only windows authentication method in this video. I did not used any password here.

If you do not want to rename a specific file, then before the File System Task, you can put a data flow task, and connect the data flow task with the File System Task. Now on the expressions between data flow task and File System Task, select Expressions, and write the expressions there Just drag and drop the FileName into the expression windows and write condition @Filename != "Product.csv" And then save the changes. I have written @FileName but here you would need to drag and drop the FileName ssis variable. Now what will happen the File System Task will only get executed if the file name is not Product.csv

did you create any video on this , Please share the link with me

@@Ankit_Gurjar_1997 If you look at this video, I have used the expressions here to decide whether to execute the next task or not. ua-cam.com/video/OklmSWn3qso/v-deo.html

If we won't insert the zero sk then we might miss some records to be inserted to fact table as there won't be any match for those records.

My answer is: For example, loading the fact table every 1 hour and dimensions every 1 week So we need to insert zero sk But in addition price changes and quantity changes we need to consider sk changes To update zero value sk I think the conditional split can have another condition to check for sk changes thanks

We are using a foreach loop container here, So the foreach loop container can loop through files in a folder and can assign the FilePath with the value of a File Path that is available in a folder.

it totally depends what is written in the SSIS package. Is it a simple load or incremental load. What is your source? Are you reading data from a single table or multiple table ? Is enough data available in source or not ?

@@learnssis it is a cte which has subqueries, joins . data is available in source and it is incremental load by using staging table

@@user-jh2yv2jz5s Try to run the cte queries on the source system whether it is fetching data or not. If it fetching data then that data might be available in destination table.

@@learnssis i have checked with cte the data is coming

@@user-jh2yv2jz5s Maybe that data already exists in the destination table. Can you insert that data to a staging table and see if that data is already in the destination table ?

No. If the files are at sharepoint then first you would need to download the files from sharepoint to a folder on the server and then you can use this method.

Sorry this is very limited error message. Can you go to sql job and if the ssis package is deployed to ssis catalog then please check the error message there. And in the sql agent job if you are using the ssis package from file system then please go to history of the job and try to get the complete error message from there.

Sorry I am not aware about any other method. There are very minor chances that it can be done by some other method.

I think I already replied to you.

You need to get the ssis packages from github to a folder on the machine and then you can deploy it. In this video you can learn how to get the code from github ua-cam.com/video/ZpBoUntYzoM/v-deo.html

Please take a look at this video. ua-cam.com/video/1GBrMBZSAoA/v-deo.html

You are most welcome.

Thank you Arwa Omer.

this is the code to zip a single file. string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string LogFolder = @"D:\Files\Logs"; // string sourceDirectory = @"D:\Files\Apple"; string zipFilePath = @"D:\Files\Data.zip"; // string zipFilePath_Folder = @"D:\Files\Apple.zip"; try { // ZipFile.CreateFromDirectory(sourceDirectory, zipFilePath_Folder); string formattedDate = DateTime.Now.ToString("yyyyMMddHHmmss"); string newZipFilePath = Path.Combine(Path.GetDirectoryName(zipFilePath), Path.GetFileNameWithoutExtension(zipFilePath) + "_" + formattedDate + Path.GetExtension(zipFilePath)); string sourceFilePath = @"D:\Files\Email.csv"; using (FileStream zipToCreate = new FileStream(newZipFilePath, FileMode.Create)) { using (ZipArchive archive = new ZipArchive(zipToCreate, ZipArchiveMode.Create)) { archive.CreateEntryFromFile(sourceFilePath, Path.GetFileName(sourceFilePath)); } } } catch (Exception exception) { using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + currentdatetime + ".log")) { sw.WriteLine(exception.ToString()); } }

Error: 0xC00291E2 at GET Max RunId, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

@@verdigin.kadar.alirsin1453 This means the sql query in the execute sql task is not returning the single row. Watch this video for more details ua-cam.com/video/_8nbAMHXGIQ/v-deo.html

ha ha, thank you.

You can do that using C#. Below is the C# code which can work here. You can put this code in Main method and provide the values to LogFolder path variable, FolderPath variable, and FilePath variables, rest of the code will remain same. string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string LogFolder = @"D:\Files\Logs"; string FolderPath = @"D:\Files"; string FilePath = @"D:\Files\sdf 3.xlsx"; string FileName = Path.GetFileNameWithoutExtension(FilePath); try { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase); Workbook book = app.Workbooks.Open(FilePath); Worksheet sheet = (Worksheet)book.Worksheets[1]; if (File.Exists(FolderPath + "\\" + FileName + "_New.xlsx")) { File.Delete(FolderPath + "\\" + FileName + "_New.xlsx"); } book.SaveAs(FolderPath + "\\" + FileName + "_New.xlsx"); if (File.Exists(FilePath)) { File.Delete(FilePath); } book.Close(); app.Quit(); if (File.Exists(FolderPath + "\\" + FileName + "_New.xlsx")) { File.Move(FolderPath + "\\" + FileName + "_New.xlsx", FilePath); } } catch (Exception ex) { using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + currentdatetime + ".log")) { sw.WriteLine(ex.ToString()); } }

@@learnssis can you please share an video with apply this code. Thanks in advance

@@user-qs1cc6fq9y I don't have a video at the moment, I will try to make a video this weekend and will share it on Monday.

@@learnssis in this code have to add excel app reference in namespace..like using excel.interop ..is it?

@@learnssis will wait for your video till the time I will try my end. Thanks 🙏 Your knowledge is really mind-blowing.thanks a lot

Can you try to run the package in 32-bit mode and see if it helps.

You are most welcome.