Solved

Exporting to Excel from SQL 7

Posted on 1998-12-23
3
186 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 setup several different housekeeping processes for a SQL Server.

763 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

11 Experts available now in Live!

Get 1:1 Help Now