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.
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..
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 RelatedHow 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.
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
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
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 RelatedHi.. 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 ?
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
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.
Hello everybody
I have one question about deleting blank row on flat file destination from conditional split.
I create an SSIS package to filter data from Flat file source.
On flat file source, it is Ragged right format and header row delimeter in {CR}{LF}
the coulums are devided manulaay using markers.
I use only 2 columns divided and send the source into conditional split task and the conditions are given to filter data,
when the output from conditional split is placed on flat file destination, i notice blank rows on the output. I want to delete the blank rows so the result data can be displayed continuously in rows.
anybody has any idea for this? I know the script task will work but hope to avoid to use script task.
Thank you in advance for all the help.
I have a Problem with my destinations. I have a split condition with two ways the flow can use.
In this case: all and Date.
All and Date can be set by using a variable. Its working good.
When a user fills the variable with a date value (cast to string) the conditional split executes the correct flow with all the needed rows... The same time the all flow will be executed with 0 rows. In the end the destionation file for the all values will be overwritten with nothing. The same on the other hand when a user fills the variable with the all value, the date file is empty. What can i do to make sure that the files are not empty?
Import.csv file looks like,
TABLE_NAME DESC CODE
tab1 table1 A
tab1 table1 B
tab1 table1 C
tab2 table2 D
tab2 table2 E
tab2 table2 G...
First column values are table names which are already exists in target database. Next two columns[Desc],[Code]Â data gets populate from CSV file to table.
In this scenario, how to load tab1 data into the same table in destination and so on.
Which way will be more standard to accomplish this task? If its a script task using C#, looking for clear script to identify a value changes in the first column.
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.
Quick question.
I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it).
Thanks in advance.
Ray
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!!
Is 8,000 or so rows of data "large" and unwieldly in SQL terms?
Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about.............
What did they say about a little bit of knowledge being a dangerous thing?
Thanks in advance of any guidance to a neophyte!!?
Hi to all
I have one problem regarding sp and pass value in sp
I am gating a value like Abc,Def,Ghi,
Now I want to split the whole pass value by “,�
And fire one for loop to store value in database
This things is done in asp.net web form but I want to do all process in sp
So please guide me how I am write sp .
The purpose is pass value one time so connection time is decrees and give fast perforce
SQL UDF split()
The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string.
Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure.
To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results.
If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters.
Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table.
Here is how I handled the above situation.
1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max))
2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string
3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data
The basic structure of the stored procedure is as pasted below:-
CREATE PROCEDURE FETCH_SALES_DETAIL (
@MONTH VARCHAR(MAX)
)
AS
BEGIN
DECLARE @MONTH_CNT INT,@MTH DATETIME
SET @MONTH_CNT=1
WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> ''
BEGIN
SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME)
--<<Application specific T-SQLs>>-- (BEGIN)
SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE]
FROM SALES (NLOCK)
WHERE [SALES_MONTH]= @MTH
--<<Application specific T-SQLs>>--(END)
SET @MONTH_CNT=@MONTH_CNT+1
END
END
Dbo.SPLIT() function takes 3 parameters
1)The main string with the values to be split
2)The delimiter
3)The Nth occurrence of the string to be returned
The functionality of the UDF is as explained STEP by STEP:
1.Function Declaration
CREATE FUNCTION [dbo].[SPLIT]
(
@nstring VARCHAR(MAX),
@deliminator nvarchar(10),
@index int
)
RETURNS VARCHAR(MAX)
Function is declared with 3 input parameters:-
@nstring of type VARCHAR(MAX) will hold the main string to be split
@deliminator of type NVARCHAR(10) will hold the delimiter
@index of type INT will hold the index of the string to be returned
2.Variable Declaration
DECLARE @position int
DECLARE @ustr VARCHAR(MAX)
DECLARE @pcnt int
Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter.
3.Variable initialization
SET @position = 1
SET @pcnt = 1
SELECT @ustr = ''
Initialize the variables
4.Main functionality
WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index
BEGIN
IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN
IF @pcnt = @index BEGIN
SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar)
END
SET @position = @position + 1
END
ELSE BEGIN
SET @position = @position + 1
SET @pcnt = @pcnt + 1
END
END
4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter.
4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter
4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented.
4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable
5.Return the value
RETURN @ustr
I hope this article would benefit those who are looking for a handy function to deal with Strings.
Feel free to send your feedback at dearhari@gmail.com
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend.
Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns.
Thanks
Hi,
Is it possible to split the following value in sql server ?
I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database.
Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution ..
I am using ASP.Net and C# backend is SQL Server 2000.
Thanks and Regards
Arul
CREATE PROCEDURE [dbo].[ShowComboLocation]@Keyword varchar(50) ASSELECT TOP 100 PERCENT PropertyAreaID, PropertyAreaFROM dbo.iViewAllWHERE (PropertyArea LIKE '%' + @Keyword + '%')GOQuestion 1 isIf Keyword ="London WestEnd Harrods", I know my query will end up like this (PropertyArea LIKE 'London WestEnd Harrods')But I want to to individually search for 3 or 1-nth words therefore my query should end up like this(PropertyArea LIKE 'London')OR (PropertyArea LIKE 'WestEnd')OR (PropertyArea LIKE 'Harrods')i WANT TO perform this on my SQL STored Procedure,Can anybody provide code or links pls
View 1 Replies View RelatedI have a procedure that is going to be called through asp pages. This procedure carries out instructions depending on whether customers wants to insert, update or delete their portfolios. Rules are as follows: 1. It should not allow duplicate portfolio name to insert. 2. If customer has reached their max limit of 20 portfolio they can't add. They may have to delete or update the existing portfolio first. 3. all the error handling is done and returned as output parameters.
Now coming to the question at present I have one procedure that does all these things. Should I split up the procedure and have three procedures handling the events seperately: 1 Insert 2 Delete 3 update The reason I am concerned is 1 procedure being hit so many times by concurrent users with varying events. I am concerned about performance issue and slowing down of the page. I do not have exact numbers of users at this point. But they would be in thousands or more. Thanks for any suggestions or advice you all might have to share. Hiku
I want to know how to parse a fullname into a fname and lname.
View 2 Replies View RelatedWhat I have is a table with a primary key. Then I have 5 other tables with a relating key. No problems there.
I need to create a relationship with the primary table (primary) key who's data field is 25 charachters. I need to parse that out and have 3 charachters go to one, 2 to the other and so on.
I don't know how to do that, can you help?
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it
select a name for each value and return a recordset. Any pointers greatfully received.
First attempt is dreadfully slow as I am opening recordsets each time
Function func_getFood()
Dim rsfoodsql
Dim foodoutput
for x=1 to ubound(masterfoodarray)-2
set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'")
if not rsfoodsql.eof then
foodoutput=rsfoodsql("foodname")
if not foodoutput="" then
response.write foodoutput&"<BR>"
end if
end if
next
End Function
Hope someone can help,
cheers
Hi everybody
Does any body know how to split a field in a table into two fields
eg
usermaster(table)
userid(field)
usermaster has 40 users with user id 1 to 40
i want to get data as
userid userid
1 21
2 22
3 23
. .
. .
. .
20 40
Thanks you very much
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 RelatedHi
I have this string which might have a hyphen in it "-"
What I want to do is
if I get a hyphen then take all characters after hyphen
else take only all the characters starting from the 5th position of the string
How can this be achieved?
following is the base table
create table #sample (id int , value varchar(100))
insert into #sample values (1,'aaa,bbb')
insert into #sample values (2,'ccc,ddd')
insert into #sample values (3,'aab')
& i need the attached output
So I would like to split rows in my query based on if they match criteria and I really havent dealt with splitting in SQL only combining(which is usually what everybody does lol)
So for instance my query results is this
SOPNUMBER TAX TOTAL
14-123456 $500.00
Hi
I have tried like this. but I cant execute my SP it ended up with errors.
IF (@FirstLetters IS NOT NULL)
BEGIN
SET @FirstLetter = 'SELECT SplitValue FROM dbo.FnSplitString('+@FirstLetters+','',''))'
print @FirstLetter
END
SELECT
P.PUB_ID AS ''PubId'',
P.PUB_TITLE AS ''PubTitle''
FROM HDS_PUBLICATION P
INNER JOIN HN_IM_JOIN IM
ON IM.PUB_UNID = P.PUB_UNID,
HDS_CUSTOM C,
WD_PUBLIC_SHELF S,
HDS_TOPIC T
WHERE P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = ''False''
AND P.PUB_ID = S.PUB_ID
AND P.PUB_UNID = T.PUB_UNID
AND S.Audience = ''Public''
' + @FilterByLang + '
ANDP.PUB_TITLE LIKE '+ @FirstLetter +'''%''' +'
EXEC SP
Any one please clear me
Thanks,
shakthiA
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query
any one know help me
Regards,
Prabu R
I have the following:
iddocidgroupidOption
48986111235
353973334
2314820112346
would like to see this result:
iddocidgroupidOption
48986111
48986112
48986113
48986115
35397333
35397334
231482011
231482012
231482013
231482014
231482016
thanks you