Solved

CSV to new database and exsisting database

Posted on 2011-02-21
20
398 Views
Last Modified: 2013-11-10
Hi All,

I asked this in a previous question but never been able to figure it out.. i thought i had but it didnt work..

How do i define a new database and an exsisiting database inside of the same loop if im exporting about 100 CSV files...

These would need to be copied to the "New database" created dynamicllly by the loop (which i dont know how to do)

and to the exisisting one which i do know how to do.

Any Help would be appricated.

Many thanks

James
0
Comment
Question by:NeoAshura
  • 10
  • 9
20 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34946173
Are you talking about database (exsisiting database inside of the same loop) or tables
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34950486
Hi ewangoya, thanks for the reply im talking about an exsisting database table inside the same loop.

so it dynamiclly creates one first, and then adds to an exsisting database also (like a backup i suppose) many thanks.

James
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34951807
Hello,
Your question is not really clear? do you wanna CREATE a database using SSIS and name it using a variable / parameter mapping.  and set that part of your looping over the CSV files?

You can use "Excute SQL task" in the control flow   or  "OLE DB COMMAND"  inside the data flow-see attached

and the command should be    'Create Database @DatabaseName'


OR


declare @myName nvarchar(50)

declare @myCreate nvarchar(100)

SET @myName = 'Dbname'

Set @myCreate = 'Create Database ' + @mydate


execute sp_executesql @mycreate
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34963538
Hi huslayer thanks for the reply.. I can see any attached files?

what i wanted to do was To Create a Database to add the data too.... But also add the data to an exsisitng file.


So the data goes to The one being created.

And

the one already set up.

Thanks James
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34964124
That's easy, the hardest part is using the variables to create a dynamic name for the new DB, use a multicast to insert into 2 DBs

see attached.

please hit me again if you need more help and give me more details :)
a.jpg
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34964138
put all of that into a for each loop container
b.jpg
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34964146
heehee, I've put extension CVS instead of CSV !! lol
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34970307
sorry to be a royal pain in the arse here huslayer,

But im still confused as to where your code goes inside the loop or inside the ole db command or inside the multicast? could you screen shot where it goes? and ill award you the points. it must be said this is by far the most help ive had from someone regarding SSIS so i am truly grateful for your time
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34970874
Heeheee, you're welcome, I'm so happy to hear that :)
Anyway....let me give you more detailed steps..

let me ask you think, the DBs that you'll create, what name will you use for it?

also what about the tables inside that databases? how will you create it?

0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34971096
Database name would be something like New_JamesDatabase

and the table names something like New_Customers and New_details.

The tables would be created from the excel CSV file..

The CSV comes in and needs to go to 2 Databases One Already present "JamesDatabase located on localhost for testing purposes" and Also for it to create a new Database called New_JamesDatabase also on local host... If this is not possible then Just createing New tables inside of the same Database would also be useful.

Many thanks for your help again.

James
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34976285
download this ZIP file, it contains the whole files you need

the password for the SSIS package is   -->    123

I assume that the files are in c:\test\   you may change that and the connection strings.

all the connections are pointing to localhost, integrated security

feel free to ask me for any clarifications.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34979447
there is one question why cant i edit anything on it? is it protected or something? if so how do i take it off?
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34979604
yep the password for the SSIS package is   -->    123 a
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34980315
One last question and the points are all yours or i can open a new question up to you...

I now know how to make two database with one set of data but what if i had one set of data and wanted to create two tables in the same database with the same data?

I.e

DB_James

But with

Table_James
Table_James_New

Also how do u create the split in the multicast? sorry im really new to this and trying to learn

James
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34980338
worked out the split in the multicast but still unsure about multiple tables.

James
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 34981458
Well I really wouldn't copy into 2 tables or even 2 databases, if you care about performance.
at the end you can use another execute SQL task and copy an entire table to another table. instead of inserting into 2 tables.

 copy task code to copy table
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34981480
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34981503
thanks you have been most helpful, deffinatly the most help ive got. I will award you the points. Many thanks. im sure ill have more questions but will do a new question so u get more points :) Thanks again.. p.s do you have facebook or something and ill add you to update you on questions im stuck with or an email address?

Thanks James
0
 
LVL 6

Author Closing Comment

by:NeoAshura
ID: 34981509
Very helpful expert, Clear and quick to repsond. 10/10
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34981769
thank you so much for the grade, points and the nice words, that's really what keeps me helpping more people and motivating :)

yes you can shot me an e-mail at     huslayer@hotmail.com

also i've started a SQL blog, maybe you can be my first follower :)

http://asqlb.blogspot.com/


Thank you again James and I'm ready to help you anytime with your questions

Jason
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email about the whoisactive result 7 27
SQL Query Syntax Join 4 34
Serach for record in Microsoft SQL Management Studio 8 20
SSRS troubles 4 20
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now