Executing packages in a specific sequence

I am facing some issues while working on SSIS in VS2005,

The Scenario is we have 35 excel files from which we have to import data

to Sql 2005.

We have to execute these packages in a specific sequence so we are not

using For Each Loop Control.

Now the Problem is this that we have 35 executables as an output that is

corresponding to each package, and from each executable we can Change

excel path and Sql Connection String.

The problem is that one has to open each executable to set the path; we

wanted to keep them Configurable like , as if we can have any variable

define in the packages for path, and in the packages we use that variable

and append the XLS file name to it like ..

@.PathVariable+ test.xls

And from the Configuration file at run time we can set the path to this

variable like

@.Path Variable="C: /Windows/ Test Folder/"

And then executable picks the path from there.

Is there any possibility to implement any thing similar, we have tried but

its not working, any help in this regard will be really appreciating.

Its really urgent, I will wait for feedback on this.

Yeah you can do that. just use an expression on the ConnectionStrig property of the connection managers. This should give you some clues:

SSIS Nugget: Dynamically set a logfile name
(http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx)

-Jamie

Executing a SPROC from a View

Is it possible to execute a SPROC from within a view? If so, will the query
slow the SPROC any lower than it normally would run?
I use excel pivot charts based on sql views a good bit. As far as I know, I
don't think pivot charts can be based on SPROC's. I've run into a database
that is very large and requires a good bit of formulas in the view which
slow it down.
I'm just looking for any way to speed my data call to my excel pivot chart.Theroretically yes. But pratically and perferable, no.
You *could* use the OPENQUERY method for that, but as I said I didn=B4t
make any good experiences with that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Scott (sbailey@.mileslumber.com) writes:
> Is it possible to execute a SPROC from within a view? If so, will the
> query slow the SPROC any lower than it normally would run?
> I use excel pivot charts based on sql views a good bit. As far as I
> know, I don't think pivot charts can be based on SPROC's. I've run into
> a database that is very large and requires a good bit of formulas in the
> view which slow it down.
> I'm just looking for any way to speed my data call to my excel pivot
> chart.
You can use OPENQUERY that Jens mention, but it a serious kludge, and
it is not likely to speed anything else up.
Sorry, without any more information about your real problem, it's
difficult to say anything more.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Execute Excel macro when exporting in SSRS

We have a report that users typically export to Excel. I've created
a
macro in Excel that I run against the the exported report to do some
follow up work. Unfortunately, this requires that I export the
report
myself, run the macro and forward on to those interested.
I'm looking for a way to execute the macro automatically every time
someone exports to Excel (or sets up a subscription that sends Excel
output). Any ideas?
If there is a way to do this with a custom DLL, etc. we would be
interested in hiring a consultant to complete the task.
Thanks for you help
IANOn Oct 2, 8:48 pm, IAN <iani4pro...@.gmail.com> wrote:
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
> Thanks for you help
> IAN
In terms of exporting the report to excel via subscription, if you are
not aware, this is built into the Report Mgr. Also, a report can be
exported to Excel via URL. Here's an example:
http://ServerName/reportserver?/DirectoryNameWhereApplicable/ReportName&rs:Command=Render&Param1=ParamValue&rs:Format=Excel
This opens an export dialog that defaults to the Excel format. Another
option that can most likely be used in conjunction w/Excel
manipulations (known as Automation) is utilizing the web service
available w/SSRS and the Report Server. An example link is found here:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you're talking about using the standard interface, I'm pretty sure the
answer is no. It generates a new copy of the file and that's it. Of course,
you could always build your own aspx page that generates the xls file and
then uses the excel object model to do whatever you want. An option for
subscriptions would be to send the xls file to a share, and have a .net app
that is looking at that folder (filesystemwatcher) and runs whatever code
you need when a new xls file shows up in the directory.
Mike G.
"IAN" <iani4profit@.gmail.com> wrote in message
news:1191376082.251031.190910@.n39g2000hsh.googlegroups.com...
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
>
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
>
> Thanks for you help
>
> IAN
>

Execute Excel macro after export

We have a report that users typically export to Excel. I've created a
macro in Excel that I run against the the exported report to do some
follow up work. Unfortunately, this requires that I export the report
myself, run the macro and forward on to those interested.
I'm looking for a way to execute the macro automatically every time
someone exports to Excel (or sets up a subscription that sends Excel
output). Any ideas?
If there is a way to do this with a custom DLL, etc. we would be
interested in hiring a consultant to complete the task.
Thanks for you help
IANI'm currently working on a DLL to do exactly this. We execute a macro to
strip out hyperlinks because the excel renderer doesn't have an
OmitHyperlinks option.
Contact me if you are still interested.
Raul
"iani4profit@.gmail.com" wrote:
> We have a report that users typically export to Excel. I've created a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
> Thanks for you help
> IAN
>

Execute DTS package from ADO in VB

I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

What is the syntax to do this?

Here's my current database connection code:Option Explicit

Dim db_connection As ADODB.Connection
Dim db_results As ADODB.Recordset
Dim db_error As ADODB.Error

Private Sub DB_Initialize()
Set db_connection = New ADODB.Connection
db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
"Initial Catalog='my_db';Integrated Security='SSPI';"
Set db_results = New ADODB.Recordset
End SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||Originally posted by odinsdream
Is it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?

I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

server_name=server name dts package is on
user_name=login to access server
password=user's password
package_name = DTS package name
package_password=DTS package pwd

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

GO
-------

if your package does not have a pwd then your sp should look like this

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

GO|||So how would one execute that stored procedure from VBA for Access?