Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

SSIS Package I need an exact copy of 7 tables each night

I have an SSIS package that I used to copy some tables from one server to another.  I need this to update each night.  I tried to create a new package with the same name (which overwrote the first) and when I was almost done, it said that rows would be appended to the table.  I don't want anything appended, I just need an exact copy.

I already thought that this was done via the SSIS package, and I really can't tell the client that I need to do something else.  I would really just like to fix the package or do a new one.

Someone mentioned that there was an overwrite option, but I don't see it.

Also, will it be a problem if the tables already exist? When I started, I had a blank DB.

Thanks!
0
jackjohnson44
Asked:
jackjohnson44
  • 4
  • 3
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
When creating the Import in through the SQL Server Import and Export Wizard, then once you get to the Select Source Tables and Views dialog there is a button at the bottom called "Edit Mappings".  If you click on that, you will be presented with the Column Mappings dialog that has options of "Delete rows ..." or "Append rows ...".  If you are doing this through SSIS scripting, then I would advise using TRUNCATE table in a SQL script and then just do your transfer of data then append won't matter as you will have already cleared data from table already leaving just structure.  If you have an identity field, you will just need to ensure that identity insert is on.
0
 
Kevin CrossChief Technology OfficerCommented:
0
 
jackjohnson44Author Commented:
Thanks, I will check out that link.  I am just using the SQL Server Management Studio import wizard.  I do have views which I created on this side which will need to remain.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Alpesh PatelAssistant ConsultantCommented:
Please in SSIS, drop that object and create new using maintenance tasks.
0
 
Kevin CrossChief Technology OfficerCommented:
That is an option also when creating the original package through Import Wizard.  It is on the right-hand side of the dialog I spoke of next to the 'Delete rows..." option.
0
 
jackjohnson44Author Commented:
I found that option and it looks like it will work.

Patel, I am not following, can you please explain?
0
 
jackjohnson44Author Commented:
How can I schedule that this package be run?
0
 
Kevin CrossChief Technology OfficerCommented:
You can use SQL Agent to schedule a SSIS step to run automatically.
http://msdn.microsoft.com/en-us/library/ms141701.aspx

I don't do this a lot because of old habits.  It is why I said linked server in the other question as that was what I used to do, though probably could have done the same with DTS but just liked writing the SQL myself. *laughing*
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now