How to create User Defined Data Types Columns (table) to another database in SSIS?

Question

Hello Everyone,

I have some tables in Production Database with User Defined Datatypes (uddt) columns. I want to create those tables (from script) into another database and then copy the data using SSIS. I am not able to create UDDT coulmns tables in the new database. Please help with solution.
Thanks All.

Answer 1

You need to script creation of these types first. Did you try that?

Answer 2

You either need to recreate the UDDTs or manually translate the data types into their root data type.

Answer 3

Hi @hamb ，

Do you mean that you want to copy the tables to another database with SSIS?

If I understand your requirement correctly, you do not need to create those tables previously.

You may use the Transfer SQL Server Objects Task to transfer tables with data in SSIS.

Also see the tutorial transfer-sql-server-tables-with-data-in-ssis for details.

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 4

Posting as answer as it didn't work as a comment.

If you're OK to re-do this (at least tables + indexes), then see https://www.c-sharpcorner.com/blogs/how-to-get-table-script-with-their-all-indexes-in-sql-server1

Alternatively I found https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/ but there are too many replies with corrections. I tried one solution which produced some weird output for me and I don't have time to debug why it's so:

SELECT 'CREATE ' +
         CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
         (i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS) +
         ' INDEX ' +
         QUOTENAME(i.[name]) +
         ' ON ' +
         QUOTENAME(schema_name(t.schema_id)) +
         '.' +
         QUOTENAME(t.[name]) +
         REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + CASE WHEN c.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END x
                                    FROM [sys].[index_columns] c
                                    WHERE c.[object_id] = i.[object_id] AND
                                          c.[index_id]  = i.[index_id]  AND
                                          c.[is_included_column] = 0
                                    ORDER BY c.[index_column_id]
                                    FOR XML PATH('')), '', ', '), '', ')'), '', '(') +
         COALESCE(' INCLUDE ' + REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + CASE WHEN c.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END x
                                                           FROM [sys].[index_columns] c
                                                           WHERE c.[object_id] = i.[object_id] AND
                                                                 c.[index_id]  = i.[index_id]  AND
                                                                 c.[is_included_column] = 1
                                                           ORDER BY c.[index_column_id]
                                                           FOR XML PATH('')), '', ', '), '', ')'), '', '('), '') +
         ' WITH (' + CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
         CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
         CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
         CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END +
         CASE WHEN i.[ignore_dup_key]   = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
         'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) + ') ON ' +
         QUOTENAME(FILEGROUP_NAME(i.data_space_id)) +
         ';',
       schema_name(t.schema_id) [schema_name],
       t.[name],
       i.[name],
       CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END,
       i.type_desc,
       CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
         CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
         CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
         CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END +
         CASE WHEN i.[ignore_dup_key]   = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
         'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) AS IndexOptions,
         i.is_disabled,
         FILEGROUP_NAME(i.data_space_id) FileGroupName
  FROM [sys].[tables]  t JOIN
       [sys].[indexes] i ON t.object_id = i.object_id
  WHERE i.[type] > 0 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 --AND schema_name(t.schema_id)= @SchemaName AND t.name=@TableName AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams'
  ORDER BY schema_name(t.schema_id),
           t.[name],
           i.[name]