Split Single File Over Two Tables.

Jul 18, 2006

I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS?



TIA,

Barkingdog



View 3 Replies


ADVERTISEMENT

Integration Services :: SSIS Split Single Input File Data Over Multiple Tables?

Sep 30, 2015

I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes.  The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length. 

Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).

The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.

View 8 Replies View Related

Exporting Multiple Tables To A Single File

Oct 1, 2004

I need to export data from multiple tables into one single file. The big problem here is that the tables will have different column types.

I am attempting to create something that allows users to be able to send me the contents of their tables's, through either email or ftp. I would prefer to make it easier for them so they only have to deal with one file, instead of the multiple files that bcp and dts create when exporting from multiple tables.

I was thinking of using DTS or BCP and then join (append) the files (either zip them or append the files together in some fashion), but I was hoping that there was an easier method out there.

Any ideas on how I may accomplish this would be greatly appreciated.

Andy

View 9 Replies View Related

Combining Multiple Tables Into A Single Flat File Destination

Aug 21, 2007

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.


e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

View 9 Replies View Related

Writing Data From Multiple Tables To A Single Flat File

Sep 13, 2005

I have a package that contains three database tables (Header, detail and trailer record) each table is connected via a OLE DB source in SSIS. Each table varies in the amount of colums it holds and niether of the tables have the same field names. I need to transfer all data, from each table, in order, to a flat file destination.

View 6 Replies View Related

Integration Services :: How To Load Multiple Tables Data Into Single Excel File

Aug 26, 2015

My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?

View 3 Replies View Related

Split Single Row To Multiple Rows

Feb 27, 2012

I have a table which looks like

Low High
-------------------------
cx01 cx04
sn05 sn08

I need output like

Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08

How to get this output using sql query?

View 8 Replies View Related

SQL View To Split Rows In Single Table...

May 24, 2007

I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for

I have a table is MSSQL 2000 that looks as follows:


Code:



id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1



Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:


Code:



custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1



Any help would be great! I imagine some sort of join command can be constructed using a single table?

View 2 Replies View Related

Split A Single Column Data In To 2 Columns

Jan 13, 2005

Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive

FName
John?Doe

FName LName
John Doe

thanks for the help
prit

View 7 Replies View Related

Transact SQL :: How To Split Comma And Pipe Separated From Single Set

Oct 21, 2015

I have an input parameter of an SP which value will be passed with different combinations with 2 seperators (comma and pipe)

Value to the parameter is like this :      '10|22|microsoft,20|25|sql,30|27|server,40|29|product'

I want output like this

Column1       Column2      Column3
10                   22              microsoft
20                   25              sql
30                   27              server
40                   29              product

Pipe separator is for column and comma separator is for row.

I know if its a single separator, it can be done with function but how to do if its 2 separators?

View 6 Replies View Related

DTS - Split Single Source Record (text) To Multiple Target (sql)

Aug 31, 2000

I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.

As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.

For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:

[date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3]

trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows:

[date], [custid], [code1], [name1], [amount1]
[date], [custid], [code2], [name2], [amount2]
[date], [custid], [code3], [name3], [amount3]

What is the best way to do this using DTS?

I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work

Vasilis Siatravanis,
siatravanisv@interamerican.gr , vasilliss@hotmail.com

View 6 Replies View Related

SQL Server 2008 :: Split Postal Code Range Into Single Row

Jul 8, 2015

I got a table with organisation codes with postcal code ranges, from-to.

Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249

I would like to generate rows for this range like this:

001 5200
001 5201
001 5202
.....
001 5249

The table looks like this:

SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation

View 4 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

Split The Existing MDF File Into Mutliple Files As A File Group?

Jul 30, 2007

I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!!

Anyways.. I heard that the general performance woul grow if i had them as "File Groups"..

Is there anyway - to split the existing MDF file into Mutliple files as a File Group?

Where should i start? Can someone please direct me..

View 1 Replies View Related

Split Data File Into Multiple File Groups..!

Dec 18, 2006

I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....

View 1 Replies View Related

Split Tables And Keep ID

Sep 27, 2005

