Extending BI studio

Hi,
I need to add a custom menu option say under the Tools menu in the BI studio for SSIS projects. Is it possible to extend BU studio using Visual studio addins, or Visual Studio SDK (VSIP).

The Visual studio addin works fine; however I want my custom menu option to show up only when I have a SSIS project open so that I can perfom my custom step whenever the user selects the menu option

Thanks,
-SuriYou can extend BIDS with standard Visual Studio 2005 add-ins, because it is Visual Studio 2005. If you look at the Help menu, About, you will see it is the full VS IDE. The "Installed Products" list just shows different items depending on if you have installed through SQL Server tools only, VS only, or both SQL & VS together.|||Hi Darren,
The real problem I am facing is that the Visual Studio 2005 addin wizard allows you to specify the contextGUIDS which are defined in the vsContextGuids class. This class has guids defined only for Visual Basic, C# and J# type projects. I am not sure how I can specify a SSIS project using the vsContextGuids class.

-Suri|||Sorry, haven't got that far myself yet, but I look forward to someone else answering as I'm sure I'll need to know.

Just out of interest what does your SSIS specific add-in do?|||In the custom action, I need to load the package, parse it for some specific information, and write that information to a xml file (basically filtered package information) which can be then read and processed by another app we have.

Thanks|||

I'm not positive if this is what you're after, but I have an SSIS deployment add-in that we use to allow developers to deploy packages to a server. We use the following code to determine what packages are in the current solution, it may help? This sample builds nodes on a treeview for each project/package found in the current solution.

Private Const ssisProjectKind As String = "{d183a3d8-5fd8-494b-b014-37f57b35e655}"

If _applicationObject.Solution.Count = 0 Then
Return "Not valid without an open solution"
End If

Dim prjCount As Integer = 0
Dim pkgCount As Integer = 0

For Each proj As Project In _applicationObject.Solution.Projects
If Not proj.Kind = ssisProjectKind Then
Continue For
End If
prjCount += 1
Dim prjNode As TreeNode = Me.tvPackages.Nodes.Add(proj.Name)
For Each pi As ProjectItem In proj.ProjectItems
If pi.Name.EndsWith(".dtsx") Then
Dim pkgNode As TreeNode = prjNode.Nodes.Add(pi.Name)
pkgNode.Tag = pi.FileNames(0)
pkgCount += 1
End If
Next
Next

If prjCount = 0 Then
Return "No SSIS Projects were found in this solution"
End If

If pkgCount = 0 Then
Return "No SSIS Packages were found in this solution"
End If

Thanks!
Harry

Extending BI studio

Hi,
I need to add a custom menu option say under the Tools menu in the BI studio for SSIS projects. Is it possible to extend BU studio using Visual studio addins, or Visual Studio SDK (VSIP).

The Visual studio addin works fine; however I want my custom menu option to show up only when I have a SSIS project open so that I can perfom my custom step whenever the user selects the menu option

Thanks,
-SuriYou can extend BIDS with standard Visual Studio 2005 add-ins, because it is Visual Studio 2005. If you look at the Help menu, About, you will see it is the full VS IDE. The "Installed Products" list just shows different items depending on if you have installed through SQL Server tools only, VS only, or both SQL & VS together.|||Hi Darren,
The real problem I am facing is that the Visual Studio 2005 addin wizard allows you to specify the contextGUIDS which are defined in the vsContextGuids class. This class has guids defined only for Visual Basic, C# and J# type projects. I am not sure how I can specify a SSIS project using the vsContextGuids class.

-Suri
|||Sorry, haven't got that far myself yet, but I look forward to someone else answering as I'm sure I'll need to know.

Just out of interest what does your SSIS specific add-in do?|||In the custom action, I need to load the package, parse it for some specific information, and write that information to a xml file (basically filtered package information) which can be then read and processed by another app we have.

Thanks
|||

I'm not positive if this is what you're after, but I have an SSIS deployment add-in that we use to allow developers to deploy packages to a server. We use the following code to determine what packages are in the current solution, it may help? This sample builds nodes on a treeview for each project/package found in the current solution.

Private Const ssisProjectKind As String = "{d183a3d8-5fd8-494b-b014-37f57b35e655}"

If _applicationObject.Solution.Count = 0 Then
Return "Not valid without an open solution"
End If

Dim prjCount As Integer = 0
Dim pkgCount As Integer = 0

For Each proj As Project In _applicationObject.Solution.Projects
If Not proj.Kind = ssisProjectKind Then
Continue For
End If
prjCount += 1
Dim prjNode As TreeNode = Me.tvPackages.Nodes.Add(proj.Name)
For Each pi As ProjectItem In proj.ProjectItems
If pi.Name.EndsWith(".dtsx") Then
Dim pkgNode As TreeNode = prjNode.Nodes.Add(pi.Name)
pkgNode.Tag = pi.FileNames(0)
pkgCount += 1
End If
Next
Next

