SQL 2012 :: Check Columns Before Process And Calculate Size In Byte In SSIS

Apr 10, 2014

I need find out the number of columns in flat file before i process that particular file.

I have file name in @filename variable and file path is @filepath variable.

But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles

And I am using for each loop container to read the file one by one and put the file name in @filename variable.

and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

My file structure is:

ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.

Let me know how i will do it.I need to also calculate the size of flat file.

View 0 Replies


ADVERTISEMENT

SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size

Aug 23, 2006

Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,

Any help on this greatly appreciated...

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)


Many thanks

View 5 Replies View Related

SQL 2012 :: Calculate TempDB Size For Read Committed Snapshot Enabled

Apr 14, 2014

I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.

As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.

basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction

My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .

Still I am confused in calculating the size , My perform counter gives me data as such

Free Space in tempdb (KB)               279938496
Version Generation rate (KB/s)           53681040
Version Cleanup rate (KB/s)       53422320
Version Store Size (KB)      258720
Version Store unit count        22
Version Store unit creation                      774
Version Store unit truncation         752

View 4 Replies View Related

SQL Server 2012 :: Calculate Number Of Groups And Group Size With Multiple Criteria

Jun 15, 2015

I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table

GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1

The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.

View 9 Replies View Related

SQL Server 2012 :: Calculate Total Between Two Columns

Sep 14, 2015

I have update schema and I upload image with my desire result.

CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int

[Code] .....

View 7 Replies View Related

SQL Server 2012 :: Dynamic Pivot Statement To Calculate And Organize Columns

Mar 11, 2015

How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:

CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME

[Code] ...

Below is Our Sample Table Data.

Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015

How to write Pivot Satement to get Oupt like below:

1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12

1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4

1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17

View 5 Replies View Related

CLR UDT Max Byte Size

Aug 24, 2007

Hello,

I need some help working with CLR UDTs. I have created two UDTs called trajectory and point. Each trajectory consists of a list of points. Each point consists of three members : lon( type double), lat( type double) and datetime.

I have written my own IBinarySerialize.Write method for the trajectory type which is the following:

Dim maxSize As Integer = 4000
Dim value As String = ""
Dim paddedvalue As String

Dim i As Integer
Dim pt As Point

For i = 0 To point_list.Count - 1

pt = point_list.Item(i)

If i = 0 Then
value = value & pt.X & "|" & pt.Y & "|" & pt.D

Else

value = value & ">" & pt.X & "|" & pt.Y & "|" & pt.D
End If

Next

paddedvalue = value.PadRight(maxSize, ControlChars.NullChar)

For i = 0 To paddedvalue.Length - 1 Step 1
w.Write(paddedvalue(i))
Next

If I try to store 225 points for a trajectory then the following error occurs:

System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.

Why is that happening? The limit size for a UDT is 8000bytes. Each point needs 24Bytes (2*SizeOf(double) + sizeOf(Datetime)).

Please help
ST

View 2 Replies View Related

SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables

Dec 4, 2014

I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.

I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.

View 6 Replies View Related

SQL Server 2012 :: Check To See If Columns Are NOT NULL In A Record

Jun 25, 2015

I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.

I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.

View 5 Replies View Related

Do Stored Procedures Have A Limit On Number Of Parameters Or Byte Size Passed In?

Nov 21, 2007

Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark  

View 3 Replies View Related

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'

Mar 26, 2008

hi
i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code.
can anyone help me correct the error and let me know ow to solve it
thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString")
myConnection.Open()
 Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id")
cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error
End Sub

View 1 Replies View Related

SQL 2012 :: Use SSIS Or Stored Proc Or Combination To Handle Stopping A Process On Failure

Apr 7, 2015

A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.

I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.

View 2 Replies View Related

SQL 2012 :: Script In SSIS To Check Data Connections / Sources

Jun 10, 2014

script in SSIS to check data connections/sources and send an email if it fails validation. I found this script online, but I'm not real good with scripting and only VB scripting at that.How can I modify this script or is there another one here that will do what I need. The data connection in question links to Salesforce and will fail validation if the password/security token is invalid.

The reason I need this script is that we have no control over the password changes and need to know if the salesforce team changes the password without informing us.

/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;

[code]...

View 9 Replies View Related

Integration Services :: How To Process SSAS 2012 Cube With SSIS 2014 And Script Task

Nov 11, 2015

Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)

Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?

View 3 Replies View Related

SQL 2012 :: Find Out Number Of Columns In Flat File Before Process That Particular File

Apr 14, 2014

I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

[code]....

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.

View 4 Replies View Related

Calculate Check Digit On Subset Of Ids

Jul 17, 2006

how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.

View 7 Replies View Related

How To Calculate Database Size

Nov 6, 1999

How to calculate database size ?

When u setup e-commerce site for selling product then
how to calculate datbase size ?

Navnit

View 1 Replies View Related

Calculate Table Size

Oct 10, 2001

Hi all!

How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.

Thanks,
ndba

View 1 Replies View Related

How Can I Calculate Table Size ?

Jan 29, 2008

