SSIS Missing Records From Query
Mar 26, 2008
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time.
The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package.
At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon.
The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job.
Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
View 12 Replies
ADVERTISEMENT
Mar 6, 2008
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
PID
MISSING FROM
1
TBL3
2
TBL3
3
TBL1
4
TBL2
4
TBL3
5
TBL1
6
TBL1
6
TBL2
7
TBL2
10
TBL1
10
TBL2
Thanks all.
View 5 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Apr 13, 2008
Hi
i need to finrd the find the missing records from a table based an formdate and todate,where the dates are not arranged in proper manner.
please helpme to find these records.
Rajesh
View 1 Replies
View Related
Mar 7, 2001
Hi,
OK, here's a question for all you SQL Gurus.
I want to find all the records that are in t2 but are not in t1.
(NOTE: table print outs at bottom of post)
I could write this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select t2.t from t2 where t not in (select t from t1)
t
-----------
6
(1 row(s) affected)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
BUT it takes to long.
So I thought of this:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t where t1.t = null
BUT it doesn't work.
WHY NOT?
Is there anything similar that i can write???
It should work, heres the output for the simple left join:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t
t t
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 NULL
7 7
(7 row(s) affected)
Thanks,
Benny Hill
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select * from t1
t
-----------
1
2
3
4
5
7
(6 row(s) affected)
select * from t2
t
-----------
1
2
3
4
5
6
7
(7 row(s) affected)
View 4 Replies
View Related
Dec 27, 2004
Hi,
I have a table that contains 2 columns. The first column has the datetime, and the second has a value (see the "Before" table). In my SELECT query I want to select all records like 0:00:00 <= Date <= 0:40:00. However the missing 10-minutes value 0:30:00 should be automatically inserted with value = NULL.
Before:
Code:
Date Value
------------------- -------------------
13/12/2004 0:00:00 15
13/12/2004 0:10:00 17
13/12/2004 0:20:00 21
13/12/2004 0:40:00 12
13/12/2004 0:50:00 13
After:
Code:
Date Value
------------------- -------------------
13/12/2004 0:00:00 15
13/12/2004 0:10:00 17
13/12/2004 0:20:00 21
13/12/2004 0:30:00 NULL
13/12/2004 0:40:00 12
Can anyone help me? Thanks.
View 6 Replies
View Related
Nov 26, 2002
Hi Gurus,
We have a SQL 2000 DB1 ( publisher) which is replicated using transactional replcation onto the secondary server DB2 ( subscriber). we have identity columns on the DB1 and we created those tables with 'not for replication' clause. we skipped the following errors in the replication profile '2601:2627:8102:20598'. on DB2 ( the subscriber ) some records are missing in some tables . ( I verified this with record count differences for some tables in both the servers DB1 & DB2.
How to find out what are all the records missing & the cause.
I have listed the msrepl_errors table on DB1 , distribution databases all I'm seeing there are error code 8102 , unable to update identity column.
Any Idea is appreciated.
- Thanks
Jay
View 1 Replies
View Related
Mar 1, 2006
Hi
Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity????
I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.
Thanks,
Inon.
View 9 Replies
View Related
Nov 3, 2006
hello friends..
i aleays facing this problem while reporting....
if i want to show report for date range then i am not getting records for end date...why???
my report query was
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '10/31/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3
i am getting rows 15 here but when i fired this
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '11/01/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3
then i am getting rows 16
previous one i always missed records on 10/31/2006...is there any solution or i always add one day to end date and then get values??
please help me out
Thanks
View 4 Replies
View Related
Jul 20, 2005
I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
View 2 Replies
View Related
Oct 11, 2015
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
View 11 Replies
View Related
Apr 22, 2015
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location
1 4/1/2015 bld1
2 4/2/2015 bld1
3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])
And the results would be:
4/3/2015 bld1
View 17 Replies
View Related
Apr 25, 2015
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
View 10 Replies
View Related
Jul 20, 2005
Hello,We have a query which returns ~2.8 million rows. This same query isused in a DTS package, which exports to a text file. The number ofrows in this text file, however, is ~2.7 million rows (I'm rounding ofcourse.) So a good chunk of data vanished in the export it appears.Using SQL Server 7.0 on Windows 2000.Anyone see bugs w/ DTS text exports for very large amounts of data?Thanks,DF"Never eat more than you can lift." Miss Piggy
View 1 Replies
View Related
Oct 31, 2007
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.
Any help would be great, cheers.
View 3 Replies
View Related
Aug 13, 2007
I am importing records from a flat file to a database table. If a record is in the table but NOT in the flat file, I need to update a date column in the table.
Any ideas?
View 9 Replies
View Related
Mar 28, 2007
I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "". But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12. If @month is empty, I want all the records to be displayed, but nothing is. If @month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not? Diane
View 5 Replies
View Related
Jul 20, 2005
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Nov 6, 2014
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1
aaa7
bbb2
bbb5
bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0
aaa 1 1
aaa 2 0
aaa 3 0
...
bbb 0 0
bbb 1 0
bbb 2 1
...
and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
View 6 Replies
View Related
May 19, 2015
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter.
What i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:
select col1, col2, col3
from table A1
where col1 > 1000 order by col1
View 2 Replies
View Related
Jun 18, 2007
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View 5 Replies
View Related
Nov 23, 2006
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
View 3 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Dec 22, 2006
Hi Everyone,
When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.
Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.
Thanks
View 1 Replies
View Related
Feb 6, 2006
http://img96.imageshack.us/my.php?image=untitled5ia1.jpg
how come my SQL 2005 surface area configuration don't have the configuration for SIS and other settings? seems to be missing alot of settings...it only say SQL 2005 Express when I m using SQL 2005 enterprise....
View 2 Replies
View Related
Dec 8, 2006
I am attempting to import data into SQL 2005 (using the Tasks->Import Data wizard) and get as far as selecting a datasource. My source is ODBC and I am selecting ".NET Framework Data Provider for Odbc" but then get stuck on the connection string... I am attempting to import from 'Relativity Client' which as far as I can tell is an interface to a Cobol database, I have also had this problem importing from FoxPro - how do I come up with a connection string?
I have also tried to create a DTSX package, created the connection to the odbc source, tested ok, then when selecting the connection as a source (datareader source) I get the message 'Cannot acquire a managed connection from the run-time connection manager'...
I have also created a DTS package in SQL2000, upgraded to a 2005 package, this creates a file which after opening cannot be saved 'System.OutOfMemoryException', on my laptop with 2gig physical ram (1.3gig in use), 8gig virtual memory...
This is getting just a little frustrating as the SQL 2000 wizard fetches the data straight in from any data source I have ever tried with no complication whatsoever...
Unfortunately the first training course I can get on is in the middle of January. But I would prefer not to have to install SQL2000 and SQL2005 (SQL2000 for DTS, SQL2005 for reporting) just to get a few reports up and running.
What do I need to do to make SSIS import ODBC data? Or if someone can direct me to a good source (preferably with examples) of how to get SSIS working it would be greatly appreciated.
View 3 Replies
View Related
May 26, 2008
I have installed in my machine SQL 2005 with SSIS components, and the Visual studio 2005. The installation process was successfully and all components seem to be on the machine, but I did not find the shortcut to allow me running SSIS environment.
When I re-run the SQL setup the SSIS components is there but is unable to be used.
Any idea how to solve this issue?
Thank you,
Marian
View 10 Replies
View Related
Jan 31, 2008
I want to start the SSIS wizard to import data from a flat file, but I right click the DB > select Task and the Import and Export options are not there. Is there something I have to enable before the options show up
Thanks
View 3 Replies
View Related
Jan 18, 2008
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
View 4 Replies
View Related
Sep 18, 2007
Windows 2003(64bit) server, SQL 2005 Server(64bit), Oracle client 10G rel2 (64bit) is installed. But when I am going to create a datasource to the Oracle database the Oracle OLEDB Provider is missing, the only Providers avaliable is from Microsoft.
I have tried to install ODAC (64bit) with no result.
Anyone who have come across this problem and how do I do to resolve it?
BTW! Is there a way to try communication with the OLEDB provider from the commandprompt?
View 2 Replies
View Related
Jul 9, 2007
I want to skip running the SSIS data flow task when the source file is missing. We have a scheduler that copies the source file to the staging area. This SSIS package runs as SQL server job. So when a SSIS package fails due to missing file the remaining steps in the SQL scheduler won't execute. I want to handle the missing source file condition grace fully. Please advise.
Thanks in advance.
View 1 Replies
View Related
Feb 12, 2008
Hello all.
I am trying to import a flat file using the Wizard. That option does not show up. It shows up fine on my other machine. I installed 2005 Standard edition and they are still missing. I then uninstalled everything, rebooted and reinstalled Standard edition.
The flat file option is still missing. Any thoughts?
--Thanks
Joe
View 1 Replies
View Related
May 17, 2007
Hello All,
I have a package in which I have enabled "Package Configuration".
When I run the package i am sure that it reads the configuration file and executes the package correctly.
However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.
I have event handlers for OnError and OnWarning and both these are NOT invoked.
IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).
Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?
View 5 Replies
View Related
