Is it possible to script data as Insert statements from within an SSIS package?

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 runs nightly and scripts out all the schema and data from an SQL Server 2008 database. It 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 few data types and also has issues with handling more than 4,000 columns (holdovers from SQL Server 2000 days).

The S开发者_如何学编程cript Data function looks like it could solve my problems, if only I could automate it.

Any suggestions?

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 the MSDN forums:

ScriptData doesn't script data

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