SQL Server 2008 - Script Data as Insert Statements from 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 within a SSIS package?

Here's what I'm trying to accomplish...

I have a scheduled job that nightly scripts out all the schema and data for a SQL Server 2008 database and then uses the script to create a "mirror copy" SQLCE 3.5 database. I've been using Narayana Vyas Kondreddi's sp_generate_inserts stored procedure to accomplish this, but it has problems with some datatypes and greater-than-4K columns (holdovers from SQL Server 2000 days).

The Script Data function looks like it could solve my problems, if only I could automate it.

Any suggestions?

Answer 1

It is possible to automate the scripting of all a table's data as Insert statements by using SMO's Scripter.EnumScript method. Here is the code that I came up with...

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

A little too late to help me, I also found some other people who discussed the same challenge at http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/f456a535-f7c1-49f7-90f4-6a8bf251fd3b.

Answer 2

Use my Export2SqlCE command line utility, which scripts both data and schema in SQL Compact compatible script - http://exportsqlce.codeplex.com - SMO does not support SQL Compact syntax and data type conversion.