I have a large table that I'm planning on splitting out into 5 smaller ones. What I need to do is maintain some central repository for auto-numbering new records to make sure that no 2 records in different tables have the same unique ID. Thanks in advance!

View 14 Replies View Related

Design -- Should This Be Split Up Into A Few Tables?

Feb 5, 2005

I'm grappling with this design problem right now:

I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin"
And here's my companies table: "CompanyID, CompanyName, SalespersonID"

Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."

Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.

Anyway, I really don't know what I should be doing with this design. Any suggestions?

Thanks in advance,
-Starwiz

View 1 Replies View Related

Big Table(?) Or Split Between Tables?

Nov 20, 2007

Hi Guys

I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars.

I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba).
The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way?

The questions are:

Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on?
Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on.

How many rows is okay to have in one table (with 25 columns) before its too big?

Thanks in advance.

Best regards
Johan, Sweden.

CREATE TABLE [dbo].[Cdr](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Abandon] [varchar](7) NULL,
[Bcap] [varchar](2) NULL,
[BlId] [varchar](16) NULL,
[CallChg] [varchar](6) NULL,
[CallIdentifier] [uniqueidentifier] NULL,
[ChgInfo] [varchar](5) NULL,
[ClId] [varchar](16) NULL,
[CustNo] [smallint] NULL,
[Digits] [varchar](32) NULL,
[DigitType] [varchar](1) NULL,
[Dnis1] [varchar](6) NULL,
[Dnis2] [varchar](6) NULL,
[Duration] [int] NULL,
[FgDani] [varchar](13) NULL,
[HoundredHourDuration] [varchar](3) NULL,
[Name] [varchar](40) NULL,
[NameId] [int] NOT NULL,
[Npi] [varchar](2) NULL,
[OrigAuxId] [varchar](11) NULL,
[OrigId] [varchar](7) NULL,
[OrigMin] [varchar](16) NULL,
[Origten0] [varchar](3) NULL,
[RecNo] [int] NULL,
[RecType] [varchar](1) NOT NULL,
[Redir] [varchar](1) NULL,
[TerId] [varchar](7) NOT NULL,
[TermAuxId] [varchar](11) NULL,
[TermMin] [varchar](16) NULL,
[Termten0] [varchar](3) NULL,
[Timestamp] [datetime] NOT NULL,
[Ton] [varchar](1) NULL,
[Tta] [int] NULL,
[Twt] [int] NULL,
[DateValue] [int] NULL,
[TimeValue] [int] NULL,
[Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'),
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

View 14 Replies View Related

T-SQL To Split Data From One Table Into Two Tables?

Feb 21, 2005

What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.


-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)

-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)

View 5 Replies View Related

Split Row's Columns Across Multiple Tables

Feb 7, 2006

Hello,

Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?

As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.

How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.

Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.

Thoughts?

Thanks in advance,
Dan

View 5 Replies View Related

How To Split Out Table Rows Into 3 Tables

Mar 14, 2006

I imported all rows of my txt file using SSIS 2005 into a table.  I am now trying to figure out how to split out the header, payment rows, and maintenance rows.  First, some information.

An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row

The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).

I need to

1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table

I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.

To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31.  If it contains 'MT' then you know it's a maintenance row, else it's a payment row.

How in the hell do I do this???

View 4 Replies View Related

How To Assign Unique PKs And FKs On Split Of Txt Rows Into DB Tables

Mar 14, 2006

SSIS 2005

Ok, I have a task in SSIS that does the following and works:

1) Brings in a txt file

2) Using a conditional component, checks for a value in the row.

3) Based on the value, splits the row into one of 3 tables (Header, Maintenance, or Payment)

Here is a print screen of what I have so far which splits Header rows into it's own table, Maintenance rows into its own table, and Payment Rows into its own table:

http://www.webfound.net/qst_how_to_add_header_PK_and_FKs.JPG

Here is a print screen of the conditional split:

http://www.webfound.net/conditional_split.jpg

Please take a look at the txt file here before it's processed:

http://www.webfound.net/split.txt

http://www.webfound.net/rows.jpg

Notice that the pattern is a header row, followed by it's corresponding detail rows.  The detail rows are either Maintenance or Payment rows. 

