December 22, 2006

Import Header-Line tables into Dynamic Excel destinations II

After talking about how to use a ForEach Loop container to iterate through a query result set in the first part of this post; I am going to show how to create a distinct excel file on every iteration dynamically.

This functionality will be added over the package I built in the first part. I will basically use an Execute SQL Task with an Excel Connection manger to create a excel file with a different name on every iteration. The connection manger uses an expression in ExcelFilePath to make the file name distinct each time.

Ok, no more talking and let’s get to work!

Add an Execute SQL Task inside of the ForEach Loop Container and create a precedence constraint (yes, a green arrow!) from the Execute SQL Task to the Dataflow task.




Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager in Connection property and write a Create table statement in the SQLStatement as follows:

CREATE TABLE `Excel Destination` (
`HeaderID` INTEGER,
`HeaderName` NVARCHAR(50),
`LineID` INTEGER,
`LineName` NVARCHAR(50),
`LineDetails` NVARCHAR(50)
)


As you can see, the objective of this EST is to create the excel file to make it available to the data flow task on every iteration; and it is here where the power of the expression come handy.


Create a new variable, to hold the file name to be creates. I created a variable called 'ExcelFileName' of String Type (give an intial name in the variable window to avoid validation errors).

Now, use an expression to change the value of the variable at run time; to do so, go to the preperties windows of the Variable and change the porperty EvaluateAsExpression to true and click on the expression property to bring the expression editor. That expression will change the value of the variable as the package iterates through the recordset. I used an expression like:

"C:\\Temp\\Report-"+ (DT_STR, 25,1252)@[User::HeaderID] +".xls"


In the Excel file connection manager, let's create another expression that uses the 'ExcelFileName' variable to change the ExcelFilePath. To do so, click in the connection manager, go to the properties window and click in the expression property. Once the Property Expression Editor is available; choose ExcelFilePath and bring the expression builder. Write an expression like:

@[User::ExcelFileName]


Now, cross the fingers, run the package and hopefully you will get 3 excel files with following data (rows in each red box will go to a diffrent file)



UPDATE: I have posted a Follow-up with addressing few details and questions posted in the comments: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html



