March 1, 2008

SSIS and Dynamic Excel Destinations Follow up

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:


50 comments:

  1. Hi Rafael,
    Indeed very useful post, could you please specify where to place the expression, "Header-"+(DT_WSTR,12)@[User::HeaderID]

    ReplyDelete
  2. Zulfi,

    You can set an expression to a SSIS variable by changing the variable EvaluateAsExpression property to true; then providing the expression in the expression property.

    ReplyDelete
  3. Your blogs are great and very useful. I have 2 newbie's questions.
    1. 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

    ReplyDelete
  4. coriolan,
    1. 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

    ReplyDelete
  5. 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.

    ReplyDelete
  6. EmpyreanZero, 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.

    ReplyDelete
  7. Hi Rafael,

    Extremely 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

    ReplyDelete
  8. Jay,

    1. 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.

    ReplyDelete
  9. hi Rafael,
    i 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

    ReplyDelete
  10. Hi Rafael,

    I 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

    ReplyDelete
  11. Hi Rafael,

    This 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 ?

    ReplyDelete
  12. 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?

    ReplyDelete
  13. Rafael,

    I 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.

    ReplyDelete
  14. 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

    ReplyDelete
  15. Flore,

    Excel 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.

    ReplyDelete
  16. Hey Rafael,

    Great 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?

    ReplyDelete
  17. Saeed,

    I 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.

    ReplyDelete
  18. 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?

    ReplyDelete
  19. Hi Rafael ,Thanks for the time you spent writing this, it is really helpfull.
    i 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.

    ReplyDelete
  20. Hi Rafael,

    I 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.

    ReplyDelete
    Replies
    1. 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

      Delete
  21. Hey Rafael,

    I'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?

    ReplyDelete
  22. בעז
    I 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.

    ReplyDelete
  23. Hi Rafael,
    I have my SSIS working but for some reason it is adding an apostrophe before string columns. How can i remove it?

    ReplyDelete
  24. 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?

    ReplyDelete
  25. Hi!

    I 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&copy 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.

    ReplyDelete
  26. At work with excel files I recommend try good reliable tool to my mind-ms Excel sheet recovery tools,why it?,because program helped me in very difficult situation and has free status as far as I can see,utility can repair corrupted Microsoft Excel worksheets,restore data after problems with corrupted worksheet problem as a result of virus attack, disk and network failures or any other problem,an scan a damaged Microsoft Excel worksheet and repair it,work with *.xls, *.xlam, *.xltx, *.xlt and *.xlsm formats,recover modified Excel worksheets and shows recovered data in the same way, like it will be displayed in Microsoft Excel worksheet.

    ReplyDelete
  27. 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

    ReplyDelete
  28. At work with Excel files I often use next software-recover modified Excel worksheets,it to myself advised my brother,he said that tool is free and is quite reliable,moreover it can repair corrupted Microsoft Excel worksheets,restore data after problems with corrupted worksheet problem as a result of virus attack, disk and network failures or any other problem,an scan a damaged Microsoft Excel worksheet and repair it,work with *.xls, *.xlam, *.xltx, *.xlt and *.xlsm formats,recover modified Excel worksheets and shows recovered data in the same way, like it will be displayed in Microsoft Excel worksheet.

    ReplyDelete
  29. I just put it to good use, thanks!

    ReplyDelete
  30. To 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.

    ReplyDelete
  31. When 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.

    ReplyDelete
  32. HI,

    I'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..

    ReplyDelete
  33. wonderful! thank you so much for this beautiful work!

    ReplyDelete
  34. Hi Rafael -
    i'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.

    ReplyDelete
  35. 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.

    ReplyDelete
  36. Nice one Rafael... Thanks for the detailed explanation

    ReplyDelete
  37. Rafael.....

    I'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

    ReplyDelete
  38. Ram Pratap17/9/10 6:16 AM

    Hi!
    Good 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

    ReplyDelete
  39. Hello Rafael, pretty new to SSIS. But can do basic stuff. like getting EXCEL imported to db, data conversion and things like that..

    have 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

    ReplyDelete
  40. Hi,
    I 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?

    ReplyDelete
  41. 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.

    ReplyDelete
  42. Hello Rafael,
    Great 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

    ReplyDelete
  43. I encountered a problem similar to that Kamlesh and some others experienced.

    Upon 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.

    ReplyDelete
  44. 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).
    heres my code :
    CREATE TABLE `" + @[User::SheetName] +"` (
    `Lvl` NVARCHAR(50),
    `Code` NVARCHAR(50),
    `Desc` NVARCHAR(50),
    `Date` NVARCHAR(50),
    `Status` NVARCHAR(50),
    `Post` NVARCHAR(50)
    )

    ReplyDelete
  45. Hi I have requirement where we need to put data on seprate excel sheet each day based on date

    ReplyDelete
  46. Hi Rafael,

    I 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.

    ReplyDelete
  47. 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

I will love to hear your feedback and comments. Thanks.