Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Exporting to Excel from SQL 7

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 …
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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