44 comments:

  1. This was a great article! I used this as a template to build a job that loops through all my servers and get information from the sys tables. Thanks for the blog man!

    sqlninja

    ReplyDelete
  2. Thanks for this article. I was able to apply this to a similar problem and it works beautifully.

    rj

    ReplyDelete
  3. You are my hero. This worked beautifully, esp. since I couldn't use the same method I used for dynamically name a flat file.

    ReplyDelete
  4. Thank you for taking the time to post this article. It is great that you didn't confuse the primary purpose with a bunch of confusing digressions that would have taken a week to work through.
    Thanks again for keeping it simple.

    ReplyDelete
  5. Rafael.. If i want to export to seperate excel sheet within an excel file in a foreach loop, how do I do it?

    ReplyDelete
  6. Murali,

    I have posted a new entry where, hopefully, I am addressing your question. See it here: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

    ReplyDelete
  7. I was hoping you could help. I've tried to follow your blog the best I could, but when I select the variable in the Excel Destination task, I get a "rowset" error. I've triple checked and the file and location exist. All I want to do, is ensure that the file will be created each time this package is run.

    ReplyDelete
  8. Thanks for your examples. I need to combine the 2 different examples. I need and outside loop that creates individual excel files and an inside loop that creates individual sheets within each file. So far, I've got it creating the individual files.

    Again, thanks and don't go too far, I might be back for more help.

    ReplyDelete
  9. I am getting the same RowSet error please help .... the file exists for sure

    ReplyDelete
  10. Good day,

    I would like to use a foreach loop to loop through a folder with several DBase 5.0 files and create a different table in SQL Server for each DBase file.

    Right now the loop is working but I cannot create the different tables in SQL Server.

    Help, please. Your Excel example couldn't help me out.

    Thank you.

    ReplyDelete
  11. Thanks. I was able to create the loop and multi-sheets.
    However, I also encounter some problem that are posted before.
    First, the excel file better to have one empty sheet. Then use the create sql step to create that first sheet for SSIS validation purpose.
    Second, almost all step need to flag for delay validation
    Third, my header table has Bigint, which SSIS didn't play nice and gives error I32, I64 doesn't matter. I have to cast to varchar type and have an extra variable then cast back before I combine to form the sheetname.

    ReplyDelete
  12. Hi Rafael, great blog! Many thanks for the patient details.

    In addition to dynamic sheets, I need to retain colored formatting for a static # of rows & columns. (Tried using a template but got hit by the appending issue). Could you please suggest a possible solution for this?

    ReplyDelete
  13. Hi,
    Many thanks for your article, it is absolutely accurate and efficient.
    Simply Great !

    ReplyDelete
  14. Moody1982: This is cool man...many thanks to you for posting this article...its a great learning for me...thanks again

    ReplyDelete
  15. Hi Rafael!

    Your posts on Header-Line exports to Excel are increadibly helpfull! I have never tried SSIS creation before and I am up and running in no time with input from your blog.

    I do have a few questions though, things that bug me a bit... maybe you have some clue about them.

    Firstly, is it possible to only have one template excel file? I can only get this running if I first create all the files I expect to generate.

    Secondly - how would I create new files instead of overwriting my templates?

    Thirdly - I had problems with auto formatting and found this post about how to handle it: http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php. I now have a sample line in all excel templates indicating that most of my columns contain text with more than 255 characters. Is this really the intended way to handle things? It feels like a bad and error prone work-around. My Excel files will be distributed for completion of data and then imported again and I dont like sending out junk data like this, and even worse - relying on random users not to change this junk data so that import will run smoothly.

    Lastly: will I need to supply my excel templates as part of the ssis install package when delivering this to the production server?

    Thanks a million times for your great posts!

    ReplyDelete
  16. Hi, A really useful blog.
    Can you show us how to perform the below task.

    Export MULTIPLE sql server tables data in to same excel file but different tabs.

    For example I have TEST001, TEST002,TEST003 tables. I need to Export in to SAMPLE excel file with sheet names TEST001,TEST002,TEST003

    ReplyDelete
  17. Hi, A really useful blog.
    Can you show us how to perform the below task.

    Export MULTIPLE sql server tables data in to same excel file but different tabs.

    For example I have TEST001, TEST002,TEST003 tables. I need to Export in to SAMPLE excel file with sheet names TEST001,TEST002,TEST003

    ReplyDelete
  18. hi,
    It is a good post. But i have a column of a big size and i am creating the sheet having a column size of nvarchar(4000) but it gives error in that..

    Is it not possible to create a large column size in excel file dynamically??

    ReplyDelete
  19. This works fine when the package is executed from ssis. But, when the package is deployed in SQL Server and when the job is executed it says failed to acquire the connection to the excel manager and the execute sql task to create the excel file fails

    ReplyDelete
  20. Hi,

    I download the package, and make it work on my local. But I got error when I tried the following:

    In the Data flow, after I open Excel Destination, then click on OK

    I got the following error
    ...
    check that the object exists in the database.

    So I have to cancel it. I delete
    'Excel Destination' and tried to create a new one again, but get the same error.

    How to recreate 'Excel Destination'?

    ReplyDelete
  21. Hi,

    I delete the 'Excel Destination' in data flow then tried to recreate it ,but got
    error
    ...
    check that the object exists in the database.

    How to recreate 'Excel Destination' without getting error?

    Thanks!

    ReplyDelete
  22. Hi Rafael

    i have tried out the steps what u said in previous section and this one.But im getting an ERROR message as follows

    "[Execute SQL Task] Error: Executing the query "CREATE TABLE `Excel Destination` (
    `HeaderID` INTE..." failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

    ReplyDelete
  23. This is a fantastic post! Everything was working in my package except I was missing the create table step. This new process that I created will save headaches. Bravo!

    ReplyDelete
  24. Thanks for posting the article. This was perfect for quick dump of data for our analysts today.

    ReplyDelete
  25. Great Article. Easy to understand! Regards. Leonard S.

    ReplyDelete
  26. Rafael I want to map the columns on the excel destination dyanmically for instance
    one monht my columns are 20101,20102,2013
    next month they are 20102,20103,20104
    it is clear for me how to create the excel file dynamically bur the problem is to generate dyanmically the columns names during the map to the excel destination

    ReplyDelete
  27. Hello! I've a question... what happens when in the folder you should have the excel files, you don't have any file? the package will stop with an error! right!? So have you any idea what can we do in this case? When no Excel... don't do anything

    ReplyDelete
  28. Ricardo,
    The Excel files are created in the second Execute SQL Task inside of the ForEach loop container.

    ReplyDelete
  29. Gracielena,
    There is no out of the box functionality that allows for that kind of dynamic mapping in a dataflow task. You would need to create your package programmatically or re-design the process in such way that the number,name and data type of the columns is always the same.

    ReplyDelete
  30. Hi Rafael,

    Thanks for the nice and useful post.My requirement is to create an excel file on each iteration and send that file as an outlook attachment in email to every user.
    Can you help me? If you can provide me your email id, i can send you the detail scenarion.

    Thanks

    ReplyDelete
  31. Vicky,

    I don't any example handy, but the web if your friend. You can start by looking at the MSDN SSIS forum. I just did a quick search and found this: http://tinyurl.com/2atkms5

    ReplyDelete
  32. Rafael,

    Thanks for spending time on investigating. I will look into the link provided by you and let you know if i get any solution.

    Thanks, Have a great day..

    Vicky

    ReplyDelete
  33. Hi Rafael, Thanks for this very useful post. I followed your example and every thing works fine except that I need to create a sheet that has spaces in the name like 'Data Sheet 1'. It created 'Data_Sheet_1' in the destination Excel file. I tried the following different ways (put single quotes or [ ] or $ or combined), none of them worked. The Excel file exported from my SSIS package will be used as input file for another application which is looking for that specific sheet name. Can you help me on this? Thanks.

    ReplyDelete
  34. OMG, THANK YOU for posting this! My package kept erroring out on me over and over again and no matter what I tried and how many other blogs I read, your post is the only one that worked!! Thank you for saving my sanity!!

    ReplyDelete
  35. Rafael,

    I have a slightly different approach which is failing. Can you help me please?

    Inside a for each loop, I have a file system task which has XL templates as source and destinations. Then I have a variable where the file name is getting changed to second and minutes and so on.

    the package is failing saying that Open Rowset failed. In the advanced editor for the excel destination the open rowset is blank.

    Please help

    ReplyDelete
  36. sir,

    I want to dump 4 different views or tables from the sql database to 4 different sheets in a single excel file using script task. Can you help me?

    Ragards,
    Ashwin.

    ReplyDelete
  37. I followed the same exact steps as specified here. But still getting error stating that

    [Excel Destination [630]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

    [Excel Destination [630]] Error: Opening a rowset for "EmployeeCode-999" failed. Check that the object exists in the database.

    Can someone please help?

    ReplyDelete
  38. Hello !

    Thanks a lot for sharing this valuable piece of information, it resolved a problem I carried for a few weeks...

    I just want to add that in my case I had to set the DelayValidation to True for the Data Flow Task container, otherwise I would get an error telling me that the table (for the Excel file) wasn't available.

    Hope this helps and thanks again !
    Bruno.

    ReplyDelete
  39. Hi Rafeal,

    At this point in your instructions 'Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager". Is the 'Excel Connection Manager' the same one we previously created in step 1, or a new one?
    Thanks
    Shaun

    ReplyDelete
  40. Dear Rafael,
    Thank you for this post. I had a question, Can we have a pre-formatted header row, with background colors, fonts, etc.

    Thanks,
    Shashi

    ReplyDelete
  41. hi now i want this excel file to move this file to database

    ReplyDelete
  42. Hi Rafael, Thanks for the artical it really helped me with and issue I'm having. We've encoutered another and was wondering if you had some advice.
    I've used the execute SQL task like you said to create and excel file but where mine differed is that instead of using SQL code I passed a Variable which has the code to create the table in excel this variable changes every loop thus creating multiple excel documents and the columns and the data types are different on each loop. The issue i'm having is that when I try write from my source to the excel destination i get a "Cannot convert between unicode and non-unicode string data types".

    Normally You'd use a transformation task but as the columns vary on each loop how can i achieve this?

    I hope you or someone else can provide assistance as I've been struggling on this for two days

    Thanks
    Fabrizio

    ReplyDelete
  43. Nice article. This helped me solve my issue of creating dynamic excel destinations.

    ReplyDelete
  44. This comment has been removed by the author.

    ReplyDelete

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