Solved

Exporting to Excel from SQL 7

Posted on 1998-12-23
3
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

632 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