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

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

Extract huge amount of information from production system using ssis

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

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.


View Complete Post

More Related Resource Links

MMC: Designing TView, a System Information Viewer MMC Snap-in


Windows 2000 provides remote management tools running in the Microsoft Management Console (MMC), but if you're running Windows NT 4.0 you can create your own remote management tools by writing an MMC snap-in. This article walks through the development of a distributed process management utility, TView, which is similar to Process Viewer or Task Manager. The tool consists of an MMC snap-in, a COM+ component, and a kernel-mode driver. The DCOM interface, TView's access to system processes and information, and debugging of TView are discussed in detail.

Tom Boldt

MSDN Magazine December 2000

File System Deployment from DEVelopment to PRODuction

Looking for best practices for doing an SSIS File System deployment from a DEV environment to a PROD environment using either Team System or strictly file based.

Placing ..... configuration information in a config file other than app.config

Hi, I'm sorry if this question has been asked/answered before on this forum. I have searched both here and on the web in general, but to no avail as yet... I am running VS2010 and .Net 4, so in theory have all the latest versions of everything at my disposal. I have an executable a.exe. This downcalls into a dll b.dll. b.dll wants to send an MSMQ message using WCF. For cleanness (a.exe is a generic service and knows nothing about WCF) I want to define the client endpoint in b.dll.config. However I am finding that WCF is insisting that the information is held in a.exe.config. The exact problem is that when I create my proxy (i.e. the class which derives from ClientBase<T>), I get the message Could not find endpoint element with name 'XXX' and contract 'YYY' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this name could be found in the client element. From what I have seen I have just described an extremely common problem that people have had with WCF over the years. My questions is basically to find out, given that I have WCF 4 at my disposal, whether there is now a standard approach to this scenario? Things I have tried so far: The obvious one...putting all my config info in a.exe.config. When I do this, everything works file, so I

Development and Production Server software information for Sharepoint 2010 ??

Need to ask my IT Team to get me softwares for Development machines to write code / develop sites which we can give to end customers for deploying in their Sp2010 Production farm . W.r.t to that i have following questions : For Development , a) Which Windows Server 2008 OS version is sufficient - Standard , Enterprise , R2 ? b) Which SQL Server Version is sufficient ? c) Which VIsual studio version ? d) Which Sp2010 license version ?   From Production environment perspective , have following questions - a) What should be a recommendation to client for Production Server - Widows 2008Server sp2 or R2 ? SQL Server 2008 Server Sp2 or R2 ? Which is better and why ?  

I need guidance on a program that displays system information

I have been asked by my university's IT division to write a program that displays specific information about the computer that the program is running on, such as the OS version, IP address, domain name, chipset, any device drivers that are installed, and any software that is installed. I'm allowed to use whatever language I want, but since I only know Java and VB.Net, I would like to use one of them. I've been doing Google searches for two weeks trying to find any information on a built-in class that would do this, in either language, but thus far, I've been unable to find anything that fits what I'm looking for. What I'm hoping for from this is for someone to tell me about a class that displays this kind of information, or failing that, how I would go about building my own. Be as general as you feel you need to be; this is a class assignment, and I'm hoping to learn something from this. Thank you for your time.

SSIS 2008 generates Registration information for this application needs to be updated - VSTA.exe

I am getting the following error in SQL Server 2008 R2 (build 10.50.1734) x86 platform BI Development Studio (BIDS) 9.0.30729.4462 with .NET Framework 3.5 SP1 in a Integration Services project. I have a package with a Data Flow task that contains a Script Component. I edit it and attempt to edit script and I get the following error: Registration information for this application needs to be updated. To update, log on as an administrator and run this command: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\VSTA.exe /HostID SSIS_ScriptComponent /setup About a minute later, another box displays with the following message Cannot show Visual Studio Tools for Applications editor. Additional information: The VSTA designer failed to load. “System.Runtime.InteropServices.COMExeption (0x80004005) Error HRESULT E_FAIL has been returned from a call to a COM component at VSTADTAProvider.Interop.VSTADTEProiderClass.GetDTE(String bysHostID, Uint32 dwTimeout). I execute the command above running as an administrator (there are no output results). BIDS displays the same symptoms. This happens on a Windows 7 Enterprise (I said Professional in an earlier but un-related post) workstation x86 platform. I get the error even if I create a new script component in a new data flow task in a new package. I also have VS 2010 Premium Edition (build 10.0.30319.1 RTMRel) i

How to deploy and configure ssis packages using sql server table in testing server and production en

Hi We have three environments (development, testing and production ) I have to configure and deploy the ssis packages using sqlserver table in different environments(development, testing and production).   Three environments database structures  are same But database names are different like (DEVSERVER,SITSERVER,UATSERVER) We have 14 packages and one master package. In master package we called all 14 packages question1 First time i will deploy all (master and 14 packages). If i modify one package we need to deploy all the packages or single package deployment is enough    Please help me thanks in advance          

