Ssis 2012

SSIS and Import/Export
1

In sql server 2012, I want to take input from a user and load the data into a temp table. I then want to update several production tables with the data that has been supplied by the user.

Since this is my first time creating an SSIS, I have the following questions to ask you:

  1. The user wants to be able to enter the data in a Word document. Is that possible? If not, should I ask them to load the data into an excel spreadsheet so it is easier to obtain the data?

  2. To create the SSIS package, should I use the import wizard in sql server and save the package somehow? How would I save the package so that it can be executed again? If I do not use the import wizard, what do you suggest that I do?

Who you show me with pictures and/or explain to me, and/or point me to a link (url) that will tell me how to accomplish this goal?

2
  1. The data should be in Excel. Excel is a great file format for SSIS.
  2. You can use the import wizard to create it, but you'll need Visual Studio and the Data Tools (SSDT) to modify it. The user will need SSDT too, I believe.
3

Would you tell me why I would need both Visual Studio and the Data Tools (SSDT) to modify the package? Would you tell me what each tool is used for and the order of which tool is used first and which tool is used second?

4

Import/Export Wizard is sort of like a one-way tool. As you go through the wizard, it lets you pick where to get the data from, where to store the data etc. One of the last steps gives you the option to save it as a package. You can run this package at a later time without having to use the Wizard again. But you cannot edit that package using Import/Export Wizard. You can run the package with a few command-line options, but for the most part, if you want to do any serious change, you will need SSDT.

There are lot of tutorials on Import/Export Wizard, for example here

1 Like