Dynamic Linking - Crystal XI - Image doesnt change

Im using desktop Crystal XI - connecting to a SQL database via ODBC connection.

Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.

Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.

Ive created a formula field (based on records in the SQL db) to generate the link/document location:

"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"

(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)

I then reference the formula field in the graphic location.

So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.

Can anyone think of a reason why the object/file is not changing for each record?

Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.

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 filenames from scheduled reports

We created a sales history report for a client that has one prompt, Sales
Region, and when they schedule the report all the filenames of the Excel
attachments are the same. They are all the report name.
They would like each file to have a name like
"SalesRegion-SelectedRegion.xls". And possibly the dynamic month generated
in the report from a user defined function.
Is there anyway to include the prompted Sales Region in the filename?
On a related issue I have noticed people discussing doing Data Driven
subscriptions, but even though the report data source credentials are saved
in the report server it is not an option for them.
Any help would be greatly appreciated.Without using Data Driven subscription, the fileshare delivery extension
does not have any means to accomplish this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> We created a sales history report for a client that has one prompt, Sales
> Region, and when they schedule the report all the filenames of the Excel
> attachments are the same. They are all the report name.
> They would like each file to have a name like
> "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> generated
> in the report from a user defined function.
> Is there anyway to include the prompted Sales Region in the filename?
> On a related issue I have noticed people discussing doing Data Driven
> subscriptions, but even though the report data source credentials are
> saved
> in the report server it is not an option for them.
> Any help would be greatly appreciated.|||These reports are scheduled to be delivered as Excel attachments. Please
elaborate on your response to this.
"Daniel Reib [MSFT]" wrote:
> Without using Data Driven subscription, the fileshare delivery extension
> does not have any means to accomplish this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> > We created a sales history report for a client that has one prompt, Sales
> > Region, and when they schedule the report all the filenames of the Excel
> > attachments are the same. They are all the report name.
> >
> > They would like each file to have a name like
> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> > generated
> > in the report from a user defined function.
> >
> > Is there anyway to include the prompted Sales Region in the filename?
> >
> > On a related issue I have noticed people discussing doing Data Driven
> > subscriptions, but even though the report data source credentials are
> > saved
> > in the report server it is not an option for them.
> >
> > Any help would be greatly appreciated.
>
>|||When a regular subscription is created the name of the file must be passed
in. The Fileshare extension provides no means for which to add information
dynamically to the name of the generated file. If you used a data driven
subscription you could create the name in the SQL query and have that passed
in as the file name. If the query is smart enough it could generate the
filename with the desired parameter embedded. However, you said DD
subscriptions were not an option, and the Fileshare extension does not have
the ability to do this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> These reports are scheduled to be delivered as Excel attachments. Please
> elaborate on your response to this.
>
> "Daniel Reib [MSFT]" wrote:
>> Without using Data Driven subscription, the fileshare delivery extension
>> does not have any means to accomplish this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> > We created a sales history report for a client that has one prompt,
>> > Sales
>> > Region, and when they schedule the report all the filenames of the
>> > Excel
>> > attachments are the same. They are all the report name.
>> >
>> > They would like each file to have a name like
>> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> > generated
>> > in the report from a user defined function.
>> >
>> > Is there anyway to include the prompted Sales Region in the filename?
>> >
>> > On a related issue I have noticed people discussing doing Data Driven
>> > subscriptions, but even though the report data source credentials are
>> > saved
>> > in the report server it is not an option for them.
>> >
>> > Any help would be greatly appreciated.
>>|||Thanks for your assistance Daniel. So there is no way to override the
"report name" as the attachment name in a standard scheduled report
regardless of the render type.
Outside of the fact that you should be able to select the attachment name in
the report manager just like you do the subject when scheduling a report.
i.e. "@.ReportName was executed at @.ExecutionTime"
The sales manager at our client location refuses to open each Excel
spreadsheet attachement in his email to find the sales region and then select
"Save As" with this name for each of their 77 sales regions every month.
I can't blame them. This is supposed to be automated.
Do you have any suggestions to make this work as an automated solution?
If this has to be done via Data Driven Subscriptions, do you have any
information about why a report that has stored credential would not have the
option of data driven subscription? If that works, could you please give me
an example to use for giving these managers their reports with Region/Month
filenames?
I have been using RS since the first public beta. It is an amazing product.
We are just struggling with a few of these detail issues.
Thank you so much for your time.
"Daniel Reib [MSFT]" wrote:
> When a regular subscription is created the name of the file must be passed
> in. The Fileshare extension provides no means for which to add information
> dynamically to the name of the generated file. If you used a data driven
> subscription you could create the name in the SQL query and have that passed
> in as the file name. If the query is smart enough it could generate the
> filename with the desired parameter embedded. However, you said DD
> subscriptions were not an option, and the Fileshare extension does not have
> the ability to do this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> > These reports are scheduled to be delivered as Excel attachments. Please
> > elaborate on your response to this.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Without using Data Driven subscription, the fileshare delivery extension
> >> does not have any means to accomplish this.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> > We created a sales history report for a client that has one prompt,
> >> > Sales
> >> > Region, and when they schedule the report all the filenames of the
> >> > Excel
> >> > attachments are the same. They are all the report name.
> >> >
> >> > They would like each file to have a name like
> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> > generated
> >> > in the report from a user defined function.
> >> >
> >> > Is there anyway to include the prompted Sales Region in the filename?
> >> >
> >> > On a related issue I have noticed people discussing doing Data Driven
> >> > subscriptions, but even though the report data source credentials are
> >> > saved
> >> > in the report server it is not an option for them.
> >> >
> >> > Any help would be greatly appreciated.
> >>
> >>
> >>
>
>|||Data driven subscriptions are not supported in the standard edition of RS.
Is it possible that he has that?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> Thanks for your assistance Daniel. So there is no way to override the
> "report name" as the attachment name in a standard scheduled report
> regardless of the render type.
> Outside of the fact that you should be able to select the attachment name
> in
> the report manager just like you do the subject when scheduling a report.
> i.e. "@.ReportName was executed at @.ExecutionTime"
> The sales manager at our client location refuses to open each Excel
> spreadsheet attachement in his email to find the sales region and then
> select
> "Save As" with this name for each of their 77 sales regions every month.
> I can't blame them. This is supposed to be automated.
> Do you have any suggestions to make this work as an automated solution?
> If this has to be done via Data Driven Subscriptions, do you have any
> information about why a report that has stored credential would not have
> the
> option of data driven subscription? If that works, could you please give
> me
> an example to use for giving these managers their reports with
> Region/Month
> filenames?
> I have been using RS since the first public beta. It is an amazing
> product.
> We are just struggling with a few of these detail issues.
> Thank you so much for your time.
>
> "Daniel Reib [MSFT]" wrote:
>> When a regular subscription is created the name of the file must be
>> passed
>> in. The Fileshare extension provides no means for which to add
>> information
>> dynamically to the name of the generated file. If you used a data driven
>> subscription you could create the name in the SQL query and have that
>> passed
>> in as the file name. If the query is smart enough it could generate the
>> filename with the desired parameter embedded. However, you said DD
>> subscriptions were not an option, and the Fileshare extension does not
>> have
>> the ability to do this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> > These reports are scheduled to be delivered as Excel attachments.
>> > Please
>> > elaborate on your response to this.
>> >
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Without using Data Driven subscription, the fileshare delivery
>> >> extension
>> >> does not have any means to accomplish this.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> > We created a sales history report for a client that has one prompt,
>> >> > Sales
>> >> > Region, and when they schedule the report all the filenames of the
>> >> > Excel
>> >> > attachments are the same. They are all the report name.
>> >> >
>> >> > They would like each file to have a name like
>> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> >> > generated
>> >> > in the report from a user defined function.
>> >> >
>> >> > Is there anyway to include the prompted Sales Region in the
>> >> > filename?
>> >> >
>> >> > On a related issue I have noticed people discussing doing Data
>> >> > Driven
>> >> > subscriptions, but even though the report data source credentials
>> >> > are
>> >> > saved
>> >> > in the report server it is not an option for them.
>> >> >
>> >> > Any help would be greatly appreciated.
>> >>
>> >>
>> >>
>>|||Hello Daniel, That is exactly it. They have the standard version of RS.
Do we have any options using either Email or Fileshare to get a Excel
filename other than the default report name? i.e. Instead of all 77 email
having the same attachment filename "Sales Region.xls" it could be even the a
combination of the report name and report parameter "@.SALESREGION" resulting
in "Sales Region - North East.xls".
Thanks.
"Daniel Reib [MSFT]" wrote:
> Data driven subscriptions are not supported in the standard edition of RS.
> Is it possible that he has that?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> > Thanks for your assistance Daniel. So there is no way to override the
> > "report name" as the attachment name in a standard scheduled report
> > regardless of the render type.
> >
> > Outside of the fact that you should be able to select the attachment name
> > in
> > the report manager just like you do the subject when scheduling a report.
> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >
> > The sales manager at our client location refuses to open each Excel
> > spreadsheet attachement in his email to find the sales region and then
> > select
> > "Save As" with this name for each of their 77 sales regions every month.
> >
> > I can't blame them. This is supposed to be automated.
> >
> > Do you have any suggestions to make this work as an automated solution?
> >
> > If this has to be done via Data Driven Subscriptions, do you have any
> > information about why a report that has stored credential would not have
> > the
> > option of data driven subscription? If that works, could you please give
> > me
> > an example to use for giving these managers their reports with
> > Region/Month
> > filenames?
> >
> > I have been using RS since the first public beta. It is an amazing
> > product.
> > We are just struggling with a few of these detail issues.
> >
> > Thank you so much for your time.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> When a regular subscription is created the name of the file must be
> >> passed
> >> in. The Fileshare extension provides no means for which to add
> >> information
> >> dynamically to the name of the generated file. If you used a data driven
> >> subscription you could create the name in the SQL query and have that
> >> passed
> >> in as the file name. If the query is smart enough it could generate the
> >> filename with the desired parameter embedded. However, you said DD
> >> subscriptions were not an option, and the Fileshare extension does not
> >> have
> >> the ability to do this.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> > These reports are scheduled to be delivered as Excel attachments.
> >> > Please
> >> > elaborate on your response to this.
> >> >
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> extension
> >> >> does not have any means to accomplish this.
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> > We created a sales history report for a client that has one prompt,
> >> >> > Sales
> >> >> > Region, and when they schedule the report all the filenames of the
> >> >> > Excel
> >> >> > attachments are the same. They are all the report name.
> >> >> >
> >> >> > They would like each file to have a name like
> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> >> > generated
> >> >> > in the report from a user defined function.
> >> >> >
> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> > filename?
> >> >> >
> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> > Driven
> >> >> > subscriptions, but even though the report data source credentials
> >> >> > are
> >> >> > saved
> >> >> > in the report server it is not an option for them.
> >> >> >
> >> >> > Any help would be greatly appreciated.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Maybe I am a little confused here. Do you have 77 subscriptions? How are
you generating each file?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> Hello Daniel, That is exactly it. They have the standard version of RS.
> Do we have any options using either Email or Fileshare to get a Excel
> filename other than the default report name? i.e. Instead of all 77 email
> having the same attachment filename "Sales Region.xls" it could be even
> the a
> combination of the report name and report parameter "@.SALESREGION"
> resulting
> in "Sales Region - North East.xls".
> Thanks.
> "Daniel Reib [MSFT]" wrote:
>> Data driven subscriptions are not supported in the standard edition of
>> RS.
>> Is it possible that he has that?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
>> > Thanks for your assistance Daniel. So there is no way to override the
>> > "report name" as the attachment name in a standard scheduled report
>> > regardless of the render type.
>> >
>> > Outside of the fact that you should be able to select the attachment
>> > name
>> > in
>> > the report manager just like you do the subject when scheduling a
>> > report.
>> > i.e. "@.ReportName was executed at @.ExecutionTime"
>> >
>> > The sales manager at our client location refuses to open each Excel
>> > spreadsheet attachement in his email to find the sales region and then
>> > select
>> > "Save As" with this name for each of their 77 sales regions every
>> > month.
>> >
>> > I can't blame them. This is supposed to be automated.
>> >
>> > Do you have any suggestions to make this work as an automated solution?
>> >
>> > If this has to be done via Data Driven Subscriptions, do you have any
>> > information about why a report that has stored credential would not
>> > have
>> > the
>> > option of data driven subscription? If that works, could you please
>> > give
>> > me
>> > an example to use for giving these managers their reports with
>> > Region/Month
>> > filenames?
>> >
>> > I have been using RS since the first public beta. It is an amazing
>> > product.
>> > We are just struggling with a few of these detail issues.
>> >
>> > Thank you so much for your time.
>> >
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> When a regular subscription is created the name of the file must be
>> >> passed
>> >> in. The Fileshare extension provides no means for which to add
>> >> information
>> >> dynamically to the name of the generated file. If you used a data
>> >> driven
>> >> subscription you could create the name in the SQL query and have that
>> >> passed
>> >> in as the file name. If the query is smart enough it could generate
>> >> the
>> >> filename with the desired parameter embedded. However, you said DD
>> >> subscriptions were not an option, and the Fileshare extension does not
>> >> have
>> >> the ability to do this.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> >> > These reports are scheduled to be delivered as Excel attachments.
>> >> > Please
>> >> > elaborate on your response to this.
>> >> >
>> >> >
>> >> > "Daniel Reib [MSFT]" wrote:
>> >> >
>> >> >> Without using Data Driven subscription, the fileshare delivery
>> >> >> extension
>> >> >> does not have any means to accomplish this.
>> >> >>
>> >> >> --
>> >> >> -Daniel
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> >> > We created a sales history report for a client that has one
>> >> >> > prompt,
>> >> >> > Sales
>> >> >> > Region, and when they schedule the report all the filenames of
>> >> >> > the
>> >> >> > Excel
>> >> >> > attachments are the same. They are all the report name.
>> >> >> >
>> >> >> > They would like each file to have a name like
>> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> >> >> > generated
>> >> >> > in the report from a user defined function.
>> >> >> >
>> >> >> > Is there anyway to include the prompted Sales Region in the
>> >> >> > filename?
>> >> >> >
>> >> >> > On a related issue I have noticed people discussing doing Data
>> >> >> > Driven
>> >> >> > subscriptions, but even though the report data source credentials
>> >> >> > are
>> >> >> > saved
>> >> >> > in the report server it is not an option for them.
>> >> >> >
>> >> >> > Any help would be greatly appreciated.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||For the purposes of this question there are two reports involved. One for
Sales Regions and another for Product Regions. Each report has a parameter
for its respective region.
For Sales Region (14 regions) and Product Regions (63 regions) each have a
subscription setup to send as an email.
They originally created 77 reports covering each of the regions and a
subscription to each. Then management decided to make a few modifications to
the report format. Then we were faced with modifying 77 reports anytime
changes were needed. Each report had a unique name so that was the name of
the Excel Attachment.
We created the two reports with parameters so when changes were needed to
the format there were only two reports that needed to be modified. But now
that each region comes from the same report the Excel attachment name is the
same for all reports.
They simply want to setup the subscription to email each region with an
attachment name descriptive of the report. i.e. Sales Region - Southwest.xls
These reports use dynamic dates so the report is always based on the
previous complete month. It would be even better if the email attachment
would have the name "Sales Region - Southwest - 1204.xls" adding the month
year to the attachment name.
Do you have any idea how we could accomplish this? Would it even be
possible to set the email Excel attachment name if we create a custom
application? Is attachment name something that is exposed with the Reporting
Services API?
Thanks.
"Daniel Reib [MSFT]" wrote:
> Maybe I am a little confused here. Do you have 77 subscriptions? How are
> you generating each file?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> > Hello Daniel, That is exactly it. They have the standard version of RS.
> >
> > Do we have any options using either Email or Fileshare to get a Excel
> > filename other than the default report name? i.e. Instead of all 77 email
> > having the same attachment filename "Sales Region.xls" it could be even
> > the a
> > combination of the report name and report parameter "@.SALESREGION"
> > resulting
> > in "Sales Region - North East.xls".
> >
> > Thanks.
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Data driven subscriptions are not supported in the standard edition of
> >> RS.
> >> Is it possible that he has that?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> >> > Thanks for your assistance Daniel. So there is no way to override the
> >> > "report name" as the attachment name in a standard scheduled report
> >> > regardless of the render type.
> >> >
> >> > Outside of the fact that you should be able to select the attachment
> >> > name
> >> > in
> >> > the report manager just like you do the subject when scheduling a
> >> > report.
> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >> >
> >> > The sales manager at our client location refuses to open each Excel
> >> > spreadsheet attachement in his email to find the sales region and then
> >> > select
> >> > "Save As" with this name for each of their 77 sales regions every
> >> > month.
> >> >
> >> > I can't blame them. This is supposed to be automated.
> >> >
> >> > Do you have any suggestions to make this work as an automated solution?
> >> >
> >> > If this has to be done via Data Driven Subscriptions, do you have any
> >> > information about why a report that has stored credential would not
> >> > have
> >> > the
> >> > option of data driven subscription? If that works, could you please
> >> > give
> >> > me
> >> > an example to use for giving these managers their reports with
> >> > Region/Month
> >> > filenames?
> >> >
> >> > I have been using RS since the first public beta. It is an amazing
> >> > product.
> >> > We are just struggling with a few of these detail issues.
> >> >
> >> > Thank you so much for your time.
> >> >
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> When a regular subscription is created the name of the file must be
> >> >> passed
> >> >> in. The Fileshare extension provides no means for which to add
> >> >> information
> >> >> dynamically to the name of the generated file. If you used a data
> >> >> driven
> >> >> subscription you could create the name in the SQL query and have that
> >> >> passed
> >> >> in as the file name. If the query is smart enough it could generate
> >> >> the
> >> >> filename with the desired parameter embedded. However, you said DD
> >> >> subscriptions were not an option, and the Fileshare extension does not
> >> >> have
> >> >> the ability to do this.
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> >> > These reports are scheduled to be delivered as Excel attachments.
> >> >> > Please
> >> >> > elaborate on your response to this.
> >> >> >
> >> >> >
> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >
> >> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> >> extension
> >> >> >> does not have any means to accomplish this.
> >> >> >>
> >> >> >> --
> >> >> >> -Daniel
> >> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> >> > We created a sales history report for a client that has one
> >> >> >> > prompt,
> >> >> >> > Sales
> >> >> >> > Region, and when they schedule the report all the filenames of
> >> >> >> > the
> >> >> >> > Excel
> >> >> >> > attachments are the same. They are all the report name.
> >> >> >> >
> >> >> >> > They would like each file to have a name like
> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> >> >> > generated
> >> >> >> > in the report from a user defined function.
> >> >> >> >
> >> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> >> > filename?
> >> >> >> >
> >> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> >> > Driven
> >> >> >> > subscriptions, but even though the report data source credentials
> >> >> >> > are
> >> >> >> > saved
> >> >> >> > in the report server it is not an option for them.
> >> >> >> >
> >> >> >> > Any help would be greatly appreciated.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Ok, I have a better idea what is going on now. Unfortunately I don't have
good news. The FileShare Delivery Extension does not have anyway to get
report parameters into the name of the file. It can't do this because the
Delivery API do not give it this information, so you would not be able to
even write your own extension to do this. The only option you have with
Standard is to create the 77 subscriptions, giving each subscription a
unique filename. (You could create 77 linked reports and have each report
have a single subscription. This would make managing them a little easier
since the descriptions of the subscriptions are not helpful)
The best option would be to use DataDriven subscriptions, but that would
require you purchasing the Enterprise Edition.
I hope that helps.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:BE830564-D7BC-4373-90AF-3A55B75E66E4@.microsoft.com...
> For the purposes of this question there are two reports involved. One for
> Sales Regions and another for Product Regions. Each report has a
> parameter
> for its respective region.
> For Sales Region (14 regions) and Product Regions (63 regions) each have a
> subscription setup to send as an email.
> They originally created 77 reports covering each of the regions and a
> subscription to each. Then management decided to make a few modifications
> to
> the report format. Then we were faced with modifying 77 reports anytime
> changes were needed. Each report had a unique name so that was the name
> of
> the Excel Attachment.
> We created the two reports with parameters so when changes were needed to
> the format there were only two reports that needed to be modified. But
> now
> that each region comes from the same report the Excel attachment name is
> the
> same for all reports.
> They simply want to setup the subscription to email each region with an
> attachment name descriptive of the report. i.e. Sales Region -
> Southwest.xls
> These reports use dynamic dates so the report is always based on the
> previous complete month. It would be even better if the email attachment
> would have the name "Sales Region - Southwest - 1204.xls" adding the month
> year to the attachment name.
> Do you have any idea how we could accomplish this? Would it even be
> possible to set the email Excel attachment name if we create a custom
> application? Is attachment name something that is exposed with the
> Reporting
> Services API?
> Thanks.
>
> "Daniel Reib [MSFT]" wrote:
>> Maybe I am a little confused here. Do you have 77 subscriptions? How
>> are
>> you generating each file?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
>> > Hello Daniel, That is exactly it. They have the standard version of
>> > RS.
>> >
>> > Do we have any options using either Email or Fileshare to get a Excel
>> > filename other than the default report name? i.e. Instead of all 77
>> > email
>> > having the same attachment filename "Sales Region.xls" it could be even
>> > the a
>> > combination of the report name and report parameter "@.SALESREGION"
>> > resulting
>> > in "Sales Region - North East.xls".
>> >
>> > Thanks.
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Data driven subscriptions are not supported in the standard edition of
>> >> RS.
>> >> Is it possible that he has that?
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
>> >> > Thanks for your assistance Daniel. So there is no way to override
>> >> > the
>> >> > "report name" as the attachment name in a standard scheduled report
>> >> > regardless of the render type.
>> >> >
>> >> > Outside of the fact that you should be able to select the attachment
>> >> > name
>> >> > in
>> >> > the report manager just like you do the subject when scheduling a
>> >> > report.
>> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
>> >> >
>> >> > The sales manager at our client location refuses to open each Excel
>> >> > spreadsheet attachement in his email to find the sales region and
>> >> > then
>> >> > select
>> >> > "Save As" with this name for each of their 77 sales regions every
>> >> > month.
>> >> >
>> >> > I can't blame them. This is supposed to be automated.
>> >> >
>> >> > Do you have any suggestions to make this work as an automated
>> >> > solution?
>> >> >
>> >> > If this has to be done via Data Driven Subscriptions, do you have
>> >> > any
>> >> > information about why a report that has stored credential would not
>> >> > have
>> >> > the
>> >> > option of data driven subscription? If that works, could you please
>> >> > give
>> >> > me
>> >> > an example to use for giving these managers their reports with
>> >> > Region/Month
>> >> > filenames?
>> >> >
>> >> > I have been using RS since the first public beta. It is an amazing
>> >> > product.
>> >> > We are just struggling with a few of these detail issues.
>> >> >
>> >> > Thank you so much for your time.
>> >> >
>> >> >
>> >> > "Daniel Reib [MSFT]" wrote:
>> >> >
>> >> >> When a regular subscription is created the name of the file must be
>> >> >> passed
>> >> >> in. The Fileshare extension provides no means for which to add
>> >> >> information
>> >> >> dynamically to the name of the generated file. If you used a data
>> >> >> driven
>> >> >> subscription you could create the name in the SQL query and have
>> >> >> that
>> >> >> passed
>> >> >> in as the file name. If the query is smart enough it could
>> >> >> generate
>> >> >> the
>> >> >> filename with the desired parameter embedded. However, you said DD
>> >> >> subscriptions were not an option, and the Fileshare extension does
>> >> >> not
>> >> >> have
>> >> >> the ability to do this.
>> >> >>
>> >> >> --
>> >> >> -Daniel
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> >> >> > These reports are scheduled to be delivered as Excel attachments.
>> >> >> > Please
>> >> >> > elaborate on your response to this.
>> >> >> >
>> >> >> >
>> >> >> > "Daniel Reib [MSFT]" wrote:
>> >> >> >
>> >> >> >> Without using Data Driven subscription, the fileshare delivery
>> >> >> >> extension
>> >> >> >> does not have any means to accomplish this.
>> >> >> >>
>> >> >> >> --
>> >> >> >> -Daniel
>> >> >> >> This posting is provided "AS IS" with no warranties, and confers
>> >> >> >> no
>> >> >> >> rights.
>> >> >> >>
>> >> >> >>
>> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> >> >> > We created a sales history report for a client that has one
>> >> >> >> > prompt,
>> >> >> >> > Sales
>> >> >> >> > Region, and when they schedule the report all the filenames of
>> >> >> >> > the
>> >> >> >> > Excel
>> >> >> >> > attachments are the same. They are all the report name.
>> >> >> >> >
>> >> >> >> > They would like each file to have a name like
>> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic
>> >> >> >> > month
>> >> >> >> > generated
>> >> >> >> > in the report from a user defined function.
>> >> >> >> >
>> >> >> >> > Is there anyway to include the prompted Sales Region in the
>> >> >> >> > filename?
>> >> >> >> >
>> >> >> >> > On a related issue I have noticed people discussing doing Data
>> >> >> >> > Driven
>> >> >> >> > subscriptions, but even though the report data source
>> >> >> >> > credentials
>> >> >> >> > are
>> >> >> >> > saved
>> >> >> >> > in the report server it is not an option for them.
>> >> >> >> >
>> >> >> >> > Any help would be greatly appreciated.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||I appreciate your assistance. Thanks.
"Daniel Reib [MSFT]" wrote:
> Ok, I have a better idea what is going on now. Unfortunately I don't have
> good news. The FileShare Delivery Extension does not have anyway to get
> report parameters into the name of the file. It can't do this because the
> Delivery API do not give it this information, so you would not be able to
> even write your own extension to do this. The only option you have with
> Standard is to create the 77 subscriptions, giving each subscription a
> unique filename. (You could create 77 linked reports and have each report
> have a single subscription. This would make managing them a little easier
> since the descriptions of the subscriptions are not helpful)
> The best option would be to use DataDriven subscriptions, but that would
> require you purchasing the Enterprise Edition.
> I hope that helps.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:BE830564-D7BC-4373-90AF-3A55B75E66E4@.microsoft.com...
> > For the purposes of this question there are two reports involved. One for
> > Sales Regions and another for Product Regions. Each report has a
> > parameter
> > for its respective region.
> >
> > For Sales Region (14 regions) and Product Regions (63 regions) each have a
> > subscription setup to send as an email.
> >
> > They originally created 77 reports covering each of the regions and a
> > subscription to each. Then management decided to make a few modifications
> > to
> > the report format. Then we were faced with modifying 77 reports anytime
> > changes were needed. Each report had a unique name so that was the name
> > of
> > the Excel Attachment.
> >
> > We created the two reports with parameters so when changes were needed to
> > the format there were only two reports that needed to be modified. But
> > now
> > that each region comes from the same report the Excel attachment name is
> > the
> > same for all reports.
> >
> > They simply want to setup the subscription to email each region with an
> > attachment name descriptive of the report. i.e. Sales Region -
> > Southwest.xls
> > These reports use dynamic dates so the report is always based on the
> > previous complete month. It would be even better if the email attachment
> > would have the name "Sales Region - Southwest - 1204.xls" adding the month
> > year to the attachment name.
> >
> > Do you have any idea how we could accomplish this? Would it even be
> > possible to set the email Excel attachment name if we create a custom
> > application? Is attachment name something that is exposed with the
> > Reporting
> > Services API?
> >
> > Thanks.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Maybe I am a little confused here. Do you have 77 subscriptions? How
> >> are
> >> you generating each file?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> >> > Hello Daniel, That is exactly it. They have the standard version of
> >> > RS.
> >> >
> >> > Do we have any options using either Email or Fileshare to get a Excel
> >> > filename other than the default report name? i.e. Instead of all 77
> >> > email
> >> > having the same attachment filename "Sales Region.xls" it could be even
> >> > the a
> >> > combination of the report name and report parameter "@.SALESREGION"
> >> > resulting
> >> > in "Sales Region - North East.xls".
> >> >
> >> > Thanks.
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> Data driven subscriptions are not supported in the standard edition of
> >> >> RS.
> >> >> Is it possible that he has that?
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> >> >> > Thanks for your assistance Daniel. So there is no way to override
> >> >> > the
> >> >> > "report name" as the attachment name in a standard scheduled report
> >> >> > regardless of the render type.
> >> >> >
> >> >> > Outside of the fact that you should be able to select the attachment
> >> >> > name
> >> >> > in
> >> >> > the report manager just like you do the subject when scheduling a
> >> >> > report.
> >> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >> >> >
> >> >> > The sales manager at our client location refuses to open each Excel
> >> >> > spreadsheet attachement in his email to find the sales region and
> >> >> > then
> >> >> > select
> >> >> > "Save As" with this name for each of their 77 sales regions every
> >> >> > month.
> >> >> >
> >> >> > I can't blame them. This is supposed to be automated.
> >> >> >
> >> >> > Do you have any suggestions to make this work as an automated
> >> >> > solution?
> >> >> >
> >> >> > If this has to be done via Data Driven Subscriptions, do you have
> >> >> > any
> >> >> > information about why a report that has stored credential would not
> >> >> > have
> >> >> > the
> >> >> > option of data driven subscription? If that works, could you please
> >> >> > give
> >> >> > me
> >> >> > an example to use for giving these managers their reports with
> >> >> > Region/Month
> >> >> > filenames?
> >> >> >
> >> >> > I have been using RS since the first public beta. It is an amazing
> >> >> > product.
> >> >> > We are just struggling with a few of these detail issues.
> >> >> >
> >> >> > Thank you so much for your time.
> >> >> >
> >> >> >
> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >
> >> >> >> When a regular subscription is created the name of the file must be
> >> >> >> passed
> >> >> >> in. The Fileshare extension provides no means for which to add
> >> >> >> information
> >> >> >> dynamically to the name of the generated file. If you used a data
> >> >> >> driven
> >> >> >> subscription you could create the name in the SQL query and have
> >> >> >> that
> >> >> >> passed
> >> >> >> in as the file name. If the query is smart enough it could
> >> >> >> generate
> >> >> >> the
> >> >> >> filename with the desired parameter embedded. However, you said DD
> >> >> >> subscriptions were not an option, and the Fileshare extension does
> >> >> >> not
> >> >> >> have
> >> >> >> the ability to do this.
> >> >> >>
> >> >> >> --
> >> >> >> -Daniel
> >> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> >> >> > These reports are scheduled to be delivered as Excel attachments.
> >> >> >> > Please
> >> >> >> > elaborate on your response to this.
> >> >> >> >
> >> >> >> >
> >> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >> >
> >> >> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> >> >> extension
> >> >> >> >> does not have any means to accomplish this.
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> -Daniel
> >> >> >> >> This posting is provided "AS IS" with no warranties, and confers
> >> >> >> >> no
> >> >> >> >> rights.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> >> >> > We created a sales history report for a client that has one
> >> >> >> >> > prompt,
> >> >> >> >> > Sales
> >> >> >> >> > Region, and when they schedule the report all the filenames of
> >> >> >> >> > the
> >> >> >> >> > Excel
> >> >> >> >> > attachments are the same. They are all the report name.
> >> >> >> >> >
> >> >> >> >> > They would like each file to have a name like
> >> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic
> >> >> >> >> > month
> >> >> >> >> > generated
> >> >> >> >> > in the report from a user defined function.
> >> >> >> >> >
> >> >> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> >> >> > filename?
> >> >> >> >> >
> >> >> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> >> >> > Driven
> >> >> >> >> > subscriptions, but even though the report data source
> >> >> >> >> > credentials
> >> >> >> >> > are
> >> >> >> >> > saved
> >> >> >> >> > in the report server it is not an option for them.
> >> >> >> >> >
> >> >> >> >> > Any help would be greatly appreciated.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

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

