Dynamic report on screen vs. export

I was wondering if it's possible to display a report, but export an expanded version of that report? For instance if I have a report with, say, 6 columns, when the user exports to Excel I want to export 12 columns (6 that were displayed plus 6 additional columns). I tried hiding the additional columns, but they're also hidden in the export. I ended-up creating a separate report all together for exporting, but this is confusing to the user. Is something like this even possible?

Unfortunately renderer-specific formatting adjustments are not supported. You will probably need to construct two versions of the same report (one with the extra columns) and instruct your users to use the appropriate one for the output format.

Dynamic header column names in Excel

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.

dynamic export to excel from web

hi there,

this is my first time really using DTS packages and am trying to export some data to an excel file through a jsp page. This isn't my main problem tho...

the main problem is that i've coded the activeX script to dynamically name the file, as well as add the column headings in the excel file. The problem arises when I go to the data transform task and in the Destination tab, I can't select the worksheet that my code apparently creates.

Naturally it won't run because it will return me an error saying that the destination doesn't exist.

Any thoughts?

Thanks in advance.Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||That's great help! Thanks!

My next questions are:
1. Is it possible to pop-up a Save As dialog box when you run the DTS? I tried the msoFileDialogSaveAs but of course it doesn't work because it's not an Office app.

2. Is there a way to set global variables in jsp? I've seen code for asp, but haven't found any sample codes.

Thanks again.

Originally posted by SHICKS
Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||I don't know if JSP supports it, but we use ADO to save a stream of data as XML. Then using an XSL-T transform, we set the file up just about any way the user wants it (comm-delimited, fixed width, different delimiters, etc).

hmscott

Originally posted by SHICKS
I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||Shicks: The problem with creating in a csv file is that if you have a large number field and you try to open the file in Excel, it won't retain the format of the number. I've had this happen on many occasion.

Here's my current situation:
I've now been able to create a dts package that exports data to an excel file. For security reasons, I saved the package as a .dts file on our server to be called by the webserver when run.

The question is.. can I set the global variables of the package by referencing the file itself rather than the package on the server? Also, can this be done in java?

Dynamic columns for Flat File destination?

I have a database app, and we're implementing various data export features using SSIS.

Basically, it's a couple of straight extracts of various recordsets/views, etc. to CSV (flat files) from our SQL Server 2005 database, so I'm creating an SSIS package for each of these datasets.

So far, so good, but my problem comes here: My requirements call for users to select from a list of available columns the fields that they want to include in their exported file. Then, the package should run, but only output the columns specified by the user.

Does anyone have any idea as to the best way to accomplish this? To recap, at design time, I know which columns the users will have to choose from, but at run time, they will specify the columns to export to the flat file.

Any help or guidance here is greatly appreciated

You will need to create the package programatically in order to acheive this.

http://msdn2.microsoft.com/en-us/library/ms345167.aspx is a good place to start.

Donald

|||

Thanks Donald... I have one more thing to ask. Is it possible to create and execute a package from scratch within a Script Task?

I really want to avoid scripting this stuff outside of the SSIS package (ie, in a VB.NET assembly), because my solution architecture depends on encapsulating all of the logic for SSIS stuff within the package.

If it's not possible from within a script task, could there possibly be another way to achieve this while preserving encapsulation of the logic within the SSIS package?

|||

Yes it is possible to do it in a script task. the code is exactly the same as if you were doing it outside of SSIS (as long as you were using VB.Net of course :)

The best example I have seen so far is here: http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3 Click on "Create Packages Dynamically" in the bottom right hand corner.

-Jamie

|||

Hi Jamie,

again, thanks for the reply... While that code is indeed a fine example of creating packages programmatically, there's a bit of a problem with trying to replicate it inside of SSIS.

That code sample (as well as pretty much all others I've found for building packages programatically) imports the Microsoft.SqlServer.Dts.Runtime.Wrapper and the rosoft.SqlServer.Dts.Pipeline.Wrapper namespaces. Apparently neither of these namespaces are available from within a SSIS script task. The VSA IDE won't let me add references to the assemblies containing these namespaces.

Is there something else I'm missing?

|||

It is not possible to create packages or new objects within packages using SSIS.

You may be able to build the functionality into an assembly which could be called from a script - but you'll need to deploy the assembly separately.

Donald

|||

Oops, I didn't know that! Sorry J Nail!

If I were you I would build it innto a custom task. Its not that difficult - not that much harder then doing it in the script task.

-Jamie

dynamic archiving

I need to set up a stored procedure or DTS that can export data to a text file - based on parametres passed when it's called. We have a scheduling server, where all our logging is stored and procedures are called. The data I need to export can be on diffierent servers in different databases.

I started with the following:

DECLARE
-- @.TableName varchar(128),
-- @.Filename varchar(200),
-- @.date_field varchar(20),
@.SQL varchar(2000)
-- SET @.TableName =

SET @.SQL = ''''+ 'bcp "SELECT * FROM '+@.TableName+ ' WHERE ' + @.Date_Field+
' > GETDATE()"'+ ' queryout "c:\test.txt" -c -q -U"sa" -P"" '+ ''''
print @.SQL

-- set @.SQL = 'EXEC master.dbo.xp_cmdshell ' + @.SQL
-- EXEC (@.SQL)

However, I'm not sure how to work in the dynamic server names. A variation of the above works fine locally on my machine - but I'm thinking I may need to use a DTS to get the servers in there...if so, does bcp even work through DTS? WHat about the variables? Does anyone have a better way of doing this?

for server name, you specify -S. Does this not work?

|||

Well, der. Thanks, I forgot about that.

I have come up with another problem, however:

I'm running BCP through a stored procedure so that the exports can be dynamic - however, I can't get the proc to fail if the bcp command fails. It returns an out put indicating failure, but won't actually fail the proc - I can't seem to use raiserror.

Is there a way to take the output from queryout and do something with it?

|||

I think I figured this out.

Still testing, however, it looks like adding "NO_OUTPUT" forces SQL server to interpret the results as either failure or success - if you don't specify this, it seems to consider the execution of xp_cmdshell rather than the result of the execution. (i.e., I would get the output results returned indicating the specifics of the error, but the value of @.@.ERROR within the proc would not change)

I could put the output results into a file, but that would complicate the process as I would then need to evaluate the output before proceeding (which would be a pain with a text file).

If anyone knows how to get the output into a table on execution, especially a temp table, that would be nice to know...

|||This might be fixed in SQL 2005, where if command inside xp_cmdshell fails, then the batch fails, but not sure, you should test it out. I'm assuming you're on SQL 2000.|||

We don't have 2005. Still on 2000.

This is terribly frustrating. :(

Any further suggestions would be greatly appreciated, as this still seems to be wonky.

|||

did you try checking the return value from xp_cmdshell?

i.e.

declare @.ret int

exec @.ret = xp_cmdshell 'bcp xxxxx'

if @.ret <> 0 raiserror ('Error!', 16,1)

go

|||

This worked great! it forces an error when run from a DTS.

In addition, I forgot that most system stuff can be saved in a temp table - i.e.,

create table ##output

(output varchar(255))

Insert ##output

(output)

exec xp_cmdshell @.SQL

So this was great! thanks so much for your help.