Load All Data Without Knowing Old One Was Load In The Previous Time???
Apr 27, 2007
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Here I will describe my problem. 1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance. 2. Now when we put the application on web farm garden, it is not able to load the application. 3. We are sending the request the servers through Router kind of application. 4 This application is working fine on single server enviornment.
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.
I have a CTE query that is used to fill in nulls on a history table. The With statement executes just fine. sub 2 seconds on 974 records, however the main query is what's turning the whole query into a turtle. I know that it's the looping that it's doing there that is causing the slow down, but I'm just not sure how to fix it. I've tried inserting it into a temp table, refactored the code a hundred times, but nothing seems to be working.
Code is below and the execution plan is attached. Server Version: 12.0.2342.0 Enterprise: 64bit
;WITH BuildTable AS ( SELECT [GEGTH].[ID] , [GEGTH].[Changed By] , CAST( [dbo].[GetWeekStarting] ([GEGTH].[Changed Date] , 2 ) AS DATE) AS WeekOf , [GEGT].[Title]
I have a Visual Studio 2005 web application Front End.
The front end is basically only used as a series of links to SQL Reports.
It seems that when you first click the links to access the reports, there is a slow response time to first load the report viewer. Then after a while of accessing the page, I suppose some of the components are cached so the page loads faster.
Is there a way to automatically cache the pages or any other way to decrease response time on the report viewers?
Edit: To clarify, it first takes around 10-15 seconds to load the .aspx that contains the report viewer. Then it takes around 5-10 seconds to load the report viewer and the report. Is there any way to minimize these times? It seems to speed up the more the page is accessed, but is there a sure-fire way?
My ASP.net project with database connection inside, I mean DataBound or whatever the name is make my website load slower because there's a delay about 8 til 9 seconds when in its first load from localhost, after the first load my ASP.net project run very fast and it just take 1 second or less to reload. The problem will show up again after I restart my computer and trying to load my project from localhost at the first time. How do I make my ASP.net project to load very fast with just 1 second or less at the first time from localhost?
I am trying to load a table from MS Access into SQL Server. The Table has several columns defined as Date/Time. When I define the transform I get an error saying that the conversion between DT_DBDATE and DT_DBTIMESTAMP is not supported.
I have a big problem and i'm not able to find any hint on the Network.
I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)
I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.
I imported the dtsx file inside my "Stored Packages".
I would like to load and run the package programmatically on a Remote Scenario using the web services.
I created a solution with web service and web page that invoke the web service.
When my code execute: Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)
I got the Error: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
The error message doesn't help so much and there is nothing on the www to give me and advice....
Hi! We make complete data load once a week. Now we need to make additional steps to append data on daily basis. We have primary key on the table and it doesn't allow appending duplicate rows. What steps should we create to append data?
Hi everobody! When I run : LOAD DATABASE db1 FROM DISK = 'c:mssqldatadb1_backup.dat' go
I got error message: Msg 3201, Level 16, State 1 Can't open dump device 'c:mssqldatadb1_backup.dat', device error or device off line. Please consult the SQL Server error log for more details. How can I fix the problem? Thank you. Alona
I'm needing to load data on the client side to load into a DataGrid. I decided to use excel to load the data, but it doesn't need to be. My problem is that it only loads from the server not the client. I browse to find the file and get the path with a control named ctlFindFile. A button labeled ctlLoadData will when pressed display the pathway in label1 and also place the pathway in the function GetDataFromExcel which returns a dataset from the spreadsheet and displays the data in a dataset. Data is returned fine if I’m on the server, but when I'm on a remote machine I receive an error. That is unless I've place a spreadsheet with the same name and pathway on the server as on the client machine, then I’m able to load the file. Now how do I get it to load from on the client machine?
Code below:
Private Sub ctlLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ctlLoadData.Click Label1.Text = ctlFindFile.Value DataGrid1.DataSource = GetDataFromExcel(ctlFindFile.Value, "SampleNamedRange").Tables(0) DataGrid1.DataBind() End Sub Public Function GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String) As System.Data.DataSet 'Returns a DataSet containing information from a named range in the passed Excel worksheet Try Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ Data Source=" & FileName & ";Extended Properties=Excel 8.0;" Dim objConn As New System.Data.OleDb.OleDbConnection(strConn) objConn.Open() ' Create objects ready to grab data Dim objCmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " &_ RangeName, objConn) Dim objDA As New System.Data.OleDb.OleDbDataAdapter objDA.SelectCommand = objCmd ' Fill DataSet Dim objDS As New System.Data.DataSet objDA.Fill(objDS) ' Cleanup and return DataSet objConn.Close() Return objDS Catch ex As Exception ' Possible errors include Excel file already open and locked, et al. Return Nothing End Try End Function Error on Client:Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 31: Private Sub ctlLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ctlLoadData.Click Line 32: Label1.Text = ctlFindFile.Value Line 33: DataGrid1.DataSource = GetDataFromExcel(ctlFindFile.Value, "SampleNamedRange").Tables(0) Line 34: DataGrid1.DataBind() Line 35: End SubSource File: C:InetpubwwwrootSAI_LoadWebForm1.aspx.vb Line: 33 Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] SAI_Load.WebForm1.ctlLoadData_Click(Object sender, EventArgs e) in C:InetpubwwwrootSAI_LoadWebForm1.aspx.vb:33 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
I have a user that is loading data via an Access load procedure to a table that actually is a SQL Server 2005 table but is linked to the Access database. He is saying the load is extremly slow. How can I monitor what it is doing on the SQL Server side?
Hi,I'm extracting data from a mainframe application with a view to loadingit into a MS SQL database. I'm trying to determine the most efficientway to format the mainframe extract file to make loading into thedatabase easier.The problem I have is that the existing record structure includes anarray that can vary between 1 to 50. If I include this array in asingle record the table I use to import the data would need 50 columnsthough not all these would be populated. There is a field in the recordto identify how many occurances of the array there are.Current Record Structure :Account NumberAccount NameOther Account DetailsTotalNumberOfArrayFieldsPopulatedArray :Value1Value2Value3....up to Value50 (if required)i.e.12344,Mr Agent,$29.95,2,BX123,BX12412345,Mr Jones,$14.95,3,XX123,XX124,XX12512345,Mr Jones,$14.00,1,XY12312345,Mr Jones,$15.95,2,XZ124,XZ12512346,Mr Smith,$19.95,3,AX123,AX124,AX12512346,Mr Smith,$19.00,1,BY12312347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129There may be up to 3 records created for each Account Number withdifferent values in the array fields.Am I better to break this file into two files .. one with the corecustomer information and a second file with a row for each array valuewhich has a link to the customer information file.OrIs there a way to efficiently process the original file once it isloaded into the staging tables in the database ?i.e.File 1 - Core Customer Information====================================Current Record Structure :Record NumberAccount NumberAccount NameOther Account DetailsTotalNumberOfArrayFieldsPopulatedFile 2 - Array Information====================================Record NumberArray :Value1Value2Value3....up to Value50 (if required)File 1========================12344,Mr Agent,$29.95,212345,Mr Jones,$14.95,312345,Mr Jones,$14.00,112345,Mr Jones,$15.95,212346,Mr Smith,$19.95,312346,Mr Smith,$19.00,112347,Mr Acant,$99.95,7File 2========================12344,BX12312344,BX12412345,XX12312345,XX12412345,XX12512345,XY12312345,XZ12412345,XZ12512346,AX12312346,AX12412346,AX12512346,BY12312347,CX12312347,CX12412347,CX12512347,CX12612347,CX12712347,CX12812347,CX129At times the individual array values will be used for look ups thoughessentially the Customer Information record will be the primary lookupdata.I'm leaning toward changing my COBOL code and creating the 2nd outputunless someone can suggest a simple way to process the information onceloaded into the table.Any help that could be suggested would be greatly appreciated.
I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".
I tried to load data from the table in different server. If I just want to limit one year data ( using date_key in that table ) then what task do I need to do that? Please let me know. Thanks.
How to extract and Load Data using ADO.NET in SSIS.i hope to extract data we have DataReader source .but how to load (Insert) data with ADO.NET ?.and is ADO.Net quicker than OLEDB ?
Hello I am looking for some advise. I have a process to import flat text files. We are importing data from five vendors. These files are seperated by vendor with each vendor having their own directory. The general file layouts are different for each vendor. Each vendor may have up to five different types of files to be imported that are their one directory (sales, inventory, transactions etc). Sales file for Vendor 1 is different layout than sales file for Vendor 2. Each vendor may have multiple instances of each type (store 1 inventory, store 2 inventory, store 1 sales, store 2 sales etc.) There could be up to five hundred files (of the five different types) in a given vendors directory. I am using an import package. This package has five (.dtsx files) different data flows. Each of these data flows has connection managers that connect to the specific types of files (sales, inventory, transactions etc) for that vendor.
My current play is to have the data flow(.dtsx file) parse the store name (from the file name) for each of the file types. It would load/process each of the available file formats (sales, inventory, transactions etc) for that store in that vendor folder. We want the process to load all data files from a given store. It would then move on to the next available store (same vendor). I would like to set the process to run multithreaded so that I am loading as many of the stores for that vendor as possible (there could be over a hundred stores for each vendor) at the same time. How do I get each dataflow to run multiple instances (Instance1 for vendor(x).dtsx, instance2 for vendor(x).dtsx etc) for maximum Vendor(x) input. What is the best way/process/design to track each store name so that each process (instance1, instance2 etc) is loading a distinct store. The files will be moved to a history folder as they are processed. Should I have a different process that gets each store name initially and then saves that information to a SQL table. The dataflow would load the next available store name from the SQL table query and let SQL lock that store?
I have to Load Contacts data from different systems into a single SQL Server 2005 table, the scenario is the following :
I have three systems System 1,System 2 and System 3
Step 1 Load Data From System 1 Step 2 Load Data from System 2 and if there are matching contacts with System 1, then match their details, keep System 1 details Step 3 Load Data from System 3 and if there are matching contacts with System 2, then match their details and if different, flag them, do the same with System details.
The unique ids in the three systems are not the same so only way to match is to do a concatenation of name + address + zip code
everything is ok.. the connection is on..the page can be loaded... no error.... BUT the data from the database cannot be retrieved... as in.. it just didnt load on screen... i dont know whats wrong... i have follow all the steps show on a book.. can anyone pls help me?? thks
Hi, I have problem I'm hoping someone can give me some pointers with.
I need to load data from several text files into one table. The format of the files are simple - each line is comma separated, with double quotes around each element e.g.
However, the files themselves will have different numbers of columns e.g file 1 may have 8 columns, file 2 may have 16 columns.
I'm going to load the data into a table that has at least as many columns as the longest file. The table columns are all varchar, and are named simply as [Col001] [Col002] [Col003] etc...
The first two columns of this table must be left empty during the load (I use these later on), so the data entry will start at [Col003].
My question is what is the best way to do this? I thought perhaps using a BULK INSERT in a stored procedure might do the trick, but I haven't used it before and haven't got very far. I gather another approach might be to use bcp utility. Someone has also suggested a DTS package, but the filenames will be suffixed with current date/time stamp, so i don't think that will work.
My preferred appraoch would be the BULK INSERT..but i'm open to any pointers.
I am extracting data from an SQLServer database to load into Teradata using DTS. The performance is abysmal. The same data in a text file loads quickly via multiload. I can move the data to other DBMSs via DTS quickly as well. Is there some way for me to improve the elapsed time/performance while using DTS? If not, what is the best way to move data from SQLServer into Teradata?
I have a sql server database linked to my application. I have a table that I want one of the columns (Service Perfomed) to load in a list box. When I start the application nothing appears in the list box. What could I be doing wrong?
Here the code the visual studio created:
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load
Me.ServiceTableAdapter.Fill(Me.MaintenanceRecordsDataSet.Service) End Sub
I've tried combo box, text box and these seem to work fine but not the list box.
I'm fairly new to SQL and I have been asked to load multiple tables with duplicate and inconsistent data into a single table. Each source (table) has been assigned a rank in terms of the quality and reliability. After some thought, I have come up with this solution to get all of the data loaded based on rank.
Does anyone know of a better way to do this?
Value is a unique key.
SELECT * FROM table_1
union
SELECT * FROM table_2 where (value + '_' + yr) in ( select value + '_' + yr from table_2 except select value + '_' + yr from table_1)
union
SELECT * FROM table_3 where (value + '_' + yr) in ( select value + '_' + yr from table_3 except select value + '_' + yr from table_2 except select value + '_' + yr from table_1)
I need to load a lot of data into a sql table as fast as possible. (All data is string data, CSV file). I have read that the "fastest" way to load data is using the "Fast Load" option of the OLEDB Destination but I have also read that the "SQL Server" Destination is fast.
1. Is there a general consensus as to which way is the quicksest load?
2. The file is a CSV file. Would a fixed-format file be faster to read?
Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.
I need to import typically 5000 - 50000 rows of data to OLTP database from a flat file. Flat file contains all active information - new & existing rows. About 10% of the rows are new. Each row has about 30 columns.
I have created a Data Flow which divides the data to new records (for insert) and existing records(for update) via the Lookup transformation (as described in Jaime Thompson blog - thanks).
The question is - in performance perspective - should I check if the existing records have been changed or not?
Currently I do a full refresh of all data - (my opinion is that in SQL side - if you update a row with exact same data - it will not cause SQL server to "perform row update") - however I'm concerned that SSIS performing between 4000 - 40000 update statements each time in OLE DB command may cause issues.
Is there anyway to load all the raw data in one directory into the database? l've several files with same format but different filename in one folder which l do not know their filename before hand. Is SSIS For Each Loop can help me to accomplish the file loading? If so, where can l find the example?