I need to somehow during the Script component or some other way, to assign a unique HeaderID (PK) to each of the header rows and add that ID to it's corresponding Maintenance and Payment detail rows in their corresponding tables as a PK.  The problem is

1) I don't know how to do this in the flow of the components as I have it now

2) How do I tell it to create a new Header ID and Header FKs for the detail rows based off of each new Header row?

In the end (much later on in my entire package), the goal is to be able to run a stored proc to join and select the Header and Details rows back into a final table so I can then do more processing such as split each header and detail rows into their own txt files, etc....I don't need to go into details why but just know that this is the goal, therefore I need to relate each header row with their corresponding detail rows that are split off into a MaintenanceRow and PaymentRowTable

 

View 2 Replies View Related

Split The File

Oct 16, 2006

my ssis package downloades the text file from Ftp. iT downloades for ex 5 files.

I want to split the file to smaller file after downloades. If the size of file is more then 600 mB then I want to split it into 6 files.

please suggest if any task in SSIS can perform this or any other way.

View 1 Replies View Related

Stored Procedure To Split Values And Insert Into Tables

Oct 19, 2012

I have an empty employee table and employee_details table. The temp table which i created say it has 10 columns of which 6 are from employees and 4 from employee_details. I have loaded some data into temp table say 10 rows.

Now the stored procedure using cursor should be created such that, it should fetch the rows one by one from temp table and insert the values into employee table(6 columns) and the rest in employee_details table(4 columns).
This is the scenario.

Here is the column names of my temp table

CREATE TABLE [dbo].[temp](
[employee_id] [char](7) NOT NULL,
[first_name] [char](50) NOT NULL,
[middle_name] [char](50) NOT NULL,
[last_name] [char](50) NOT NULL,
[title] [char](5) NOT NULL,

[Code] ....

Here the last 4 columns belong to the employee_details table. The stored procedure should fetch record by record from temp split and insert into employee and employee_details table.

View 1 Replies View Related

Split Txt File Into Multiple

Mar 16, 2006

How can I split this incoming file into separate txts.  I want to cut out each Header/detail row section into a new txt.  What I mean by header/detail row:





incoming txt file:



http://www.webfound.net/split.txt

basically want to cut out each section like this:

http://www.webfound.net/what_to_cut.txt

http://www.webfound.net/rows.jpg



and a kicker...each new txt name must use a certain field (based on x numbers in header row) followed by another field whcih is the date form the header row.  somethign like this:



SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 59,4) == "HD" + SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 1,8) == "HD" + .txt

I need some hand holding here, it's my first time trying to do something so complicated in SSIS 2005.  If I can first just get the txt split into multiple, that would be a big help.

View 7 Replies View Related

SQL Server 2008 :: Joining Two Tables - Split Rows Into Column

Sep 29, 2015

I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.

This how the data is

Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample

This is what is expected...

Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')

[Code] ....

How I can implement this ? I believe if there are any numbers I need to split into two columns .

View 2 Replies View Related

Conditional Split On Field In Csv File

Jun 21, 2007

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

View 8 Replies View Related

Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables

Sep 5, 2007

Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7

The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?

Many Thanks,
probetatester@yahoo.com

View 7 Replies View Related

Can I Split A Long .sql File Into Multiple Files?

May 9, 2008

 I have one really long .sql file I'm working on.  It's actually a data conversion type script.  It's gotten really cumbersome to work on as long as it is.  I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks 

View 3 Replies View Related

MSSQL DB File Getting Bigger, Can Split Or Reduce?

Aug 30, 2006

Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation?

i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?

View 3 Replies View Related

How To Split One Report Into Many File Based On Volume.

May 4, 2007

Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,).

Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees.

Can anyone suggest a way to do this€¦

Regards,
Krishna

View 1 Replies View Related

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables

Mar 21, 2008

Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:


1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.

1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.

1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.

I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.

Thanks in advance for any help/suggestions,
Bob

View 1 Replies View Related

SQL 2012 :: 500GB Data Warehouse - How To Split It Into File Groups

Aug 21, 2014

OK, so I have:

- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others

How should I organize them across available drives? I was thinking about sth like:

800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved