Solved

SQL SSIS data import

Posted on 2013-02-05
9
428 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
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 …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

9 Experts available now in Live!

Get 1:1 Help Now