Please help....

I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.

My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.

I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.

Thanks,
Dave

You could use a Foreach Enumerator to loop through each file with the same structure and load it to the destination table, by placing the data flow within the loop container.

-Doug

|||

How would I go about getting the file name to change appropriately with each file. I have the foreach creating a variable called FileName. I have a script task picking off the information in the filename (format: <vendor #>_<start date>_<stop date>.xls). I then put vendor#, start date, and stop date into package variables. I guess my main concern is not knowing if I'm using expressions right. I mapped the ExcelFilePath and ServerName to user::FileName. With the file changing, do I have to set anything else to keep errors from occurring?

Thanks,

Dave

|||

Dave,

You shouldn't need to worry about the ServerName property for Excel. As for ExcelFilePath, I would want to make sure that the ConnectionString property was updated immediately and automatically when I provide a new value (outside the UI) for ExcelFilePath. Otherwise you could use an expression on ConnectionString, instead of simply mapping ExcelFilePath to User::FileName.

Updating the ConnectionString for Excel by using an expression has been discussed here previously. The expression shown in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1 should work for you when you insert your FileName variable at the proper place.

-Doug

dynamic excel destination depend on dataflow data

I created a data flow with complaicated SQL. There is "type" field in the output column.

I would like to created excel files for each "type" value

E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.

Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?

The conditional split has fixed conditions, it is not suitable for by dynamic number of value

For Loop condition is not a good choice because I need to run the complicated SQL for many time.

Thanks.

Put all data in one place, but with the additional type column. Then drive a loop to extract each type of data into a file. This assumes that each types file has the same format, and would require two data flows, the latter of which would be inside the loop.

I would store the list of unique types in advance, perhaps by multicasting the preparatory flow just prior to writing the full file, and then getting distinct type values, use the Sort component.

Your second data flow would just be source, conditional split acting as a filter then the destination. The split filter would be based on typecolumn == @.typevariable. @.typevariable would be set from the loop enumerator, it is type value for that iteration. I would also use that variable in an expression on the destination connection to give a type specific filename.

Dynamic display of SQL in Excel?

I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.See if under Data menu...Import External Data...New Web Query

Not sure of the particulars but it would get you started.

Originally posted by shadowplayer
I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.|||Thanks for your reply. That's exactly what I ended up doing. Found it by accident when I noticed the "little yellow box" that appeared when I cut & pasted data from the webpage into my spreadsheet.

I just saved the qry file and opened it in notepad to config the values.

This will save me HOURS of work.

VERY cool.

Thanks again for your reply!

Dynamic Database Connection - EXCEL to SQL Server

Hi *, is there a way to connect Excel to SQL Server so that Excel serves as a frontend to SQL Server? I heard rumors that this is possible with the 2007 release.

I'm looking for a product that helps me storing massive data outside an Excel file. Right now, I'm using Palo (open source multidimensional database).

Regards,
Steve

Are you looking for something along the lines described in this article: http://support.microsoft.com/kb/321686 titled "How to import data from Excel to SQL Server"?

|||Well, something like that... Basically, I wanna use Excel as a Front-End to SQL Server. That means, data input AND output in Excel. Data storage in SQL Server.

Any ideas?|||

What about using ADODB.Connection in VBA. That way you can use Excel as your interface and store data in SQL Server.

Jakob

|||

Visual Studio Tools for Office focuses on the scenario that you describe...you can find a good article that walks through the same basic scenario at: http://msdn2.microsoft.com/en-us/library/aa192473(office.11).aspx

If you can't use Visual Studio Tools for Office, you could potentially use a poor man's version of what it does under the covers. By adding an "Update" column and using the code in the link that Peter provided earlier, you could determine what rows need to be updated and execute an appropriate query.

If you aren't bound to the unique functionalities of Excel, a linked server in Access might be an easier way to provide the basic reporting/editing functionality that you are looking for.