Solved

SQL SSIS data import

Posted on 2013-02-05
9
435 Views
Last Modified: 2016-02-10
I need some help creating an SSIS process to import a large amount of data from an older server to a new one. what I would like to do is get a subset of data no older than a specific date to import, as well as all indexes that exist. is this possible using SSIS?
0
Comment
Question by:atorex
[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
  • 4
9 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38858406
"what I would like to do is get a subset of data no older than a specific date to import"

Sure, just use a Data Flow Transformation and put a WHERE clause in the source query.

"as well as all indexes that exist"

I wouldn't really put this in an SSIS package. But you can of course do that if you want.  If this is a one-off process, first transfer the data using a Data Flow Transformation.  Hook it up to an Execute SQL Task (in the Control Flow) that creates the indexes.  That way the index maintenance doesn't impact the data transfer speed, as they're created afterwards.
0
 

Author Comment

by:atorex
ID: 38859004
I'm not familiar with the process you describe, I'm looking for speed and ease of process so I would be interested in looking at what you propose, do you have any more details or how to processes?

Thanks
0
 

Author Comment

by:atorex
ID: 38859020
To be clear on my last update, I have accomplished the first part
"what I would like to do is get a subset of data no older than a specific date to import"

Sure, just use a Data Flow Transformation and put a WHERE clause in the source query.

the indexes are my real issue as when I imported (just a lab test so far) the data with the where clause I didn't get the indexes, perhaps I should have been more clear but was thinking there may be a way to do both at in one process. So I guess at this point the indexes are what I need help with.
regards,
Atorex
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 37

Expert Comment

by:ValentinoV
ID: 38859057
There are several options, here's on of them.  Open the Management Studio and connect to your original DB/table (the one with the indexes).  Open up the Object Explorer tree until you get to the list of indexes in your table.  Now right-click one of the indexes and select Script Index As > Create To > New Query Editor Window.

This will generate a CREATE INDEX statement that can be used to create the index on your new table. Repeat this for the other indexes.

These CREATE INDEX statements can be put in an Execute SQL Task in the Control Flow.  Make sure that you remove the "USE YourDB" line because the Execute SQL Task is already connected to a database through a connection manager.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38859063
Forgot to mention: if this is a one-off process which you don't need to repeat, you can also just execute the CREATE INDEX statements on your new database by using the Management Studio.
0
 

Author Comment

by:atorex
ID: 38859087
OK, I see what you are saying, I actually have a script that drops and creates all indexes in this database I guess I could just use that rather than importing ( there are several hundred indexes in the database a manual process not ideal) however the essence of your option is understood.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 38859200
That would indeed come down to the same, though be careful not to drop any indexes on tables that haven't been impacted by your migration process.  It would just be a waste of resources. (Well, assuming the DB isn't being used by users at the moment that you're manipulating the indexes, otherwise the impact would of course be greater.)
0
 

Author Comment

by:atorex
ID: 38859204
True, the new DB will be off-line to users during the process, thanks again for the help.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38859359
Glad I could help, good luck with the project!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
point in time restore in SQL server 26 53
Assigning Database Principals to Database Roles 3 39
sql server major issue  need help 2 81
I am new to using JSON in SQL Server 2 57
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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