SQL Server 2008 - Script Data as Insert Statements from SSIS Package

Posted by Brandon King on Stack Overflow See other posts from Stack Overflow or by Brandon King
Published on 2010-05-11T15:18:44Z Indexed on 2010/05/11 15:24 UTC
Read the original article Hit count: 264

Filed under:

ssis

|

ssis-data-tranformations

|

sql-server-2008

|

sqlce

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?

© Stack Overflow or respective owner

Related posts about ssis

SSIS packages incompatibilities between SSIS 2008 and SSIS 2008 R2

as seen on SQL Blog - Search for 'SQL Blog'
When you install SQL 2008 R2 workstation components you get a newer version of BIDS (BI Developer Studio, included in the workstation components) that replaces BIDS 2008 version (BIDS 2005 still live side-by-side). Everything would be good if you can use the newer version to edit any 2008 AND 2008R2… >>> More
Parsing flat files using SSIS : SSIS Nugget

as seen on SQL Blog - Search for 'SQL Blog'
Often when using SQL Server Integration Services (SSIS) you will find there is more than one way of accomplishing a task and that the most obvious method of doing so might not be the optimal one. In the video below I demonstrate this by way of an experiment using SSIS’s Flat File Source component;… >>> More
Introducing SSIS Reporting Pack for SQL Server code-named Denali

as seen on SQL Blog - Search for 'SQL Blog'
In recent blog posts I have introduced the new SSIS Catalog that is forthcoming in SQL Server Code-named Denali: What's new in SSIS in Denali Introduction to SSIS Projects in Denali Parameters in SSIS In Denali SSIS Server, Catalogs, Environments and Environment Variables in SSIS in Denali… >>> More
New SSIS tool on Codeplex – SSIS Log Analyzer

as seen on SQLIS - Search for 'SQLIS'
I stumbled across a new SSIS tool on Codeplex today, the SSIS Log Analyzer which was only released a few days ago. Whilst it is a beta release and currently only supports 2005 (2008 is promised) it looks quite interesting. It seems to be a fancy log viewer, but with some clever features and a nice… >>> More
New SSIS tool on Codeplex – SSIS Log Analyzer

as seen on SQLIS - Search for 'SQLIS'
I stumbled across a new SSIS tool on Codeplex today, the SSIS Log Analyzer which was only released a few days ago. Whilst it is a beta release and currently only supports 2005 (2008 is promised) it looks quite interesting. It seems to be a fancy log viewer, but with some clever features and a nice… >>> More

Related posts about ssis-data-tranformations

SSIS how to split a single record in to two different records?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a Product record that has multiple "zoned" prices, one for each store that sells the product. ProductID int Name string PriceA money PriceB money PriceC money In SQL Server Integration Services, I need to split this in to multiple records: ProductID int Version string //… >>> More
script component

as seen on Stack Overflow - Search for 'Stack Overflow'
what is the use of script component in Data Flow ? Is it same as Script Task in Control flow ? Any difference ? >>> More
Error in retrieving data from Excel File

as seen on Stack Overflow - Search for 'Stack Overflow'
I have an excel file. I wanted to pull the data from excel file to SQL Server table. And the data is successfully transferred.In the excel file, I removed a text from one column named "Risk" from one row.The text was lengthy one.now the package execution fails at the source ie from the excel file… >>> More
Script Task - File Manipulation

as seen on Stack Overflow - Search for 'Stack Overflow'
Using Script Task, I have written a code to create a folder and create a file of msword document inside the created folder. Its working in local machine. But its not working in another server, after I deployed it. The folder is created successfully, but the word document file is not created. for word… >>> More
How can we abort the post in ProcessInputRow() event in SQL SSIS Script Component?

as seen on Stack Overflow - Search for 'Stack Overflow'
How can we abort the table post in this event: public override void Input0_ProcessInputRow(Input0Buffer Row) ... based on an input table field condition in our SSIS Script Component? For example if(Row.BoolValue) AbortPost(); We don't want to abort the whole process just this one record post… >>> More