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

x
?
Solved

USE SCHEMA TO CREATE TABLE INSERT DATA THEN CREATE EXCEL SPREDSHEET

Posted on 2011-03-21
9
Medium Priority
?
420 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:NeoAshura
[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
  • 5
  • 3
9 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 35183409
If i'm understanding you correctly...

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.

0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35183697
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?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 35183787
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 6

Author Comment

by:NeoAshura
ID: 35183932
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.
0
 
LVL 16

Accepted Solution

by:
carsRST earned 1000 total points
ID: 35184022
My guess is you might start off with changing the SQL, so that it counts the data.  Try subqueries as fields.

I don't know your table but something like this...

select 'Colour',
(select count(cars) from <<table name>> t1 where colour = 'Black' and t1.<<field>> = t2.<<field>>) as Black,
(select count(cars) from <<table name>> t3 where colour = 'Red' and t3.<<field>> = t2.<<field>>) as Red

from <<table name>> t2 group by 'Colour'
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35185821
so would these sub queries go as an "sql task" before exporting to excel sheet? something like

sql task -> data souce -> excel ?
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35185829
p.s what are t1 t2 and t3? sorry to be a pain
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 1000 total points
ID: 35239532
Create table using execute script task. and in dataflow create file using Excel data destination or csv file using Flatfile destination.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35240257
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.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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