CODEWITHSUNDEEP
sql-server-2008sql-server-cessis
user338390
user338390

Reputation:

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 Script Data function looks like it could solve my problems, if only I could automate it.

Any suggestions?

Upvotes: 3

Views: 3148

Answers (2)

user338390
user338390

Reputation:

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

Upvotes: 3

ErikEJ
ErikEJ

Reputation: 41769

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.

Upvotes: 2

Related Questions