18

In SQL Server 2005, Import Data (SSIS), my desire is to import a text file and have it append to an existing table. The first time through the wizard on the Column Mappings step I swear the Append rows to the destination table radio button was enabled. But, now, it's disabled (grey) and even re-starting the wizard won't cause it to re-enable.

Is there some secret/magic I don't know about? How do I get that option to re-enable so that I can append (rather than Create destination table)?

Pete Alvin
  • 4,646
  • 9
  • 39
  • 56

4 Answers4

33

I know what you're missing... because I once missed it too!

From the screen, "Select Source Tables and Views",
there is a "destination" drop-down menu on the right side.

Select your table from this drop-down,
then click on the "Edit Mappings" button.

Now the append option will be enabled!

David
  • 72,686
  • 18
  • 132
  • 173
Indy
  • 331
  • 1
  • 3
  • 2
  • 4
    Just what I was missing too. Thanks! – anton.burger Oct 15 '12 at 09:28
  • Same issue here. I suppose one idea, a little crazy but hear me out. What if, it's a big IF, the tool had a check if the destination has a matching schema.table as the source and it pre-populates the destination with the matching value. Nah nvm. Stupid – DontFretBrett Oct 09 '20 at 16:11
  • This is hilarious. Microsoft should have added a drop down arrow to indicate such a field...I didn't realize it was a dropdown. Thanks! – akuma6099 Oct 12 '22 at 17:15
11

In my experience, if the table you're importing into doesn't exist at the time you're going through the "SSIS Import and Export Wizard", you won't have the option to Append; if the table does exist at the time you're going through the wizard, the option is available.

If you do choose the "Create table" option, the wizard just builds the SSIS package with an Execute SQL task that builds the table prior to the data flow that loads the data. So, you can always just remove the task in the resulting package and you'll get "Append" by default.

Patrick Marchand
  • 3,405
  • 21
  • 13
  • 1
    This was the clue! In my case the table names had different cases. – jlo-gmail Dec 07 '15 at 23:11
  • Thanks! This was my issue. "the table you're importing into doesn't exist at the time". I didn't refresh sql server after I used code first migrations with EF6 so my tables didn't exist yet. – CareTaker22 Oct 17 '16 at 10:28
  • My mistake was that I typed only the table name, but had to select the full name from the dropdown instead, so `dbo.` etc. Now it works :) – Adam Aug 18 '22 at 18:53
2

The name of the table must be preceded with schema name ("dbo."). Otherwise the wizard didn't find it in my case.

Ondra
  • 21
  • 1
0

The trick to have that option is to open it from SSMS, right click on the database then choose export data. It was not grayed out when I opened it that way.

James
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 30 '23 at 00:13