Is it possible to use script data as insert statements from an SSIS package?

Question

SQL Server 2008 provides the ability to script data as insert statements using the Generate Scripts option in Management Studio. Is it possible to access the same functionality from the SSIS package?

Here is what I am trying to accomplish:

I have a scheduled task that runs at night and scripts the entire schema and data from a SQL Server 2008 database. Then it uses a script to create the mirror copySQLCE 3.5 database . I use the sp_generate_inserts stored procedure to accomplish this, but it has problems with multiple data types and also has problems processing more than 4,000 columns (excerpts from SQL Server 2,000 days).

The script Data function looks like it can solve my problems, if only I can automate it.

Any suggestions?

Answer 1

Use my Export2SqlCE command-line utility , which runs both data and a schema in a compatible SQL Compact script, SMO does not support SQL Compact syntax and data type conversion.

Answer 2

You can automate the scripting of all tabular data as Insert statements using the SMO method . Here is the code I came up with. Scripter.EnumScript

Using conn As New SqlConnection(SqlServerConnectionString)
    Dim smoConn As New ServerConnection(conn)
    Dim smoServer As New Server(smoConn)
    Dim smoDatabase As Database = smoServer.Databases(smoConn.DatabaseName)
    Dim smoTables As SqlSmoObject() = New SqlSmoObject(2) {smoDatabase.Tables("Employee"), _
                                                           smoDatabase.Tables("Company"), _
                                                           smoDatabase.Tables("Job")}

    Dim smoScripter As New Scripter(smoServer)
    With smoScripter.Options
        .ScriptData = True
        .ScriptSchema = False
        .IncludeDatabaseContext = False
        .EnforceScriptingOptions = True
        .SchemaQualify = False
    End With

    Dim outputScript As New StringBuilder()
    For Each script As String In smoScripter.EnumScript(smoTables)
        outputScript.Append(script)
    Next

    Return outputScript.ToString()
End Using

Too late to help me, I also found other people who discussed the same issue on MSDN forums:

ScriptData script