Solved

Exporting to Excel from SQL 7

Posted on 1998-12-23
3
192 Views
Last Modified: 2010-03-19
I'm using the DTS (Data Transformation Services) and have set up a package of the process; I'd like to pass in a date range via global variables which can be changed each month when the user runs this.  There is a "holding" place for this under properties for the local Data Transformation Package that I wrote - but the "New" button is greyed out and there is nothing in Books on line.  Does anyone know anything about this feature?

I'm also having a problem scheduling the process I wrote - I've scheduled it, the SQL server is running, but it doesn't export the data - has anyone worked with this?  
0
Comment
Question by:scarlett
3 Comments
 
LVL 7

Expert Comment

by:tchalkov
ID: 1092369
Right click ont the package and choose edit package. Then right click somewhere on the design area(the white area) and choose package properties. This is the same as if you choose package properties without editing the pacakage but new global variabale is not disabled now.

0
 

Author Comment

by:scarlett
ID: 1092370
So far so good - but still doesn't work.

In the non-global variable query I was declaring the two variables as datetime types, since that type isn't an option in the DTS global area I have defined them there as strings.  

This is my query that the package calls (I do not set the variable values in the query since I believe that the values will be set by editing the global variable area of DTS):

DECLARE @@mo_start char(10),
        @@mo_end   char(10)

/* dates are set via a global variable in the DTS */

SELECT DISTINCT t.ORG_ID, e.*
   FROM org_episodes AS e JOIN org_time_periods AS t
      ON (e.period_id = t.period_id)
   WHERE e.dis_date BETWEEN @@mo_start AND @@mo_end

In the global variable definition of the package I'm enclosing the values in single quotes.

All appears to go well - a new Excel file is created with the correct headers (SQL fields) - but no records are selected.

I'm having a problem scheduling the process I wrote - I've scheduled it, the SQL server is running, but it doesn't export any rows of data - has anyone worked with this?  

For the record, the query works fine when it is run outside of the DTS or when I hardcode the variable values into the DTS query by setting the variable values there.

 
0
 
LVL 3

Accepted Solution

by:
cognition earned 40 total points
ID: 1092371
Create another table, or use an exisitng lookup/variable table to hold the two dates for the range, and join with this table

SELECT DISTINCT t.ORG_ID, e.*
   FROM org_episodes AS e , org_time_periods t, Newtable n
   WHERE e.period_id = t.period_id
   WHERE e.dis_date BETWEEN n.mo_start AND n.mo_end

NewTable has one record and two fields. You could of course also put a condition on the Newtable and have more than one record.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 36
How to identify differences between 2 backup files? 7 33
SQL VIEW 7 24
Common Records between Sub Queries 4 14
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now