.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How can i generate a huge xml from SSIS?

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


I'm trying to generate an XML file through SSIS and i'm following the below mentioned steps to generate it:

In SQL Server:

1.  I created a proc called “spGenerateXML” that contains my SELECT FOR XML statement.


1. I created a Package level string variable called "XMLVariable"

2. In Connection Manager, I created a New .Net Provider/SQL Client Data Provider Connection called "ADODB"

3. In Connection Manager, I created a New File Connection called "XMLFile"

4. On Control Flow tab

  a) Added Execute SQL Task

  b) Added Script Task

  c) Connected Execute SQL Task (parent) to Script Task (child)

5. Edited Execute SQL Task, General section

  a) Changed Result Set to "XML"

  b) Set Connection to "ADODB"

  c) Set SQLStatement to "EXEC spGenerateXML"

6. Edited Execute SQL Task, Result Set section

  a) Clicked Add button

  b) Set Result Name to 0

  c) Set Variable Name to "User::XMLVariable"

7. Edited Script Task, Script Section

  a) Set ReadOnlyVariables to "XMLVariable"

  b) Click Design Script button

  c) Replaced "Main" Subroutine with:


View Complete Post

More Related Resource Links

SSIS package failed for huge data



I have created one package to archive database. The target database is around 400 GB data and source db is around 300 GB. I have the Lookup control to check the duplicates in the data. The package is working fine with small amount of data. but it is giving the errors when I am trying to archive some 400 GB of data. Getting the below errors while running the package

1)Communication link failure
2)TCP Provider: An existing connection was forcibly closed by the remote host
3)OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties
4)failed the pre-execute phase and returned error code 0xC020824E.

Appreciating your help in advance


SSIS 2005 - Dynamically generate create table statement from source



I have a requirement where i had to migrate the table structure and data from ODBC database to SQL Server. I have dynamic source reader that uses sql query from variable to get the data and the next step is to get the create statement and create table and then to insert data. how can i get Create Statement the destination produces and use sql task to create the table and then insert the data using data flow task.

Please suggest



How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

Extract huge amount of information from production system using ssis


Environment - Production System - SQL 2008(2000 compatibility)
                      BI System  - SQL 2008 R2

We are currently extracting data from a production system on a nightly basis.
One of the tables contains massive amount of information. We extract a 90 day window and the method we are using is extract to raw file and then we import the raw file to a staging database.

The tables contains +- 360 columns which is all used in the BI Warehouse.

The raw file amounts to about 9 Gigs of data.

This extraction process takes from 2:30 to 4:00 hours.  This is almost halve the evening and the ETL process is starting to take to long.

We recently upgraded the hardware so lack of hardware power is not the issue.

Is there another way of moving the data accross which will be more efficient and take less time?

Any assistance would be appreciated.


How to generate an XML file from SSIS (based on an XSD)?


I have an XSD that describes an XML format. I also have a sample XML file as well from the XSD. I am trying to understand SSIS's capability to generate the XML file in an ETL process. I would need to create the XML file based on the XSD that I have. It is safe to assume that all the data elements are in the DB that I am incorporating in the ETL and it is also safe to assume that the data in the XML may be from multiple different tables (so massaging is probably necessary). 

Would this be a job for C#/VB or can I do this through an SSIS process/task? Please help me understand my options. Thanks.

Generate thumbnails on the fly

There are many posts how to generate thumbnails using ASP.NET. Some good some less. Be sure to get distance from the versions that use Image.GetThumbnailImage method.

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Generate Random Numbers

SQL Server has a built-in function that generates a random number, the RAND() mathematical function. The RAND math function returns a random float value from 0 through 1.

huge number of data from database ... so how to minimize load time


Hi ...

I have used the above method to configure my crystal report ...

Its working fine , But i have huge number of data in database ....

So , it take long time to load the report ...

So , i have decided to show only the last 50 records inserted into the database , not all the 500 records to be loaded every time.

Plz help me with this issue  ...


Also , Plz explain me abt index legend in crystal report.

Pattern for huge logic



I am looking for a pattern/way to code for implement Huge logical hierarchy, Basically this is to verify person details like qualification, experience, expertised-in and global region.... and issue certain access for a perion of time.

The verification process has huge number of conditions it took around 1 year to document. And now we have to develop C# to implement it, can any one help me...? a sample code will be appreciated.

Thank you


Generate rss feed with HTTPHandler





I'm looking for link/tutorials/code samples on How to create HttpHandler for generating rss feeds for a website.




ASP.NET Designer - How Do I generate this Design-Time Markup?


I want to generate this:
    <x:MyControl id="a" runat="server">
            <x:MyScript .. />

How to generate large pdf's?


Hi all,

I am using rdlc reports in my appln. Am trying to generate a huge pdf file which almost consists of 1500 - 2000 pages.
I have used ReportViewer and am adding datasource (Generic collection) to report. But am getting system out of Memory exception as it is a huge file.
Can any one suggest me how to generate large pdf files in asp.net c#

Toolbox: Generate Office Documents, Monitor Event Logs, and More


Most data-driven Web sites are used as interfaces to collect, process, and summarize information. Reports that summarize the data can be presented to the user in a variety of formats-the most common way is to display the report directly in a Web page.

Scott Mitchell

MSDN Magazine June 2006

UI on the Fly: Use the .NET Framework to Generate and Execute Custom Controls at Run Time


Creating UI controls on the fly can be accomplished via run-time code generation. And there are lots of reasons to do so. Generating these controls once and then reusing them as needed is more efficient than generating the controls each time. Read on.

Morgan Skinner

MSDN Magazine December 2005

Stay Alert: Use Managed Code To Generate A Secure Audit Trail


In today's security-conscious environments, a reliable audit trail is a valuable forensic tool The Windows Server 2003 operating system provides features that let you enable a wide range of applications to make use of auditing functionality. This article looks at auditing from the operating system perspective and describes a sample managed code implementation that will allow you to add auditing to your own server applications.

Mark Novak

MSDN Magazine October 2005

DCOM Interop: Generate Custom Managed C++ Wrappers for Easier COM Interoperation Using DCOMSuds


Now that you're writing managed code, you'll certainly want to use your existing COM components, but you can't simply call them directly. Instead, you have to wrap the COM component in a runtime-callable wrapper that acts as a proxy between the component and your managed code. While the CLR provides wrapper classes for this purpose, there will be times when you'll want custom objects to wrap your COM components. One way to get the low-level access you need to precisely control resource cleanup, pass security information, and get access to CLR features is to write your own wrapper class in managed C++. This article shows you how.

Vishwas Lele

MSDN Magazine August 2003

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend