I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.
The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.
Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
....
up to Value50 (if required)
There may be up to 3 records created for each Account Number with
different values in the array fields.
Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?
i.e.
File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
....
up to Value50 (if required)
At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.
I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.
Any help that could be suggested would be greatly appreciated.
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.
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?
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 an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.
What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.
We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.
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]
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?
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
I am researching alternatives to a stored procedure that uses a linked server query that my ASP page uses to retrieve records to the page.
Should I:
Use replication (Data is constantly updated, so a snapshot won't do. Merge replication?)
Break up the stored procedure into subqueries?
Use a view on the remote server instead of the actual tables?
I am trying to reduce the amount of table scans, what is the best way to do this?
Here is the stored procedure I am trying to tune: (@startDate and @endDate are parameters passed from the web page):
CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime AS SELECT Store.[Str#], Store.[Dcid#], E.card_number, E.program_number , E.start_date, E.end_date, E.card_number, E.event_number , E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount , L.merchant_name FROM (Store INNER JOIN Event E ON Store.[DemoID#] = E.event_number) LEFT JOIN (Location L RIGHT JOIN POS_TX P ON L.location_number = P.location_number) ON E.event_number = P.event_number WHERE (((Store.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate ORDER BY Store.[Str#] GO
Any suggestions on my sp or anything else that can implement would be greatly appreciated.
My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .
what approach should i take so that data gets loaded in a relational structure.
for example
Name Age Salary Address dsds 23 fghghgh
Salary description level 2345 nnncncn 4
here salary is used in this example , the datatype is char in real scenario
what approach should i take to load the data in with cleansing the spaces in ssis
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.
