2

Objective: automate creating a table along with all its columns and data types given a SSIS source

My guess is:

1) Pointing Sources to a Destination to a SQL command

enter image description here

2) Using Select * into ... Problem is I don't know what the from equivalent of a source is

enter image description here

Alternative) Store results in Recordset and pass on to Execute SQL task. Problem then is how to access that result from execute sql task enter image description here

Vinh Ton
  • 107
  • 3
  • 10
  • It's not possible to do that. Does the oData schema regularly change, or do you just want to avoid having to manually create the target table – Nick.Mc Jan 30 '18 at 00:23
  • @Nick.McDermaid I want to avoid having to manually create the target table – Vinh Ton Jan 30 '18 at 07:20
  • What I would do: get the field list from somewhere, paste into excel and make a table creation script. You might also be able to use the import/export wizard to create your table. – Nick.Mc Jan 30 '18 at 07:55
  • After several experimentation and research, I'm leaning towards the conclusion that there is no getting around manually creating each column whether it is done through a [sql task](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/272ddd31-1812-422c-aad7-8671a4c7e0b2/what-is-the-equivalent-of-select-into-in-ssis) or a [script task](https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx) – Vinh Ton Jan 30 '18 at 14:58

2 Answers2

2

I think you should use a Recordset Destination to store data into an System.Object Variable, Then use a Script Task (starts after that Data Flow Task is executed) in which you will select the System.Object Variable as ReadOnly Variable. and you will write your own code to insert the Recordset to SQL using System.Data.SqlClient.SQLCommand Object

You can refer to one of these links

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thanks again @hadi. I stored the results in a recordset. Is it possible to pass that result directly to an Execute SQL task? see updated screenshot – Vinh Ton Jan 30 '18 at 07:22
  • You have to use script task to achieve this – Hadi Jan 30 '18 at 08:25
1

If you need just the structure of table use this trick

select top 0 * into NewTable from YourTable
Krismorte
  • 642
  • 7
  • 24