May 7, 2002 at 2:56 pm
Hello,
I have a database consisting of around 60 tables that are all based on eachother. The whole relationship thing.
Anyway, I need to create an excel file every evening of the entire database. That is easy enough when it doesn't matter what data I grab. The problem occurs when I am told to only grab approved data.
You see when a record is edited the content is flagged as unapproved. I only want a copy of approved data. But how am I going to only copy approved data when not every table has an approved or unapproved field. I have to somehow draw the relationship in the transformation.
Is this what I need to do?
Is it possible?
Can you give me a direction so that I can actually get this done? (i.e. look into a custom transformation using C++)
Thanks,
Daniel
May 8, 2002 at 5:22 am
Look at writing a query that ties the data together for approved items only. You can use a query as your source in a transformation. This also will mean multiple queries if the data is stored on different worksheets in the xls file. But if you know your realtionships it should be fairly simple.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 10:41 am
quote:
Look at writing a query that ties the data together for approved items only. You can use a query as your source in a transformation. This also will mean multiple queries if the data is stored on different worksheets in the xls file. But if you know your realtionships it should be fairly simple."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
I went in and saw how to create an .xls file from a query and that will definitely solve the problem.
However I would like to make this as tight as possible, meaning I would like to work with one .xls file (numerous sheets) and ideally only one package to schedule.
Upon looking at it the task seems as if it is going to take a number of packages, because each one will need it's own query.
Is this true?
The thing that would help the most is knowing how to take your data and put it on the same .xls file but just on a different sheet.
How is that done?
Thanks,
Daniel
May 10, 2002 at 6:16 am
You can use a UNION between several SELECT
ex
SELECT a, b, c, d
from table1
where e='Y' (the validation flag)
UNION
SELECT asd a, grt b, ohsd c, ewh d
from table2
...
May 10, 2002 at 7:29 am
First no you will not need to have more than 1 package. The following steps should help you build a package for your needs and takes into account if the excel file is deleted from the expected location.
1) Create New Package
2) Add SQL Server Connection Item and define for the server in question.
3) Add Excel Connection item and set path to file.
4) Click SQL object, hold CTRL key and click Excel Object
5) On menu toolbar select Task and choose transform data task.
6) On the new task line double click for properties.
7) On the source tab which should be your SQL Server change to SQL Query and insert your query in the box.
8) Click the destination tab and a create destination dialog will appear, select the text and copy and put into notepad for safe keeping. (Note if you miss this step then after you close the dialog you can press the create button to get the dialog back).
9) Click the transformations tab and verify the transformations are as expected.
10) Add an "Execute SQL Task" which is the yellow icon with two red arrows making a circle.
11) Change Description to "Create[SheetNameForExcelHere]"
12) Set Existing Connection to Excel File connection.
13) Past Create statement we coppied into notepad into SQL statement box then press OK
14) Add another "Execute SQL Task" item
15) Change Description to "Delete[SheetNameForExcelHere]"
16) Set Existing Connection to Excel File connection.
17) Insert following "DELETE FROM [SheetNameForExcelHere]" in SQL Statement and press OK.
18) Select both Create And Delete items you created (note: Create first) then on toolbar choose Workflow "On Completion"
19) If this is the last sheet select Delete and SQL Server objects else loop thru from step 4 to create another sheet and select this Delete and the next Create object (note: Select delete object first). Then choose Workflow "On Success"
20) Now save you package and test.
21) Delete the file and test again to make sure gets regenerated.
Note: If the file exists then you will get errors for the Create items that is why we use "On Completion" for the workflow. This is ok we only worry if the Delete of those tables fail.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 7:37 am
Kowabunga that's an answer! Thanks for going the extra mile James!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply