Insert and update in sql table with excel/csv data that has no unique column

Question

Good Day,

I am trying to create a package that automates excel/csv files data import into sql server. The excel data does not have any unique column, so I created an ID column in sql database that increments by 1. This process is perfect for insert process but how should I perform the update process?
Also any good video on XML package creation for changing excel file server location and ole db server location (moving the package from dev to qa to prod)

Thanks,
Ishaan Baliwada

Answer 1

Hi @Ishaan Baliwada ,

Staging table is nothing but a "table". But it is not your final or target table where you want your data to reside in the end. Stage tables are table which may contain the data temporarily before you move them into your target table.

You may refer to sql-server-staging-table-vs-temp-table.

how do we best manage changing data types in excel when loading data through SSIS

There are 2 different type of data type conversion:

Implicit conversion: Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

Explicit conversion: Explicit conversions use the CAST or CONVERT functions or other tools.

You have to choose which type of conversion you should go with based on the SSIS data types you are working with and what is the logic you have to implement within your data flow.

See ssis-data-types-change-from-the-advanced-editor-vs-data-conversion-transformations.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Ishaan Baliwada ,

To update the table:

1.Import excel/csv file to staging table

2.Create a Sql Task that updates your table from staging table

Moving SSIS packages from Dev Environment to Production Environment:

You may refer to moving-ssis-packages-from-dev-environment-to-production-environment.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.