How to Extract specific information

Is there a way to extract only certain information with the use of javascript?If i have the following line of code<A href="Person.aspx?q=83537">Prince Bahawal () Abbas Abbasi</A> and I would like to extract 83537 part from that line, in a JavaScript function.is this possible?

SSIS Generic Error: The command execution has generated errors, more information?

How do I get more information from this error? I am just pulling from a csv file, looking up a couple values, and on match, updating a database with a stored procedure. I have made sure all datatypes match, and that there are no special characters in any of the records. I am importing over 3000 records, and only 4 fail, with this generic error, and I have scrutinized the data to the utmost. How do I get more information on this error? The lineageID is 0, what does that mean? In know it refers to a column, but 0 certainly doesnt relate to any columns. Thanks

SSIS package fails giving "System.Runtime.InteropServices.COMException" exception


I am trying to execute the package in SQL Server 2008 and i am getting this error.


Executed as user: sql\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    

Started:  11:07:09 AM  Error: 2010-09-20 11:07:10.95     Code: 0x00000000     Source: Transform Data Task  undefined      

Description: System.Runtime.InteropServices.COMException (0x80040428): Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.     at DTS.PackageClass.Execute()     at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()  End Error  DTExec: 

The package execution returned DTSER_FAILURE (1).  Started:  11:07:09 AM  Finished: 11:07:10 AM  Elapsed:  1.094 seconds.  The package execution failed.  The step failed.


Do any one have a clue


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


Deploying SSIS Package configurations in production and development


I'm working on a SSIS package which I want to deploy onto our development environment on a daily basis as I work on it. In addition, I want it to be easy to deploy to production. We're using xml configuration files for our packages, but I haven't seen anyone discuss the entire development process.

Do people keep their production and development configuration files in source control? If so, how? Are they part of your BIDS SSIS project? I tried adding them to "Miscellaneous". i.e. Example:

I have a project with the following SSIS Packages in it:




In the Miscellaneous folder I have:






I did this so I can keep them in source control. We don't use package deployment utilities, we just deploy by hand, but putting the files in Miscellaneous breaks the building of the deployment utilities because it attempts to copy the config files into the Deployment directory twice.


In addition, I set up the package configurations such that the

run system.dioganistic.process to open notepad not work on production server


Hi I have try to open notepad from local machine test code thats work fine, but when I try the code in production server my code not open the note pad.

this is the code I am using


I have change .netframework >> config >> machine.config file <processModel to username="System" and password"Autogenerate"

even though this is not working then i create a individual application pool for this application , and set identity tab Predefine value to "Network service" value to "local service" and then "local system" non of these not run the process from vb.net code.

how do I solve this. I have seen one article says need to change IIS  logon tab to allow interact with desktop, but i cannot find this feature in IIS.

anybody to help me please......


SSRS 2008 crashng when running a report with huge amount of data


We have a huge amount data that is loaded (using flat files, upto 80GB in size) from the mainframe on a daily basis after which our users use SSRS to view the results using predeployed reports. One of the report query brings back a huge amount of data but when rendering the reports, it dies and restarts the service.

library!ReportServer_0-12!1310!10/14/2010-17:49:45:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database., ;
session!ReportServer_0-12!1310!10/14/2010-17:49:45:: e ERROR: Sql Error in GetSnapshotData: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
ui!ReportManager_0-11!1b34!10/14/2010-17:49:45:: Unhandled exception: Microsoft.Reporting.WebForms.ReportServerException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the dat

File System Task Error in SSIS


Hi All,


I am trying to move some .txt files with in same location different folder.


Destination Filename=C:\Error Files\Specific Client


Source Filename=C:\Error Files


I used for each container in that I took file system task.

In file system task Destination variable= Destination Filename, sourcevariable= Source Filename, operation=move, issourcepathvarible=true, overwriterdestination=true


Execute PowerShell Script to get Cluster Information from SSIS - Am I crazy?



I created an few scripts that calls cmdlets that gets information from our clusters. The idea is to run the process once and get the information from our clusters into one single place. This is important after outage/patch weekends so we can check the state of our clusters in one single place.

I have never worked with PowerShell scripts before, and I'm doing this after getting inspired by some really clever folks I had the pleasure of seeing at SQL PASS.

I wanted to leverage SSIS so I can take advantage of the the ForEachLoop container and perform the same checks across my 13 clusters.

Have you guys done this before?
Am I on the right path?
Should I use WMI instead or PowerShell?
Any suggestions on how to execute PowerShell scripts from SSIS?
Any other suggestions?

--- Best Regards, Igor Santos - DBA igor.santos@citrix.com

Logging Information from Nested SSIS Packages


Hi All -

I have packages that contain other packages. I run my packages using dtexec. Currently I am doing my logging from the batch that calls dtexec. This does not seem to capture any information on the child packages, other than their call from the parent. Can someone suggest a good general resource on logging that includes advice on how to capture logging on nested packages?


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