this is the table i have created .

CREATE TABLE [dbo].[Table] (
[BCode] [varchar] (3) ,
[RefNo] [varchar] (12) ,
[RType] [varchar] (2) ,
[TheirRef] [varchar] (50) ,
[Amount] [money] NULL ,
[AccountNo] [varchar] (20) NULL ,
[EnteredDate] [datetime] NULL ,
[EnteredBy] [varchar] (6) ,
[IsTrue] [varchar] (1) ,
[RDate] [datetime] NULL ,
[Details] [varchar] (255) ,
[PostBy] [varchar] (6) ,
[SeqNo] [int] IDENTITY (1, 1) ,
[ThisCode] [varchar] (3) ,
[VDate] [datetime] NULL ,
[Id] [varchar] (20) ,
[Flag] [varchar] (1) ,
[TDate] [datetime] NULL ,
[Type] [char] (1) ,
[SerialNo] [int] NULL
) ON [PRIMARY]
GO

if i insert a single row in a table with values :

INSERT INTO [Table]
SELECT '100','1','DD',1000000,'999999000',
'2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502',
'101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'

EXECUTE sp_spaceused 'Table'

name | rows |reserved | data |index_size | unused
[Table] | 1 |16KB | 8KB | 8KB | 0KB

i couldnot understand
i) how the system calculate this 16 KB and 8 KB ?
ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ?
iii) how can i see the change in size of table by reducing the size of datatype ?

can anybody help me ?

View 3 Replies View Related

How To Calculate Average Row Size Of A Record.

Apr 22, 2008

 I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table. Here is my table structureCREATE TABLE patient_procedure(    proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY,    patient_id int NULL,    surgeon_name varchar(40) NOT NULL,    proc_name varchar(20) ,    part_name varchar(30),    wth_contrast int ,    wthout_contrast int ,    wth_wthout_contrast int,    xray_part varchar(60),    arth_area varchar(30),    others varchar(30) ,    cpt varchar(20) ,    procedure_date smalldatetime NOT NULL,    mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id)    REFERENCES dbo.patient_information (Patient_id)) Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.Here the problem is when the user enters these two procedures information remaining fields will store the  null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. Waiting for valuable suggestions. 

View 2 Replies View Related

Calculate The Size Of A Database And Backup It.

Feb 13, 2004

Hi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.

thanks.

View 5 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Transact SQL :: How To Calculate Size Of Files That Have UNC Paths For

Mar 27, 2014

I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?

View 4 Replies View Related

How To Calculate The Size Of A VARBINARY(max) Field Or Variable

Jan 7, 2008



How can i do the following:


Calculate the size of a varbinary(max) field or variable

Calculate the average of a varbinary(max) table column
I am using SQL 2005
Thanks for your posting

View 3 Replies View Related

Disaster Recovery: Calculate DB Size Based On .dmp Files

Feb 7, 2000

I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?

I know it won't be pretty.

`Ken

View 1 Replies View Related

How To Calculate The Space (size In Bytes) Used By A BLOB Column In A Row

Dec 3, 2007

What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?

Thanks.

View 3 Replies View Related

Integration Services :: Using Foreach Loop Container To Elaborate A Table With Two Columns - SSIS 2012

May 6, 2015

In order to update an Oracle table target from a SQL Server table source I need to use a Foreach Loop Container, so I can loop on the rows of the SQL Server table source. This source table has two columns: the old identifier to update and the new identifier to apply. I must use the value of the old identifier to filter the Oracle rows to update, while the new identifier is the new value to assign to the filtered old identifier.

I already know how to use the Foreach Loop Container when it is necessary to loop on an unique column of a table/view (using an object variable, using a Foreach ADO enumerator, etc.), but I need to loop on two columns.

View 8 Replies View Related

Calculate Age From 2 Columns

Feb 7, 2008

hello i`m rather new in sql and i have to do something simple for u i guess.
i have 3 columns a)date of birth b)current date c)final age.
i have the values of the first 2 columns and i want column c to be calculated automaticaly when i enter the values of the first 2 columns. example.
a)20/03/1972 b)07/02/2008 c)?? (should be 36).

if you could help me i would appriciate very much.
thanks in addvance

View 3 Replies View Related

How To Calculate The MAX Value From A Range Of 48 Columns?

May 25, 2001

I have a table of data with values for every 30 mintues per day (i.e. 48 values per record per day.) I need to find the maximum value over the range of half-hourly time periods in any particular day and then calculate the MAX value for each month and year.

Is there a way of doing it? I am using SQL Server 7.0.

Many thanks.

View 1 Replies View Related

Transact SQL :: Calculate Total Between Two Columns?

Sep 16, 2015

I have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.

CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);

And i have some data:

INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)

I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):

SinGroup C1 C2

y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50

Z 50 0

And also result like this (c1-c2 and grop by each row,the result show under the column 1):

SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0

View 5 Replies View Related

T-SQL (SS2K8) :: Calculate On Newly Created Columns

Aug 2, 2014

I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns

I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.

Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.

SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours

[Code] ....

View 1 Replies View Related







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