ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
111 How to export data to new excel file every time in ssis
ฝัง
- เผยแพร่เมื่อ 12 ส.ค. 2024
- How to export data to new excel file every time in ssis
Download the file\script used in the Video from below link
drive.google.com/drive/folder...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
how to export data to new excel file every time in ssis
How to dynamically create Excel file in SSIS?
How do I export data from SSIS package to Excel?
How do I overwrite data in Excel destination in SSIS?
How do I create an Excel file with date time on each package execution in SSIS package?
Happy Learning.
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”
This is an excellent video. Be sure to watch the last 3.5 minutes!!! It solved my problem by creating an Execute SQL Task to the Excel Connection Manager (not the db) that creates the Sheet needed to dynamically map the columns to the dynamic Excel file.
Thank you Maria for sharing your experience. God bless you.
@@learnssis I ran into one problem that I think is worth mentioning. The Excel Connection Manager Property, RetainSameConnection, should be set to True to avoid getting error 0x80004005 Description: "External table is not in the expected format.".
@@mariahalt4603 Oh thats cool. thanks for sharing.
Amazingly explained. Simple and concise. Well done. 👍
Thank you Uzan for your comment.
way too fast though. especially if the user is not well versed and is still learning SSIS
You are the Man! Subscribed.
You are most welcome.
this is a great help! Thank you.
You are most welcome Jenn.
Amazing video and helped a lot . Great Job😇
Thank you.
First of all Thanks. I want to propose an alternative solution which is easier for me. Use file system task > Configure source and destination variable(create source file path variable and destination file path variable). > Connect to data flow> change Excel file connection from expression
Yeah that sounds good as well.
This task will help us more thanks a lot
Thank you Kallanai for your support.
You are amazingly knowledgeable. I'm learning so much from you. I tried to find an explanation of why there is sometimes a $ after the worksheet name after you specifically said to choose the worksheet name without the $ in this video. Can you explain what the $ indicates on the end of a worksheet name and why you chose the worksheet name without the $ in this video?
Thanks for your comment.
In Microsoft Excel, a dollar sign ($) at the end of a worksheet name indicates that the sheet is a "frozen" or "static" sheet. This means that any references to cells on that sheet will always refer to the same cells, even if new rows or columns are inserted or deleted in the spreadsheet.
The dollar sign is used to "anchor" the reference to a specific cell or range of cells. For example, if you have a formula that refers to a cell on a static sheet like "=SUM(Sheet1$A$1:Sheet1$B$10)", the reference to "Sheet1$A$1" and "Sheet1$B$10" will always refer to cell A1 and B10 on Sheet1, even if you insert or delete rows or columns.
In contrast, if you remove the dollar signs from the reference, like "=SUM(Sheet1A1:Sheet1B10)", the reference is "relative" to the current cell. so when you copy the formula, it will adjust its references to the sheet, ex. if you copied the formula to cell C3, it would become "=SUM(Sheet1A1:Sheet1B10)"
In short, the dollar sign makes the sheet reference absolute, it locks the sheet name, the row number and the column letter, so it will not change even if you copy the formula to another cell or sheet.
@@learnssis Thank you. Let me be more specific because the answer you provided doesn't seem to apply to the package you created in this video. At 5:37 into your video, you can see what I'm talking about. You specifically said to choose the worksheet name with the $. I created my own package and tried selecting the worksheet with the $ and it seemed to work correctly so maybe it doesn't matter.
@@dianeschuster Yes you are correct, it won't matter in this case whether you select the sheet with $ or without $.
Awesome Agil 👍
Thank you Booleys.
Awesome! thanks a lot
You are most welcome Raghu Ram.
Thanks for this. But is it possible to output the excel in numeric data type instead of text for some of the field? It will be easier for user to do analysis in excel. I couldn't figure out especially for dynamic excel filename.
This is how data is exported in excel using ssis. Maybe you can create a template file and for every export copy the template file to your destination location and export the data there.
th-cam.com/video/y5kvWjVNVfc/w-d-xo.html
however as far as I am aware there is no option in ssis to give some columns as numeric and others as text. It will always export data as text.
Thanks Aqil @@learnssis . My script task to format the excel file is not working in SSIS due to the compilations error. but I got the answer from other video of yours th-cam.com/video/XEnMgLkcbnQ/w-d-xo.html. Where I need to properly add the necessary references to make it working.
Hi,
I have used the same process for CSV file export. The question I have is, do we need to use execute SQL task to create CSV file? You have used this to create an Excel file.
Thanks in advance.
No, for CSV file we don't need to use the Execute SQL Task. The csv file will be created automatically using Flat file destination.
I am getting ole db error so make the delay validation as true but still getting excel connection manager error
Take a look at this video and try to use the template file.
th-cam.com/video/rW_GieEjIS0/w-d-xo.html
How to replace or overwrite same file incase we have to run job multiple time in same day ?
If you see the video carefully, we are appending the second as well in the time, and none of the package can be executed multiple times in a single second.
Thanks for this video bro, but do you know how to not include header row when generating the excel file?
Sorry I don't know.
I have the same question. Were you able to find out how?
Thanks for sharing
You are most welcome Thales.
Does anyone know how to not include the header row when generating excel files?
Excel file can not be generated without header row in SSIS.
This is helpful, also could you please show CRUD operation in SSIS package. This will be very helpful.
If you want to create
ead\update\delete data from a sql server table then you can execute any type of query inside execute sql task in SSIS. I have created few videos where you can see how to use the execute sql task to run any type of sql query.
th-cam.com/video/_8nbAMHXGIQ/w-d-xo.html
th-cam.com/video/oIQlPAnH4xU/w-d-xo.html
th-cam.com/video/6AIM3xkO0H8/w-d-xo.html
Hi Aqil,
Thanks for your amazing video which help us lot.
I have one question can you please resolve if possible.
Can we open,save and close the excel file using SSIS @scheduled time. Like every day 3 PM that package execute and open the excel file which is in particular folder and save and close the file.
Awaiting for your response
Thanks in advance
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
This is a very helpful video. Thak you. but as i undrestand, this excel file is used as a "template". what if we have to move it each time to a different folder?
You can move the exported excel file to an archive folder using file system task and this package will create a new excel file without any issue if you will rerun it. It is not dependent on old file, it is anyhow creating a new file every time you run the ssis package.
Great video
Thank you so much.
Can we achieve same thing by using script task, please explain
yeah we can do same thing using script task. Check this video.
th-cam.com/video/ZE7jlWuqsvU/w-d-xo.html
This is great!!! Thank you so much. I do have a few questions- i will email you !😊
You are most welcome Anna. Sure you can.
Thanks for your question Anna, so we got one more new learning opportunity from Aqil. Thanks lot Aqil 😊
@@kssabin786 ha ha. I got a new topic to make video on 😃
Hi Aqil
Thanks a lot very clear explanation .
Can u make a package it should pickup excel file automatically and load into sql as well as in excel destination . once loaded source file should be moved to archive folder. And this should be automatic.
Thanks in advance.
I have already created a video on almost same topic, you can check it below
th-cam.com/video/TH9ptAkCHNA/w-d-xo.html
Thanks a looooottttt Aqil
Hi ,
My create table statement is not generating in excel destinations. Can you please help me .
Can you tell more what is happening ? What error are you getting ?
When i am trying to run the package from the SSMS job, it is throwing error as opening a rowset for sheet failed check the object exists in the database
Please help me in this error
Did you ran the package from visual studio from same server or you are trying to run it from different server ?
From the same server only, from visual studio the package is running successfully and creating the excel file, but from the job it is throwing the error
@@ChandraSekhar-qy9px Check the owner of the sql services, make sure to use a user who has full access on the machine.
th-cam.com/video/fIhkb3P3Jss/w-d-xo.html
thanks
You are most welcome Prashant.
@@learnssis I have issues with my SP while executing in SSIS. so can I ask for this if you have no problem .
@@prashantsuthar7 Sure go ahead.
@@learnssis Thanks, here the SP EXEC MISDB.dbo.RR_PNP_Extract @projectid = '29590,29813,34312,2565',@isenabled = 1
want to execute in SSIS with multiple dynamic parameters(coming from SQL query Ex: "select projectid from projectmaster" consider as first parameter) after export results as Excel daily with datetime as file name schedule in SQL server agent