Hardcode the column values using SSIS

Question

Hi,

I have a couple of tables in my database. One of them has 7 columns and the other has 16 columns. The data types and columns match for the 7 columns of table1 and hence per my requirement I need to move the values of the 7 columns of table1 to table2. But for the other 9 columns in table2 I need to hardcode them. Can someone point me to right direction on how to get this done?

Thanks much for help in advance.

Answer 1

Does each of the 9 columns require a different value for each row?

---

Carla Sabotta

Answer 2

Hi,

you don't need to map all columns to the destination just map your desire columns to the appropriate columns of table. as you can see in the following snapshot that only related attributes are map and remains are un mapped.

thanks,

Zaim Raza.

Answer 3

Does each of the 9 columns require a different value for each row?

---

Carla Sabotta

Yes Carla.

Answer 4

Hi,

you don't need to map all columns to the destination just map your desire columns to the appropriate columns of table. as you can see in the following snapshot that only related attributes are map and remains are un mapped.

thanks,

Zaim Raza.

That works well. But I want to assign hardcoded values to the other columns. All the columns a unique value. And these unique values will be used through out the rows.

Answer 5

Ok.  You could use a Derived Column (http://msdn.microsoft.com/en-us/library/ms141069) to add values to the existing 9 columns.  But you'll need to add logic (via an SSIS expression) to specify the different values for each row.  For example, you could use a conditional statement to specify the column value for a given row.

You could also use the OLE DB Destination or the SQL Server Destination to run an UPDATE statement with the SET argument to update values in a single column.  Again, you need to specify which row or rows gets the column value (WHERE clause).  For examples, see the "UPDATE (Transact-SQL)" topic in BOL.  The SQL Server Destination supports bulk data load but does have some limitations.  For more information about both, see SQL Server Destination (http://msdn.microsoft.com/en-us/library/ms141095) and OLE DB Destination (http://msdn.microsoft.com/en-us/library/ms141237) in BOL. Another option is adding the columns outside of the Data Flow, via the Execute SQL Task (http://msdn.microsoft.com/en-us/library/ms141003).

---

Carla Sabotta

Answer 6

the best of way to use the hardcode value in the sql statement like:

select a, b, "hardcode value" AS D from table