If prjCount = 0 Then
Return "No SSIS Projects were found in this solution"
End If

If pkgCount = 0 Then
Return "No SSIS Packages were found in this solution"
End If

Thanks!
Harry

Expression Syntax Help

Does anyone know of a good reference for Expression Syntax? I have
years of experience in SQL using all kinds of tools, but this thing
has got me really flustered. No If..Else, just endless nested Iif's
in Expressions? No Case..When? No Count Distinct? Can I fake a
Running Total?
This thing is killing me!
JodyPlease refer to the following resources for more information:
Expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
Functions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
In particular SQL Server 2000 Reporting Services does have a CountDistinct
and RunningValue functions.
SQL Server 2000 Reporting Services uses VB .NET expression syntax which
gives access to a large number of functions that can be used in reports.
Switch syntax is support in places of nested ifs.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408120855.1266d521@.posting.google.com...
> Does anyone know of a good reference for Expression Syntax? I have
> years of experience in SQL using all kinds of tools, but this thing
> has got me really flustered. No If..Else, just endless nested Iif's
> in Expressions? No Case..When? No Count Distinct? Can I fake a
> Running Total?
> This thing is killing me!
> Jody|||BTW: a reference of available expression functions in the VB.NET runtime
library is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl...
> Please refer to the following resources for more information:
> Expressions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody
>|||Thanks Bruce...
That gets me some basic functions, but I have a ton of complex Crystal
formulas that need to be converted and I can't seem to get the 'iif'
syntax to act the way I want it to. I don't find any good
references/examples on MSDN.
Here's a Crystal example:
if {Orders.ShippedDate} = {?Report Period} then
if {Orders.RecordSource} = 'Siebel' then
if ({OrderItems.ProductTypeCode} = 'Delivery' and
Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
{OrderItems.ProductCode} startswith 'HC2-D-QS' or
{OrderItems.ProductCode} = 'HC2-PR093-PC005' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'NFOS' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'EPIC' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
else 0
else 0
How would you handle something like this? (And no cracks about the
data inconsistancy, it was that way when I got here and ain't gonna
change any time soon enough for this project)
My first several tries were pretty pathetic. They would run, but no
data came back.
Thanks,
Jody
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> Please refer to the following resources for more information:
> Expressions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody|||Three more options:
1. Transfer some of this logic to a stored procedure.
2. Consider using Switch():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
3. Combination of the above two.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> Thanks Bruce...
> That gets me some basic functions, but I have a ton of complex Crystal
> formulas that need to be converted and I can't seem to get the 'iif'
> syntax to act the way I want it to. I don't find any good
> references/examples on MSDN.
> Here's a Crystal example:
> if {Orders.ShippedDate} = {?Report Period} then
> if {Orders.RecordSource} = 'Siebel' then
> if ({OrderItems.ProductTypeCode} = 'Delivery' and
> Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'NFOS' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'EPIC' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> else 0
> else 0
> How would you handle something like this? (And no cracks about the
> data inconsistancy, it was that way when I got here and ain't gonna
> change any time soon enough for this project)
> My first several tries were pretty pathetic. They would run, but no
> data came back.
> Thanks,
> Jody
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> > Please refer to the following resources for more information:
> >
> > Expressions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> >
> > Functions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >
> > In particular SQL Server 2000 Reporting Services does have a
CountDistinct
> > and RunningValue functions.
> >
> >
> > SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> > gives access to a large number of functions that can be used in reports.
> > Switch syntax is support in places of nested ifs.
> >
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JodyT" <datagal@.msn.com> wrote in message
> > news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > > Does anyone know of a good reference for Expression Syntax? I have
> > > years of experience in SQL using all kinds of tools, but this thing
> > > has got me really flustered. No If..Else, just endless nested Iif's
> > > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > > Running Total?
> > >
> > > This thing is killing me!
> > >
> > > Jody|||Forgive me for being such a bonehead, but the .net stuff is all greek
to me. I've used many report tools in my day, but never seen anything
as unintuitive as this.
I created an SP for the code below, brought it in as a dataset, but I
can't see a way to combine the new SP dataset with my larger text type
query dataset in the same table object. So what good is it to me?
Are you saying I should create one huge SP for the whole dataset?
That sort of defeats my goal which was to use a standardized query and
report template, and vary the expressions by client (and a couple
other variables across the business) when necessary as I did in
Crystal. But the expression editor in RS doesn't seem to be robust
enough for the complexity of the original formulas.
Am I silly to try to make things this simple?
What are the books I have not teaching me? So much of the stuff in
the MSDN Library is so .net based, I find it very hard to decipher as
a newcomer.
Jody
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<ett6PeMgEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> Three more options:
> 1. Transfer some of this logic to a stored procedure.
> 2. Consider using Switch():
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
> 3. Combination of the above two.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> > Thanks Bruce...
> >
> > That gets me some basic functions, but I have a ton of complex Crystal
> > formulas that need to be converted and I can't seem to get the 'iif'
> > syntax to act the way I want it to. I don't find any good
> > references/examples on MSDN.
> >
> > Here's a Crystal example:
> >
> > if {Orders.ShippedDate} = {?Report Period} then
> > if {Orders.RecordSource} = 'Siebel' then
> > if ({OrderItems.ProductTypeCode} = 'Delivery' and
> > Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> > {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> > {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'NFOS' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'EPIC' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> > else 0
> > else 0
> >
> > How would you handle something like this? (And no cracks about the
> > data inconsistancy, it was that way when I got here and ain't gonna
> > change any time soon enough for this project)
> >
> > My first several tries were pretty pathetic. They would run, but no
> > data came back.
> >
> > Thanks,
> > Jody
> >
[SNIP]
> > > >
> > > > This thing is killing me!
> > > >
> > > > Jody

Express Edition and Report Builder

Hi all,
Little question for you all: does the SQL express (Advanced) edition come
with end user report builder tools? Will it be possible for an end user to
build reports itself?
Thanks in advance,
PeterNo, the semantic modelling tool which builds the report models used by
Report Builder is not included in SQL Server 2005 Express, even with
the "Advanced Services" add-on, which gives you some of Reporting
Services, just not all of it.
-Eric
On Thu, 17 Aug 2006 16:53:14 +0200, "Peter Bons" <joepie@.blakjsd.bl>
wrote:
>Hi all,
>
>Little question for you all: does the SQL express (Advanced) edition come
>with end user report builder tools? Will it be possible for an end user to
>build reports itself?
>Thanks in advance,
>Peter
>

Express 2005 Tools

Aren't there any tools like Management Studio available for SQL Server 2005
Express?
Thanks.
There will be a scaled down version of management studio available for download. No timeframe yet.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <msnews.microsoft.com> wrote in message news:%23WwKU9t4FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Aren't there any tools like Management Studio available for SQL Server 2005
> Express?
> Thanks.
>
|||Jay,
there is a CTP available for download
http://www.microsoft.com/downloads/d...displaylang=en
Jan D'Hondt
<Toto, I've got a feeling we're not in Kansas anymore.>
SQL database and .NET solutions
"Jay" wrote:

> Aren't there any tools like Management Studio available for SQL Server 2005
> Express?
> Thanks.
>
>

Express 2005 Tools

Aren't there any tools like Management Studio available for SQL Server 2005
Express?
Thanks.There will be a scaled down version of management studio available for downl
oad. No timeframe yet.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <msnews.microsoft.com> wrote in message news:%23WwKU9t4FHA.1276@.TK2MSFTNGP09.phx.gbl..
.
> Aren't there any tools like Management Studio available for SQL Server 200
5
> Express?
> Thanks.
>|||Jay,
there is a CTP available for download
http://www.microsoft.com/downloads/...&displaylang=en
Jan D'Hondt
<Toto, I've got a feeling we're not in Kansas anymore.>
SQL database and .NET solutions
"Jay" wrote:

> Aren't there any tools like Management Studio available for SQL Server 200
5
> Express?
> Thanks.
>
>

Express 2005 Tools

Aren't there any tools like Management Studio available for SQL Server 2005
Express?
Thanks.There will be a scaled down version of management studio available for download. No timeframe yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <msnews.microsoft.com> wrote in message news:%23WwKU9t4FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Aren't there any tools like Management Studio available for SQL Server 2005
> Express?
> Thanks.
>|||Jay,
there is a CTP available for download
http://www.microsoft.com/downloads/details.aspx?familyid=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en
--
Jan D'Hondt
<Toto, I've got a feeling we're not in Kansas anymore.>
SQL database and .NET solutions
"Jay" wrote:
> Aren't there any tools like Management Studio available for SQL Server 2005
> Express?
> Thanks.
>
>

Exporting Tools

Hi,

I'm looking how export a Cube of my MS Analyzer Services.
I made a cube from the Foodmart DB, and i nedd to share the cube with some mates.
We don't want to build the cube every time that we change the notebook where we work :)

thanks a lotYou can use backup/restore to do this.

Exporting sql data to specific cells

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Quote:

Originally Posted by Edgar

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Have you experimented with the MS Query option in Excel? Underneath "Data" + "Import External Data" + "New Database Query" you can connect to your SQL Server datasource and query for the values you are looking for. There are some limitations however (i.e. can't run stored procedures that take parameters directly) but seems to cover a lot of the things people want to do in Excel.

|||To export data from SQL Server table to Excel file, create an Excel
file named testing having the headers same as that of table columns and
use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Bala|||Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar|||Does the data need to be fetched each time the spreadsheet is
recalcuated?|||

Quote:

Originally Posted by Edgar

Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar

If you use the External Data option I described, then you can exactly what you are looking for.

In your example, you want to put a value into c10. You use the "Import External Data" feature to connect / build / write your SQL Query. As part of this process you'll tell Excel what cell you want the data populated into, so select c10 (note: a column header is returned so you either need to put the results into c9 so your data is in c10, or put the results on a different sheet or far removed cell of the same sheet and then make c10 = the cell that the data is actually returned to.

|||I have done this a couple of different ways depending on the situation.
Here are a few ideas that might be helpful:

If there are not very many cells that would get updated then I would
create a custom excel function within the workbook that connects to SQL
Server, retrieves the value and then puts it in the cell. Using this
approach, the cell can move around the spreadsheet and you don't have
to change any of the code. This can also get more flexible as you
could pass in variables to the function. Make sure to set the function
to "volatile" or it will not recalc each time the spreadsheet is
recalc'd. Also note that this is solution can get annoying to the user
as it slows the worksheet calculation since each time it is
recalculated, a connection and query has to be made to SQL Server.
This can be aided by creating a connection to SQL server when the
spreadsheet is opened. If that is the case then I would make sure to
ask the user if they want to connect, store the response, and build it
into an if statement so that the function does time out a call to the
database server each time.

If there are a lot of cells to update, then I would suggest adding a
custom dropdown menu to the standard excel menu that has a button to
recalculate the "special" cells that get data from SQL Server. This is
more efficient in that you don't need to create, query, and then close
a connection for each cell - this requires a little more work to set up
the menu. Another issue is finding the cells that need to be updated.
You could do this with your custom menu too and store the result which
works well. I have also used cell comments to flag cells to receive
data since the comments are stored in a list that is easy to iterate
through.

Perhaps another solution would be to use the msft wizard under the data
menu and create a new database query - this has many limitations but is
very easy to set up.

A few to think about anyway, hopefully one of those is useful...|||The data is updated on a weekly basis. I mentioned I have a scalar
value. But I think I can fetch one row with 3 columns. The cells I
want to update on the Excel are: C4, C7 and C10.
The integer values are used in the excel formulas.

So far, I've tried using a DTS with activex to test just one particular
cell. But the code was updating the cell below C4 which I can't figure
out why. Here is the ActiveX code I'm using:

Function Main()

Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.add
Set oSheet = newBook.Worksheets(1)

'Specify column names.
oSheet.Range("C4").Value = "D"

DTSGlobalVariables ("FileName").Value = "C:\MyExcel.xls"

With newBook
.SaveAs DTSGlobalVariables("FileName").Value
.Save
End With

appExcel.quit

'dynamically specify the destination Excel file

set oPackage = DTSGlobalVariables.parent

' connection 2 is to the Excel file

set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Again, thank you for all your input and/or directions.

Edgar J.|||Hi Edgar,

You haven't mentioned why this approach won't work or isn't appropriate, so I'll elaborate.

First, add a new worksheet to your workbook. Call it whatever you want...for now I'll refer to it as "scaler_sheet". Also, as an example, I'll pretend your current worksheet you are referring to (the one you want the data in) is called "live_sheet". Make sure "scaler_sheet" is the active sheet...

From the "Data" Menu, choose "Import External Data" + "New DB Query". This will open MS Query. Choose "New Data Source" and then "Ok". Give your datasource a name and choose the "SQL Server" driver. Choose "Connect". This should open the SQL Server login box. Enter your server name or IP, login id, password. Click on the "Option" button and set your default db. Click "Ok". You should be back at the "Create New Data Source" dialogue. Leave "Choose Default Table" blank. Choose "Ok" and you can now tell Excel to use your new datasource (highlight it and click "Ok"). This will open MS Query...from here you can choose tables (if you want) or write your SQL directly ("Cancel" out of the table selection box and then click on the "SQL" button in the tool bar).

Depending on how complex your SQL is, MS Query may complain that it can represent your query graphically (no big deal). When your query results are displayed, choose "File" + "Return Data to Microsoft Excel". You'll be prompted to choose the cell where you want the data returned (i.e. scaler_sheet!A1). The data will be displayed with the column headers in A1, B1, and C1 and the data in A2, B2, and C2 respectively. Now, from your description above, you said you need your SQL data to appear in C4, C7, and C10. So, make this sheet the active sheet, and then reference the data from the appropriate cells from scaler_sheet (i.e. live_sheet!C4 = scaler_sheet!A2...live_sheet!C7 = scaler_sheet!B2...and live_sheet!C10 = scaler_sheet!C2).

I believe you will find this will work very well for you, and the data need only be refreshed when you want it to be.

Keith