Solved

SQL SSIS data import

Posted on 2013-02-05
9
436 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

695 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