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.
@@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.".
10:41 User::Filename is updated automatically without having to execute the task(?) In my project, it never updates - something wrong or not understanding how/when variables update.
You can actually use a foreach loop container to loop through the files from a folder and in the foreach loop container in the variable mapping you can select FileName variable, but make sure you select the "Name and Extension" option in the Foreach loop container at Collection Tab.
Thank you for your videos. There is a way to create a file for each of my clients saved in a table in sql. For example I have 10 clients and they have differents type of data, I was to use my clients table as parameter to create is own file
You can store the clients name in a sql table and maybe have a flag active in the table. So if you want to export data for 10 clients then you can set the active flag to 1 for those 10 clients and leave others inactive. Now your sql query in the execute sql task will pull the name of clients or sql table name from the table where active=1 select tablename from config_Tbl where active=1 and store the result in an object variable using full result set. Now you can use a foreach loop container with ado enumerator and can use a table_name ssis variable of string type and can set it at index 0. and can use a data flow task. Inside data flow task you can use an OLE DB source, now you can get the table name from table name or view name variable and can get the data for the client and can use a flat file destination to export data to a csv file. You can set the connection string accordingly and can pass the table_name ssis variable to flat file connection manager. ruclips.net/video/GlznRaeQkx4/видео.html
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.
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. ruclips.net/video/y5kvWjVNVfc/видео.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 ruclips.net/video/XEnMgLkcbnQ/видео.html. Where I need to properly add the necessary references to make it working.
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. ruclips.net/video/_8nbAMHXGIQ/видео.html ruclips.net/video/oIQlPAnH4xU/видео.html ruclips.net/video/6AIM3xkO0H8/видео.html
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
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.
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.
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.
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
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. ruclips.net/video/fIhkb3P3Jss/видео.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"); }
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.
@@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
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.
It really help me thanks i like this channel ❤
You are most welcome.
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
Amazing video and helped a lot . Great Job😇
Thank you.
this is a great help! Thank you.
You are most welcome Jenn.
10:41 User::Filename is updated automatically without having to execute the task(?) In my project, it never updates - something wrong or not understanding how/when variables update.
You can actually use a foreach loop container to loop through the files from a folder and in the foreach loop container in the variable mapping you can select FileName variable, but make sure you select the "Name and Extension" option in the Foreach loop container at Collection Tab.
This task will help us more thanks a lot
Thank you Kallanai for your support.
Thank you for your videos. There is a way to create a file for each of my clients saved in a table in sql. For example I have 10 clients and they have differents type of data, I was to use my clients table as parameter to create is own file
You can store the clients name in a sql table and maybe have a flag active in the table. So if you want to export data for 10 clients then you can set the active flag to 1 for those 10 clients and leave others inactive.
Now your sql query in the execute sql task will pull the name of clients or sql table name from the table where active=1
select tablename from config_Tbl where active=1
and store the result in an object variable using full result set.
Now you can use a foreach loop container with ado enumerator and can use a table_name ssis variable of string type and can set it at index 0. and can use a data flow task.
Inside data flow task you can use an OLE DB source, now you can get the table name from table name or view name variable and can get the data for the client and can use a flat file destination to export data to a csv file.
You can set the connection string accordingly and can pass the table_name ssis variable to flat file connection manager.
ruclips.net/video/GlznRaeQkx4/видео.html
Awesome Agil 👍
Thank you Booleys.
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! 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.
ruclips.net/video/y5kvWjVNVfc/видео.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 ruclips.net/video/XEnMgLkcbnQ/видео.html. Where I need to properly add the necessary references to make it working.
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.
ruclips.net/video/_8nbAMHXGIQ/видео.html
ruclips.net/video/oIQlPAnH4xU/видео.html
ruclips.net/video/6AIM3xkO0H8/видео.html
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?
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.
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.
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 😃
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.
ruclips.net/video/rW_GieEjIS0/видео.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.
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.
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 ?
Can we achieve same thing by using script task, please explain
yeah we can do same thing using script task. Check this video.
ruclips.net/video/ZE7jlWuqsvU/видео.html
Great video
Thank you so much.
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.
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.
ruclips.net/video/fIhkb3P3Jss/видео.html
Thanks for sharing
You are most welcome Thales.
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
@@Amit-q8l 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
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
ruclips.net/video/TH9ptAkCHNA/видео.html
Thanks a looooottttt Aqil
Can we create the file dynamically without header
Excel file can not be created without header.
However you can create the excel file at run time
ruclips.net/video/NPYxOpS-kLg/видео.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