Solved

SQL SSIS data import

Posted on 2013-02-05
9
429 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
  • 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
 
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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Concatenating multiple comments into one row 16 62
SQL Server - Check Constraint or NOT NULL? 11 69
Storage Spaces 3 39
Sql Query Datatype 2 19
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

861 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

22 Experts available now in Live!

Get 1:1 Help Now