146 How to overwrite data in excel using ssis

[@freakzisback]

Thank you this was great to learn

[@learnssis]

Thank you Bryan.

[@mozjdmconnection]

awesome video, thank you

[@learnssis]

Thank you for appreciating.

[@Abbathsin]

it worked well just what I needed, the only problem is to append data in the excel doesn't increase the existing table.

[@learnssis]

Okay great. Yes that is true.

[@Abbathsin]

@@learnssis I have been using mostly ADF over the years but i came across ssis recently, lot to learn.

[@hariherdiawan1824]

hello Mr., I have a problem with code like this [Excel Destination [260]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot expand named range.". Please help me to fix it, thanks!

[@samuna8950]

Sir suppose i have 5 different tables which i want to union in ssis along with that there data should be updated or deleted and if there is new data that should be inserted. How to i handle these things

[@learnssis]

you would need to use the incremental load and there you can handle new\updated\deleted records kzbin.info/www/bejne/mIvEmq2Bq7d0btE

[@samuna8950]

@@learnssis sir it is a fact table created by using unions and the records might have same pks also

[@swapnilparikh8335]

can you help me here I have 5 Excel File and i want these data in 1 destination file- Sql server , but when i am running for each loop if data is overwrite it will overwrite all data what to in that case

[@learnssis]

If you have multiple files of same columns, then you can easily import them to sql server table using the foreach loop container. And no data will be overwritten kzbin.info/www/bejne/inmcoad3oKh7hKM

[@samderrty123]

mine does not start at row 2 when I run the package. Any thoughts? thank you

[@samderrty123]

I solved it. Thanks

[@learnssis]

@@samderrty123 Glad you were able to fix it.

[@romariovargas2586]

It is always great to learn something new with you, thank you very much for sharing your knowledge, but I need help, I want to connect to Google Drive (spreadsheet) to get my data, how would I do it? Thanks in advance

[@learnssis]

thanks, actually there is no inbuilt task in SSIS for doing this, most probably we would need to write some code in C#, I will check on this one and will let you know. Thanks.

[@ramakrishnanpalanisamy5058]

Hi Aqil, I recently mailed you for the doubt i had in query logic. Can you please help me

[@learnssis]

I have provided you the sql queries those I got from chatgpt, they seems to be working fine. You can take help from chatgpt about sql queries drop table InvoiceData -- Create the table CREATE TABLE InvoiceData ( ID INT PRIMARY KEY, Reference_no VARCHAR(50), File_Name VARCHAR(255), Sheet_Name VARCHAR(50), Date_xx DATE, Invoice_amt INT, Payor_name VARCHAR(255), Account VARCHAR(50), Transit_Mode VARCHAR(50), Mode_Of_Transport VARCHAR(50) ); -- Insert data into the table INSERT INTO InvoiceData (ID, Reference_no, File_Name, Sheet_Name, Date_xx, Invoice_amt, Payor_name, Account, Transit_Mode, Mode_Of_Transport) VALUES (6, 'AC9671307accessorialAug 31 2023 12:00PM', 'Recap by with invoice SEPT2023.xls', 'Sheet1$', '2023-10-02', 120, 'CUMMINS POWER GENERATION LTD (CES )', 'SA-00968363accessorial', '-15', 'accessorial'), (4, 'AC9671307accessorialAug 31 2023 12:00PM', 'Recap by with invoice SEPT2023.xls', 'Sheet1$', '2023-10-04', 120, 'CUMMINS POWER GENERATION LTD (CES )', 'SA-00968363accessorial', '-15', 'accessorial'), (5, 'AC9671307accessorialAug 31 2023 12:00PM', 'Recap by with invoice SEPT2023.xls', 'Sheet1$', '2023-10-05', 360, 'CUMMINS POWER GENERATION LTD (CES )', 'SA-00968363accessorial', '-15', 'accessorial'); go Now your select query SELECT t1.ID, t1.Reference_no, t1.File_Name, t1.Sheet_Name, t1.Date_xx, t2.Total_Invoice_amt, t1.Payor_name, t1.Account, t1.Transit_Mode, t1.Mode_Of_Transport FROM InvoiceData t1 JOIN ( SELECT Reference_no, MIN(Date_xx) AS Min_Date_xx, SUM(Invoice_amt) AS Total_Invoice_amt FROM InvoiceData GROUP BY Reference_no ) t2 ON t1.Reference_no = t2.Reference_no AND t1.Date_xx = t2.Min_Date_xx;