USE SCHEMA TO CREATE TABLE INSERT DATA THEN CREATE EXCEL SPREDSHEET
Hi Experts,
I have a SQL Schema, Which i want to run using SSIS packages to insert the data into my database it includes all insert commands and is over 18,000 lines long so dont really want to copy it into here...
What im trying to do is get the package to run the SQL file (dont know which tool that is on the taskbar) create the database based on the schema given. (which includes syntax GO) and then create teh table and insert data..
Once its created i need it to extract 4 tables from table and insert these with the data into an EXCEL spredsheet is this possible?
Thank you.
I have a SQL Schema, Which i want to run using SSIS packages to insert the data into my database it includes all insert commands and is over 18,000 lines long so dont really want to copy it into here...
What im trying to do is get the package to run the SQL file (dont know which tool that is on the taskbar) create the database based on the schema given. (which includes syntax GO) and then create teh table and insert data..
Once its created i need it to extract 4 tables from table and insert these with the data into an EXCEL spredsheet is this possible?
Thank you.
ASKER
Hi thanks for your reply,
What im trying to do i should of explained this alot better, Is trying to extract the data from the SQL table, into a Excel spredsheet but say i have cars that are "RED" and "BLACK" i want to be able to add up all the RED cars and all the BLACK cars. so when it goes into the excel sheet it looks like
Colour BLACK RED
400 500
Rather than
Car1 Black
Car 2 Red
car 3 Black
etc u see what i mean?? how would i do that?
What im trying to do i should of explained this alot better, Is trying to extract the data from the SQL table, into a Excel spredsheet but say i have cars that are "RED" and "BLACK" i want to be able to add up all the RED cars and all the BLACK cars. so when it goes into the excel sheet it looks like
Colour BLACK RED
400 500
Rather than
Car1 Black
Car 2 Red
car 3 Black
etc u see what i mean?? how would i do that?
I see two options...
1. Set up your SQL so that it outputs the way you want it, if possible. So when you run your SQL in mgm studio the output is as you would like to see it in Excel. Then again use the dataflow-->ADO.NET Source-->Excel destination to output the data.
You would use the SQL Command in your ADO Source and input that SQL Statement.
2. Another option is to use scripting (c# or vb.net). A lot more complicated but gives you total control of how your data comes out. This you would do in a script task. I can send sample code if needed.
1. Set up your SQL so that it outputs the way you want it, if possible. So when you run your SQL in mgm studio the output is as you would like to see it in Excel. Then again use the dataflow-->ADO.NET Source-->Excel destination to output the data.
You would use the SQL Command in your ADO Source and input that SQL Statement.
2. Another option is to use scripting (c# or vb.net). A lot more complicated but gives you total control of how your data comes out. This you would do in a script task. I can send sample code if needed.
ASKER
If you would not mind that would be appricated, However as mentioned in 1. ive outputted the data to excel but when it in there it comes up like
Car1 RED
car 2 Black
car 3 Black
is there a way to change this so it would become
Colour Black RED
2 1
so it counts them rather than saying which cars are which colour. ideally need a histogram u see to show the information.
Many thanks again.
Car1 RED
car 2 Black
car 3 Black
is there a way to change this so it would become
Colour Black RED
2 1
so it counts them rather than saying which cars are which colour. ideally need a histogram u see to show the information.
Many thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so would these sub queries go as an "sql task" before exporting to excel sheet? something like
sql task -> data souce -> excel ?
sql task -> data souce -> excel ?
ASKER
p.s what are t1 t2 and t3? sorry to be a pain
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah i think i was being blonde, I should of just copied and pasted the SQL task "create table" part into an SQL task. And then did the excel data thing. i was just being dumb this day.. and forgot to close question cheers anyway.
You'll want to use the "Execute SQL Task." Open it up and change the SQLSourceType to file connection, create a new connection, and set to your SQL file. See link below (at bottom of page).
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx
>>Once its created i need it to extract 4 tables from table and insert these with the data into an EXCEL spredsheet is this possible?
Absolutely. Create a dataflow, select your source, and create the destination as an Excel file.