Folks,
I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.
With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.
When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).
How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...
Thank you.
I am afraid that is something that is not that easy in SSIS. Jamie, has a serie of posts that talk about some differences with DTS:
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx
In SSIS the data flow needs to know the column metadata at design time. One option could be to generate packages programatically. Books on line has a chapter about that.
|||Hi:
I have a related problem. I have multiple packages, but only a single table structure, and so I worked around by making a template XLS file, and using a filesystem task to make copies. The template has the required col headers.
Now the problem is that when a data structure changes, I have to re-do all my packages to incorporate the changed metadata.
Your problem description gave me an idea, and I am planning to try out the following:
1. Execute SQL task to call a stored procedure that drops my temp_table, and then inserts my resultset into temp_table (which now has all the columns required).
2. Have a script task that makes a ADO.net connection, gets a dataset, saves my data from temp_table to an XML file, creates a new XLS file, and calls the OpenXML method to open my data in the excel.
This will probably need VSTO installed on the same machine as BIDS. Since the Script Task only allows reference to managed assemblies from a particular path(s), I would need to create a custom .net DLL and store in those paths. The .net DLL will internally use Office Interop COM if I have office 2003 or lower.
Else I could simply save XML files and associate XML files with Excel by default.
I had done something similar in VB.net, and the only problem I see is that the XLS files thus created are bloated. Opening them and saving them again can compress the file to around half its size.
Does this approach look feasible? Maybe the gurus on this forum can point out flaws in this?
HTH
Kar
|||Rafael and Karfast -
I really appreciate your replies. I took a look at Jamie's post mentioned by Rafael and I am just speechless. My concern in this migration from DTS to SSIS is all what we had in DTS and we are not having in SSIS. I truly understand some losses are needed towards a better final result but this case for an example (dynamic header column names) really seems not to be possible to be implemented in SSIS, or at least will require a lot of code intelligence versus something that was really simple in DTS.
I work for EDS and DBAs here are saying we will migrate soon for the next SQL Server 2005 version (which does not support DTS) and I am just deadly scared of what is gonna happen when we truly have to migrate all our packages.
I wonder why Microsoft still has these surprises for us and maybe this is something that they were willing to change in SSIS? As Karfast said, I guess I will wait for our SSIS gurus to discuss this a little bit more.
Thanks to all.
|||
Gabriel,
Perhaps few functionalities have been changed from DTS, but personally SSIS has a lot more potential to become an Enterprise ETL platform. How familiar are you with SSIS at this point? perhaps you should look a little bit into the all other features it offers. I never used DTS, but I cerntarly understand how surprising is to find out that something like that is missing.
