Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exporting to Excel from SQL 7

Posted on 1998-12-23
3
Medium Priority
?
199 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 80 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

877 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