SSIS Drops Rows
Mar 7, 2007
I've written an SSIS package which has two DataFlow tasks running simultaneously, importing the data from two separate ASCII files into two staging/prep tables: one Tasks imports records into an ORDER table while the other Task imports the ITEM-level records into an ITEM table.
Starting several days ago, the ITEM DF Task began importing a partial set of records even though the ASCII file clearly showed more records were available.
For example, in today's processing, here is the message from SSIS: << [ITEM Ascii File [1695]] Information: The total number of data rows processed for file "\webserv01globalscapeusr vr logstandarditem.txt" is 217361. >> yet i only found 10,401 rows -- and they all appeared to be the bottom 10,401 rows of the incoming ASCII file.
I cannot see any other untoward messages about SSIS encountering a problem.
There are no Error Output restrictions on the ASCII file.
The ASCII row preceding the 10,401 rows i DID get looks OK.
Can anyone suggest a possible place to look which might explain this "jumping" over the nearly 207,000 rows?
Thanks very much.
Seth J Hersh
View 7 Replies
ADVERTISEMENT
Jul 19, 2006
We are using BCP in a Process task. The value for the path to BCP's error log requires double quotes around it. We initially put this value in a configuration file, and that worked fine. Yesterday, we eliminated the config file and tried to use the "Set Values" tab of the SSIS SQL Server Agent job to pass in this value.
The package variable was assigned the value of the path -- all except for the leading double quote. The closing quotation mark was included in the value.
We tried adding a second double quote at the beginning of the value, but that caused the job to fail immediately.
For now we will work around this problem by putting the quotes around the value in a Script task inside the package.
Has anyone else encountered this problem?
Thanks,
Ron
View 2 Replies
View Related
Aug 17, 2007
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
View 4 Replies
View Related
Apr 6, 2006
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
View 1 Replies
View Related
Oct 9, 2007
We have a database that we are preparing to set up Merge replication on. We often make schema changes via T-SQL, many of these changes are made to tables on which an ALTER TABLE statement will not do (rather the creation of a temporary table, copying of the data, deleting the original table then renaming the temp table).
My question is how this will affect Merge replication. I have not been able to find anything that is very clear on this. From what I gather, if a table needs to be dropped that is participating in a merge replication, I need to go through the manual process (manual, as in calling the necessary system stored procedures) of removing the the article from any publication (and subsequent filtering), make the modifications, then re-add it to the publication and filtering.
Is this correct? If so, a new snapshot needs to be created, correct? If so, I have a follow-up question regarding that snapshot.
If a new snapshot needs to be created, what happens during replication/synchronization? Meaning, since it is a new snapshot, does the client (subscriber) see the whole thing as new or is it smart enough to recognize that only that one table i have changed needs to be synched?
I am quite new to replication, as you can tell, so please forgive the rambling. I ask these questions because I have heard different answers on both questions...so I would like to get the correct answers.
Greatly appreciated...
View 4 Replies
View Related
Jan 12, 2006
Is there a way to have SSIS monitor a folder for file drops? I have been unable to determine which object/task to use for this. We need the ability to have it monitor for files being dumped by other systems, pick those up and then process them. Thanks for your assistance.
- DeKlown
View 6 Replies
View Related
Jul 16, 2007
We've implemented mirroring between two identical servers. Sporadically, the mirroring session will drop and the ERRORLOG reflects the errors below at the exact time the mirroring session becomes suspended. We do not manage our back end network since we use a dedicated hosting environment at a remote location. Is this issue solely caused by network connectivity issues, or are there other factors at work?
2007-07-16 04:24:37.24 spid23s Error: 1453, Severity: 16, State: 1.
2007-07-16 04:24:37.24 spid23s 'TCP://192.168.215.92:5022', the remote mirroring partner for database 'evestment', encountered error 1204, status 4, severity 19. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.
2007-07-16 04:24:48.46 spid23s Error: 1479, Severity: 16, State: 1.
2007-07-16 04:24:48.46 spid23s The mirroring connection to "TCP://192.168.215.92:5022" has timed out for database "evestment" after 10 seconds without a response. Check the service and network connections.
View 1 Replies
View Related
Jul 19, 2006
XML looks like this:
<?xml version="1.0" standalone="yes" ?>
<hist key="ABC">
<r date="2006/04/21" time="08:53:04" seq="1029">123</r>
<r date="2006/04/21" time="09:21:40" seq="1613">123.25</r>
<r date="2006/04/21" time="09:37:22" seq=" 89">194.21</r>
<r date="2006/04/21" time="09:37:22" seq=" 91">194.21</r>
<r date="2006/04/21" time="09:37:22" seq=" 93">194.22</r>
<r date="2006/04/21" time="09:37:22" seq=" 95">194.22</r>
</hist>
In SSIS it reads all the date, time, seq etc but I lose that super critical "key" in the outputted data flow. If I look at the XSD in visual studio it shows 2 tables related via a "nested heirarchy"... "hist" and "r" with "key" being an attribute in the hist table and all the rest as attributes in the "r" table. However in SSIS it only shows one table so I can't get at that key :(
Any idea how to remedy? I keep messing around with the XSD file but that hasn't gotten me anywhere thus far...
Thanks
View 9 Replies
View Related
Mar 23, 2007
I ought to know how to do this, but it escapes me at the moment. I need to write an insert statement for a table that will be based on a complex select query. The select query may return rows that are already in the target table. In that case, I don't want duplicates created, but I don't want the query to error, either. I can't remember how to set that up.
View 3 Replies
View Related
Apr 28, 2008
My server (SQL 2005 SP2) typically runs with a procedure cache usage of about 92% or higher... lately it seems like at some point in time during the day it just drops to anywhere between 50% and 65%... with this comes horrible server performance and many snowball effects. If I clear the procedure cache it will go up only about 10% for a minute or two. The only way I can get it to recover completely seems to be restarting the SQL service. Then it will be fine till the next incident. The database is a read only (not set to read only but no updates other than replication). and the same SPs are run over and over and over throughout the day. also did notice that the compiles of the SPs goes up drastically at this point also. not sure if this is part of the cause or part of the effect.
CPU is normal. response from anything (even sp_who) is slow.
i do not understand the way procedure cache works completely so I thought I would ask for some direction.
Any ideas where to look or where to start???
Any thing I can do to catch this when it happens would be great.
thanks a head of time.
View 34 Replies
View Related
Dec 31, 2003
Hi all,
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success.
Anyone have some info on this?
Thanks.
View 8 Replies
View Related
Aug 28, 2007
Does anyone have a good strategy or technique for preventing database drops in SQL Server 2000? I know in 2005 DDL triggers rock, but in 2000 what can you do to audit who drops a database why keeping the same permissions intact.
Jason
View 3 Replies
View Related
Mar 12, 2007
We are having some unusal problems with an upgrade to SQL Server 2005. I've come across two other people who have had this type of issue but I haven't found a resolution. We are running SQL Server 2000 in production and we have installed SQL Server 2005 on new hardware. We have been working for about a week to verify that all of out existing applications will work properly with SQL 2005.
We have several old VB applications that open a connection to the database and reuse it for days on end. We can test these apps against the production 2000 server and they run fine, when they connect to the 2005 server the connections work fine for 5 - 15 minutes then we get an error trying to execute a command and are told that the connection doesn't exist. (One interesting note, there is only one line of code in the whole program that connects to the database but we have seen 3+ connections from the app when we check in Management Studio for SQL 2005.)
Just to get though the tests we put in some error handling to reconnect if the connection is lost. When we do this the application ends up re-connecting only once, the new connection never has a failure even after hours of testing and we see a 300% performance improvement.
We can reliably cause this problem by stopping the sql server, starting it, waiting a few minutes, then running our app. After one run of the app the problem seems to go away for a while and we can't re-create it nearly as consistantly. Only occasionally will the initial connection fail and every time it does it will be after 5-10 minutes of activity.
Both machines are connected to the same switch and we have ruled out networking issues. We are only seeing this problem with VB6 apps, the VS2005 apps run great. We are looking for the cause of this connection behavior and a fix for it because we don't have the time to fix code in dozens of VB apps because the connections are not stable. Granted the practice of holding a connection open isn't good to begin with but with legacy code you have to work with what you're dealt.
Any thoughts as to why ADO connections from VB6 to SQL 2005 would not be stable?
View 4 Replies
View Related
Sep 11, 2007
Please forgive my ignorance, I am by no means a SQL Expert, but have encountered a strange issue.
I have 6 SQL Servers, Primarily SQL 2005 (one older SQL 2000) all loaded on Windows Server 2003 SP1.
We use the servers for a proprietary database that we created which is the backend to a software package we sell.
The issue I have is: We have added a Security account to the servers, and in one case we have granted rights for this account to the TempDB system database. However, whenever we restart this server SQL drops this user account, thus severing connectivity for the app that is relying on that account.
I have set the account as DB-Owner etc, but nothing sems to keep it on re-start.
Any input would be greatly appreciated.
View 4 Replies
View Related
Oct 22, 2015
I'm using SQL Server 2012 Bi edition.
I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.
However, when I deploy the model to the server it is only deploying 76,500 records for those tables.
Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.
View 3 Replies
View Related
May 22, 2006
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
View 1 Replies
View Related
Jul 28, 2015
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
View 0 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Apr 6, 2008
I have 3 input sources, date, name and text. What SSIS transformation could I use to merge these 3 rows into one single row. Example:
10/16/2007
John Doe
Text.......................................
Result wanted :
10/16/2007 John Doe Text.......................................
Thank you so much fro your help.
Tara
View 9 Replies
View Related
Aug 15, 2007
Hi,
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.
Your assistance will be highly appreciated.
View 4 Replies
View Related
Feb 28, 2008
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.
I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.
I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?
I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
The design of the data flow at the moment is:
..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4
any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
View 7 Replies
View Related
Sep 28, 2006
Hi *,
I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?
Thanks!
View 1 Replies
View Related
Nov 20, 2007
I have a Dataflow task with oledb source that is using SqlCommand to retrieve data and oledb destination to write the source output to a table. I have access to both the source and destination databases.
The problem is the destination component is not writing any rows to the destination table eventhough the Source component is returning rows (I can see them in the Preview and the source database table as well).
I'm using "Table/View Name from Variable" for destination.
The Package executes without any errors but there is no output.
Any ideas?
Thanks.
View 7 Replies
View Related
Jul 3, 2007
I have an SSIS package that imports from an Excel file with data beginning in the 7th row.
Unlike the same operation with a csv file ('Header Rows to Skip' in Connection Manager Editor), I can't seem to find a way to ignore the first 6 rows of an Excel file connection.
I'm guessing the answer might be in one of the Data Flow Transformation objects, but I'm not very familiar with them.
Any pointers would be greatly appreciated.
Eric
View 12 Replies
View Related
Jul 17, 2007
Hi Guys,
Yet another question again on the issues with SSIS. I have a package now which is working fine.
The package consists of a control flow and i have 2 DF tasks which are unionall first and then saved into a sql server destination.
It's fine up to this point but i've just been notified that i would need to generate 2 files based on different values after i combined the data from 2 sql server DF tasks.
My question is how can i know the rows which are being saved on this sql server destination.
I have a primary key which is an autoincrement column.
Thank you
Gemma
View 45 Replies
View Related
Feb 1, 2013
I have an excel file with following data:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
123 Agency MA 300,000 Mickey Mouse
Rogers Inc MA 50,000 Mike Greenwell
I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:
Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
Is there a way to accomplish this in SSIS?
View 2 Replies
View Related
Jul 31, 2007
I finally put together a SSIS package that takes a Text File and successfully imports its data into the right table. My question is, where in the package's properties can I find the option to Delete all rows from Destination Columns prior to Importing. I have looked everywhere in the Package Explorer for this setting. Thanx in advance.
View 3 Replies
View Related
Nov 16, 2015
While handling errors in a flat file I am routing the rows into the excel sheet to business for further processing. I need to send a mail attaching the excel sheet which contains the errored-out/In-Complete definitons. The problem is I need to send a mail if and only if there is at least one row in Excel sheet. How Can I count the rows exist in excel sheet? and send the rowcount to a variable and use that value of a variable to send a mail?
View 5 Replies
View Related
Oct 23, 2007
Hi,
I want to pull sample records lets say 1000 rows only from oracle database to sql server. Is there any option in ssis to limit the number of rows?
View 4 Replies
View Related
Aug 14, 2006
Hi,
I have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.
Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.
This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.
Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?
Regards
/Tomas
View 3 Replies
View Related
May 11, 2015
I need to do something like this in SSIS:From one SQL table I need to get some id values, I am using a simple sql query:Select ID from Identifier where value is not null.I've got this result:As a final result I need to generate and set a variable in SSIS with the final value:
@var
= '198','120','ACP','120','PQU'
Which I need to use later in a odbc expression.How can I do this in SSIS?
View 4 Replies
View Related
Jul 8, 2010
I'm doing a group by in an aggregate transformation. I have say 6 columns in the output and I'm grouping on all of them - how can I get duplicate rows in the output? If I do the same select and group by in SQL on the source data I don't get any duplicate rows. In fact out of 6000+ rows I only get 2 duplicates.
View 7 Replies
View Related
Nov 22, 2007
Hi all,
Can you help me to resolve my problem ? I have to do a simple daily backup system. Source : Flat File; Destination : SQL Server. I want to use the Slowly Changing Dimension component to backup only the new and updated row from my source (Flat File) and put them into SQL Server.
But how can I do to detect deleted rows from my source ?
Any suggestions ?
If it's not clear enough, please ask for more details !
GO
View 3 Replies
View Related
