This post is a follow up to some of the questions I have received in a couple of post I made a while back, that showed how to create an X number of Excel file destinations.
Let’s say that you found this post that shows how to create a new Excel file on every iteration of a ForEach container; but that what you really want is to create different sheets on a single file on each iteration instead.
That is certainly easy to do by making few changes to the package in the original post.
1. Create a new variable to hold the Sheet name (@[User::SheetName]). The valuae in this variable will be changed on each iteration using an expression:
"Header-"+(DT_WSTR,12)@[User::HeaderID]
The naming convention for the sheet name would be Header-X where X represents the HeaderID from the Header table
2. Create a new variable to hold the Create table statement (@[User::CreateSheetStatement)that will create each sheet. Notice that this statement is the responsible for the creation of each sheet in the Excel workbook.
"CREATE TABLE `"+ @[User::SheetName] + "`(`HeaderID` INTEGER, `HeaderName` NVARCHAR(50), `LineID` INTEGER, `LineName` NVARCHAR(50), `LineDetails` NVARCHAR(50))"
3. Set the SQLSourceType Property of the Execute SQL task inside of the For Each container to Variable and choose the Variable you created in step 2
4. Remove the expression from (@[User::ExcelFileName); this is set the EvaluateAsExpression property to False. This is required as you don’t want to create a new excel file on each iteration, hence the excel file name in the connection manager will not change during the execution of the package.
5. In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose (@[User::SheetName) from the variable dropdown list.
It is important to remember that SSIS validates connections, and in this case, it checks the excel file name and sheet name provided at design time; so while you are editing the package in BIDS, make sure that Excel file is available. Also, remember to set the DelayValidation property of the Excel connection manager to True, to avoid validation errors when the package runs.
You may want to put some logic in the package to move the Excel file to a new location or to change the file name on the subsequent executions to avoid errors like:
Table ' already exists. (Microsoft JET Database Engine)
Here is a copy of the new package:
Thanks a ton Rafael!!
ReplyDeleteHi Rafael,
ReplyDeleteIndeed very useful post, could you please specify where to place the expression, "Header-"+(DT_WSTR,12)@[User::HeaderID]
Zulfi,
ReplyDeleteYou can set an expression to a SSIS variable by changing the variable EvaluateAsExpression property to true; then providing the expression in the expression property.
Your blogs are great and very useful. I have 2 newbie's questions.
ReplyDelete1. When I have a data flow task to just export a result set to excel, do I need to have data conversion?
The reason I ask is because I am using the sql server management studio export wizard to export to Excel and save it as a package and use it as model.
Whenever I remove the data conversion, the package errors.
Can I do without the data conversion when exporting to Excel? If yes, how?
2. I really like the createSheetStatement in a variable.
My result set has 166 columns, so my variable string is LONG. What is the way to get a full editor to edit the variable string?
Thanks
coriolan,
ReplyDelete1. SSIS as other data integration tools in the market will not do any implicit data conversion for you; this means that is the data types in the source are different that the ones in the target you would need perform a data conversion.
2. No window editor here. But I think that is a great suggestion. know that the SQL Server team is always looking forward to receiving feedback, you can log your suggestions and bugs to the SQL Server connect site http://connect.microsoft.com/SQLServer
I have been following your excel destination articles and it has helped me successfully create a working dtsx that does what I want it to do. One thing I've been wondering how to do though is get SSIS to export the data to a specific row,coulmn number... because of the formatting I want to use, the column names are not on the first row, they're on the 7th row. But I cant find anything that would allow me to specify that.
ReplyDeleteEmpyreanZero, I am not sure if there is an easy way to do that. One thing you could try is to load the data to a different sheet that has no format and the column name is in the 1st row; then within Excel have the formated sheet to reference the sheet where the data is loaded.
ReplyDeleteHi Rafael,
ReplyDeleteExtremely helpful. Have two questions, one related to the article, the other is not.
1. How do I test if an worksheet already exists in an Excel file? If it exists, I 'd like to remove it first or at least delete the data that are on the sheet. I could not find a solution.
2. I have a flat file data source and I'm only interested in records whose first character is "3". The records are ordered by the first character. I use a Conditional Split to filter out records with "3" as its first character. It works fine. To reduce execution time, I would like the package to stop reading records as soon as the first records with "4" as its first character is read. How do I do that?
Thanks
Jay
Jay,
ReplyDelete1. you can use an script task or For each loop container to check if an specific sheet(table) exists. BOL has some samples http://technet.microsoft.com/en-us/library/ms345182.aspx
Now removing data from a sheet is close to impossible and I have never tied to remove a sheet. I would look into creating a new file each time.
2. I don't see an easy way of halting the execution of a dataflow that way, and to be honest reading from a Flat file is so fast, that I don't think the time savings justify the extra effort.
Rafael,
ReplyDeleteThanks
Jay
hi Rafael,
ReplyDeletei have a question here... i want to put the file path in each iteration and then send this variable to a script task. please can you throw some light on how to do this.
thank u very much,
sandy
Hi Rafael,
ReplyDeleteI am getting rowset error when i set variable sheetname in excel destination
Error at Data Flow Task [Excel Destination [423]]: An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [Excel Destination [423]]: Opening a rowset for "Sheet-0" failed. Check that the object exists in the database.
Please Help
Hi Rafael,
ReplyDeleteThis is an awesome piece of code, it is really very helpful, but I closely followed your post, and i am getting an error "opening rowset for "sheetX" failed" , I have set Delayvalidation property of excel manager to true . I even dowloaded your package , modified connections for my database, created same tables, but still i am getting this error, can you please help? am I misssing something ?
Hi Rafael, Is there a way to delete the old data in an excel file and get the update data from the database each time the package runs?
ReplyDeleteRafael,
ReplyDeleteI am trying to use you "Create Table" example in your article and I am getting the following error:
[Execute SQL Task] Error: Executing the query "CREATE TABLE `EK`(`Ticker` NVARCHAR(50), `Closing` Double, `M_D_Y` DateTime)" failed with the following error: "Line 1: Incorrect syntax near '`'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I can not figure out where the error is this is the expression I am using in my variable:
"CREATE TABLE `"+ @[User::SheetName] + "`(`Ticker` NVARCHAR(50), `Closing` Double, `M_D_Y` DateTime)"
The sheetname variable is getting data ("EK") in this case. I copied and pasted your example and just changed the field names.
kamlesh, Make sure you are setting the DelayValidation property to true in the excel connection manager. It seems that the error is produced when the package is validated`. Another thing to check is that the create sheet step is actually creating a sheet that matches the name in SheetName variable
ReplyDeleteFlore,
ReplyDeleteExcel is not a DB engine, you just get some DB alike operations thanks to JET provider. Unfortunately, deleting data on the way you want is not supported by the provider. A workaround is to create a new file each time.
Bob,
It looks like the string you get when the expression in CreateSheetStatement variable is evaluated is not a valid create table statement. Perhaps the table name (sheet name) is not a valid name for execel.
Hey Rafael,
ReplyDeleteGreat Piece...
So, my question is what if I want each of my excel's tab structures (Create Table statement..) be created based on the query that we have defined for the OLE DB source inside the data flow? I mean, now, we have to enter same information at two places (Create table, and Select statment) can we summarize that into one?
Saeed,
ReplyDeleteI don't see how you could do that. You are basically asking for combining the create table and the Insert logic in one step. While this is something you could do in T-SQL (select..into...from), I doubt that can work with Excel files.
Awesome example! I followed your example of grouping content of flat file into several files and it works great, except for one thing. I'm using a date for the grouping and if i use == because i want the file name to match the date in the rows, it reverses them. if i use != it orders them correctly, but the conditional expression is really incorrect. any idea why?
ReplyDeleteHi Rafael ,Thanks for the time you spent writing this, it is really helpfull.
ReplyDeletei have a question now - further down the pipeline, just before i send the excel file i created , i want to password protect it.. is it possible within SSIS??
thanks.
Hi Rafael,
ReplyDeleteI have a package that exports data from a SQL Server to a xcel File.
vry time the package is run, a new excel fil has to be created and data must be xported to that new xcel file.I read your post about how to create a dyanamic excel file in MSDN forums using an expression in Excel Connection manager proprties. However when i run my package, only one file which is already used in the connection manager is gettig a table creatd in it. When i run m package again the data is exported into th same file.
My package is not able to use the variable user filepath and ithe exprssion for that. What changes do you suggst. How should i make the package to throw data into the
nw file vrytim and not use the dfault filename.I used the info in th below link to generate a dyanamic file name.
Can you pls lt m know what could be the isse, thnx so much.
Did you ever resolve this, NIKI? I have the same problem. It's set up the way Rafael described it, but it is not using the Excel file path variable to change the output .xls to another file. It is just making separate tabs/sheets within the same .xls
DeleteHey Rafael,
ReplyDeleteI'm using a foreach loop to load data from an excel file with multiple sheets. In the dataflow i have an excel source that selects a certain range from sheet1$.
how do i change this so that at each iteration the sheetname is fed into the select statement?
בעז
ReplyDeleteI have not idea how you could password protect the excel file within SSIS.
Duli,
You can have control over the select statement and change it at run time/each iteration by placing the Select statement in a variable, then use an expression to change the content of the variable value, then set the excel source to get the sql statement from the variable.
Hi Rafael,
ReplyDeleteI have my SSIS working but for some reason it is adding an apostrophe before string columns. How can i remove it?
While i m trying to export excel file using excel destination then it's leave one blank row after header row. i don't require this empty row. how can i avoid this?
ReplyDeleteHi!
ReplyDeleteI do have a problem: importing data from an Excel file works fine within Business Intelligence Studio. When run as SQL Server job, it sometimes fails with "OLEDBERROR... Opening a rowset for 'tablename' failed...". If I just cut© this excel table/sheet to another Excel file, then job runs fine. So, what can be wrong with an Excel file so it cannot be imported ? In my case, there is no any protection on it.
You are creating a Excel Table before exporting. What if I have to export to multiple excel sheets from different SQL Tables with different column names. For example customers table should be exported to Sheet1 and columns ate customer_id,customer_name. Product table should be exported to Sheet2 with columns product_id, product_no, product_name ? How can I pass source table names dynamically. And also create excel sheets dynamically during run time
ReplyDeleteI just put it to good use, thanks!
ReplyDeleteTo my mind MS Excel is a not good tool,because it can't recover data.But I know tool better-recover excel document.And it is free as is known.Program can recover data and can also open any file of xls format that is not accessible by Microsoft Excel.
ReplyDeleteWhen I was filling one table something happened...And all data in this file was deleted.I didn't know what to do and accidentally used google and found-.xlsx recovery.Tool solved these problems in 30 seconds and made it for free.Besides program demonstrated recover all these graphics, interviews, tables and reports in Microsoft Excel format.
ReplyDeleteHI,
ReplyDeleteI've a problem.. When i try to create multiple dynamic excels sheets inside a 'for loop' , i get the error "[Excel Destination [566]] Error: An OLE DB error has occurred. Error code: 0x80040E37. [Excel Destination [566]] Error: Opening a rowset for "Excel Destination" failed. Check that the object exists in the database. [DTS.Pipeline] Error: component "Excel Destination" (566) failed the pre-execute phase and returned error code 0xC02020E8. " randomly.
That is, first time i got this error at 5th iteration. I stopped and re-run the package, then i got the same error at 3rd iteration. next time 6th iteration. Next 2nd iteration and so on. Dont know whats the problem.. I could not rectify this . Please help.. Thanks in advance..
wonderful! thank you so much for this beautiful work!
ReplyDeleteHi Rafael -
ReplyDeletei'm trying to create a simiar ssis package and have some questions. I have an existing package that produce csv file, about 100,000 row. I need to change the destination file to excel and Due to row limited on each excel tab I need to put the data into different tabs. I'd like to do it dynamically by created new tab when certain number of row is reached. I'm just not sure how to set up the variables to count for the number of row...Can you advice how should I put it together?
Thank you.
Some days ago I was in intricate condition and I didn't know what to do next. Because all my xls files were lost. But fortunately my friend advised me - Excel file recovery 2003. It solved my problems for seconds and for free as far as I kept in mind. I hazard the remark that the tool is easy to use.
ReplyDeleteNice one Rafael... Thanks for the detailed explanation
ReplyDeleteRafael.....
ReplyDeleteI'm getting the below error.... Even I've set the property Delay validation = True
Error at Data Flow Task [Excel Destination [179]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [Excel Destination [179]]: Opening a rowset for "Header_" failed. Check that the object exists in the database.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination" (179)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Please help me on this
Hi!
ReplyDeleteGood Article!! Thanks
I need to do similar task, but i have to use some color formatting and grouping in the excel files (common to all files generated).
So is there anyway we can use a template excel file? Please provide steps to implement it.
Thanks
Hello Rafael, pretty new to SSIS. But can do basic stuff. like getting EXCEL imported to db, data conversion and things like that..
ReplyDeletehave a specific requirement. User is giving us a file with a filename_mmddyyyy.xls Need to read the month and year from the filename and insert that into a db. There might be more number of files in the folder say 1 for each month in a year.
After importing we need to get files moved into a different folder. can you help me with that
Hi,
ReplyDeleteI have a requirement wherein I need to read the data only from the first workbook sheet name. For eg, if my source excel-sheet has four workbook sheets viz., Sheet1, Sheet2, Sheet3 and Sheet4, I need to read only the data appearing in Sheet1. Is it possible to achieve using SSIS?
Secondly, that workbook may have a range defined in it as well. Is it possible to delete the range as well using SSIS?
Hi Rafael! This is very useful post! Here file is static and the sheet is dynamic but my requirement is little bit different i.e. i need to create the file and sheet dynamically. please help me.
ReplyDeleteHello Rafael,
ReplyDeleteGreat post, tried your method I seem to be doing something wrong. I am unable to set the property to table name from variable in excel destination keeps giving error does not exist. set delay validation to true and also set to open rowset from variable in properties but still cannot make it work. created a spread sheet and then tried still same problem can u help
I encountered a problem similar to that Kamlesh and some others experienced.
ReplyDeleteUpon making the changes instructed in step 5, and trying to view the Mappings in the custom property editor of the Excel destination component I received the error:
Error at Data Flow Task [Excel Destination [99]]: Opening a rowset for "Header-0" failed. Check that the object exists in the database.
I changed the expression for the SheetName variable to this: "'Header-"+(DT_WSTR,12)@[User::HeaderID]+"$'"
which adds a $ and single quotes enclosing the entire resulting expression. I also made sure that each of the target sheets ( Header-1, Header-2 and Header-3 ) pre-existed in the output file, with the column names in place.
The package worked like a charm. This info may be 2 years too late for Kamlesh, but may help others in the future.
Thanks Rafael for this jumpstart on a useful task!
Dan H.
I get a error in the last step when trying to create a new sheet name in the excel file. says cannot create table "Syntax error in Create table statement.(Microsoft JET Database Engine).
ReplyDeleteheres my code :
CREATE TABLE `" + @[User::SheetName] +"` (
`Lvl` NVARCHAR(50),
`Code` NVARCHAR(50),
`Desc` NVARCHAR(50),
`Date` NVARCHAR(50),
`Status` NVARCHAR(50),
`Post` NVARCHAR(50)
)
Hi I have requirement where we need to put data on seprate excel sheet each day based on date
ReplyDeleteHi Rafael,
ReplyDeleteI have a requirement here.I need to export data from an OLE DB Source to excel template.AS SSIS cant do any implicit conversion so I prepared the excel template by using the following way.
OLE DB Source----->Data Conversion------->Excel Destination
In Excel Destination I create an Excel Connection Manager.....select Data access mode to "Table or View" and to prepare the excel sheet I click the "New" button in "Name of the Excel Sheet" option.
it create the table like this
CREATE TABLE `Excel Destination` (
`City` LongText,
`StateProvinceID` Long
).
After that I run the package.In my database table there are 200 rows.I saved the excel sheet in a different location and delete the 200 rows.
Then when I rerun the package using this excel template then the data is entering into the excel sheet but not from the first row of excel sheet.I checked the preview in excel destination, it shows that first 200 rows have NULL values. Can you please suggest me that how will I remove this NULL values.
Nice article. I do have a question or your idea of using template when we generate .csv files to destination folder. Is is ever possible is SSIS? IF yes what should I do to use a template for generating formatted .csv files? Thanks in advance
ReplyDelete