Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL SSIS data import

Posted on 2013-02-05
9
Medium Priority
?
439 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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

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.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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