I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this
View 7 RepliesI am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to 255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])
For example load 0.15 from Excel to varchar field in database and get 14.99999, why?
How just get 0.15 in varchar field?
I have 5 tables in Academy database. Like
Academy details tables, Academy Students table, Academy Student Parents table, Academy class Section Table, Academy Staff table.
I have created the tables and data into the database. And Now I need to prepare a Excel sheet to upload data into the these tables. How to prepare the Xlsx sheet and how to upload into database without using SSIS package.
As part of my data warehouse nightly build, I truncate my tables in mytarget database.As example, I find it is much quicker to do a bulk API load of 13Mrecords and to do an update/insert of 100K rows. I also drop theindexes before the builds and reindex after. Thats an aside.What I am wondering is how is this impacting the statistics? Do I needto update them?Not well versed on statistics and any data is welcomed.ThanksRob
View 1 Replies View RelatedFacing problem while loading date in MS SQL Server 2005 from excel file (csv format).
How to load the excel file data without changing the excel file (csv format) .
see the [Start Date] and [ Exp End Date] having values like this : " 2015/07/31"
I am working on an HR project and I have one final component that I am stuck on.
I have an Excel File that is loaded into a folder every month.
I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).
I then combine it with another table in a view.
I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).
Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.
I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.
So the data will look like this.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
The data is in one table labeled [EEMaster]. To test the count I have the following.
SELECT COUNT([PersNo]) AS HistoricalHC
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'
this returns the HistoricalHC for 2013 as 418.
SELECT COUNT([PersNo]) AS NumbOfTermEE
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'
This returns the Number of Termed employees for 2013 as 42.
I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from.
(so on Dec 31st this package will run and add |2013|418|42| to the next row, and so on.
My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?
View 3 Replies View RelatedI am trying to load data from Excel 2007 version into SQL server 2014 DB. I am getting below error" SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTION FROM CONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed".I have tried all options like changing Delay Validation is TRUE and in properties i changed runtime 64 bit to FALSE but still getting above error.
View 3 Replies View RelatedI am using SSIS to replace set of tables daily. One of the table has primary, unique, foreign keys and full-text index. Before truncating, I am dropping the foreign key constraints (to truncate the parent table), truncating the tables and recreating the foreign keys.
I have few questions:
1) Do I need to drop and recreate the unique key as well? (I am not dropping the primary key) - Unique key is identity column created just for the full-text indexing since it was mentioned that key on integer is better than key on varchar and my pk is a varchar.
2) Do I need to drop and recreate the full-text index or just rebuild/repopulate it every time the table is loaded.
This is the first time i am using full text index and I was able to learn a lot about it from the sites. I would like to understand the correct approach while loading the tables.
I am loading data using SSIS 2008 from a table in SQL Server 2008 DB to excel 97 sheet pre-defined with column headers. All the columns in excel is has 'Text' format property and the columns in the SQL Server table are defined as nVarchar. One of the columns has trailing spaces in few rows in DB but after exporting to excel 97, the spaces are gone. We need to retain the whitespaces in the column values. How can we do that.
View 3 Replies View Related
Hi,
I have reinstalled Office 2007 (to changre the license key)
after this, the data mining excel add-in failed to load.
the "COM add-ins" displays: "not loaded. a runtime error occurred during the loading"
reinstalling the add-in doesn't solve the problem
installing the 2008 version don't solve the issue too.
There is no other information, what can I do to solve the issue?
thanks.
Hi,
Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.
Please help us.
Ajay Kumar Dwivedi
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
We have build a powerpivotmodel that uses a couple of datasources. The user extracts on monthly base an excel file with 20 samples (randomly) from the model to check some aspects of the input. He can put Yes or No into a column if he agrees or not and fill in a remark field. Next this excel file is loaded back into the powerpivot model (in a nightly batch) and the Yes/No field and the remark field are added to the original data. If the user refreshes his excel sample file from the pivot, the remark and YN field will be filled with the values he added. So far so good. One moth later when he extracts the file for a new month a problem arises. Since the excel file only has data for the new month, when the PP-model is reloaded the data for the previous month will be lost. So we need some sort of incremental load for the excel file. Is that possible and is this the best way to handle this situation?
View 4 Replies View RelatedI have 2 different excel files file1 and file2. file1 should be loaded to table1 and file2 should be loaded to table2. Both of the files will have 1 sheet inside. Do I need to create separate excel source for file1 and file2? I mean file1 in one excel source and that will be connecting to 1 execute sql task. file2 in other excel source and that will be connecting to another execute sql task. Is this the way I should proceed or is there any looping should be done? I need to schedule this activity to run every week. So, I'll get new files every week with the same file names and sheet names. Do I need to consider anything for this requirement also?
I'm planning to do truncate and reload not an incremental load.
Hello,
Here's my need :
In one directory there's several excels file. Theses file have the same structure :
col : LOOKID, LOOKNAME, ASMPID, ASMPNAME, LOOKTYPE, LTYP_NAME, PAR_TYPE, PTYP_NAME, PARAMETER, VALUE.
The problem is that the cell format of the PARAMETER col. is different bewteen excel files. It could be date, numeric, ... The col destination in sqlservr database is Varchar(10).
I've created the ssis package with a ForEach Loop, and in the ForEach loop I've created a Data Flow Task.
In the data Flow Task I've created an excell source file (using excel file with col PARAMETER in date format) with an OLE DB destination.
When I launch the package on the same excel file as the one using to create Excel Source object it's OK, no errors, and data in the sql table are OK.
But when I launch the package on Excel file with col. PARAMETER in numeric format, there's no execution errors, but in the destination table the value of the PARAMETER col. is transform in date format.
I tried to change in Excel source object the datatype of the input PARAMETER col, but I've got some compilations errors. It seems that SSIS recognize automaticaly excel source data type col. But may be I did something wrong in the excel source settings ?
Is there a way to force the excel source datatype with varchar(10) ?
I've also tried to do the treatment with an script task but my vb.net knowledge isn't enought to do that.
If you have some suggestions, I'm listening.
Thanks
Luffy
I am trying to load a simple Excel file into a Database table and the SSIS Package is not loading any records beyond 3233 records. I am just surprised. I tried using the "IMEX=1" mentioned in some of the online resources but it didn't work. I am using an Excel Source, a Data Conversion Transformation and an OLEDB Destination in my package in SQL Server 2014 (which is pretty simple and straightforward).The Excel file I am trying to load can be found here.
And, here is my table structure.
CREATE TABLE [gov].[loan_limits](
[FIPS_State_Code] [varchar](3) NOT NULL,
[FIPS_County_Code] [varchar](3) NOT NULL,
[County_Name] [varchar](50) NOT NULL,
[State] [varchar](2) NOT NULL,
[CBSA_Number] [varchar](6) NOT NULL,
[code]...
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container.
View 7 Replies View RelatedI have multiple excel Files each has one sheet (With same column names) need to be loaded in a single table. I tried For each loop but couldn't succeed.
As I am new to SSIS. How to configure For each loop container for this...
Hi,
I'm using SQL 7. Is there a way I can allow data to be truncated? I'm trying to insert a 50 length column into a 40 length column. I need to truncate the data to prepare for exporting to a fixed field format.
Any Suggestions?
Thanks,
Denise
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies View RelatedWhen writing an INSERT statement, what code do I pass to the SQL database to automatically truncate a value to fit in to the database if it contains more characters than the field the data is being entered into?
View 7 Replies View RelatedHi,
I have an application that uses a Cache data base. The data is exposed to sql server via a linked server.
In the Cache database they have data columns that can be up to 32395 characters long. When I do a simple select query like this:
select Intervention_text from cwsavpmtest_live..system.Intervention_data
… I get the error message:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[Intervention_data].intervention_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
I figured out that I could get around the problem with an OPENQUERY like this:
SELECT *
FROM OPENQUERY(cwsavpmtest_live, 'select left(Intervention_text,32395) from Intervention_data’)
The error goes away, but I only get the first 255 characters of the data. I don’t really need all 32395 chars (I figure 4000 chars would be acceptable).
This is not a display problem in Query Analyzer. I set the maximum characters per column to 8000 on the Results tab of the Option dialog. Also I have a stored procedure that is called by Reporting Services and it is only sending 255 characters to the report.
The stored procedure has code that looks like this:
CREATE TABLE #Intervention_Data
(ID varchar(254),
FACILITY Int,
INTID Int,
Intervention_Text varchar(4000),
Intervention_Name varchar(50))
SELECT @QueryString =
'INSERT INTO #Intervention_Data SELECT * FROM OPENQUERY(' + @LinkedServer +
',''select ID, FACILITY, INTID, left(intervention_text ,4000), ' +
'Wiley_Intervention_ID from Intervention_Data where facility = ' +
cast(@FACILITY as varchar(10)) + ''')'
EXEC(@QueryString)
I have tried making the Intervention_Text column in the temp table both "text" and "ntext" data type with the same result.
Does anybody have any clues about how I can make this work?
Thanks,
Laurie
Note: When I link to the Cache database using Access, Access interprets the long fields as being of type "Memo" and I can see all of the data.
edited because tap and tab are not the same word.
Hi,
I'm trying to Service Pack 4 a fresh installation of SQL 2000 (a named instance called 'SQLSERVER2000') and get the error "Installation of the Microsoft Full-Text Search Engine package failed. (-2147467259) 0x80004005 Unspecified Error" towards the end of the process thus I cannot service Pack my fresh installation of SQL 2000. I have done this many times before on other machines, but its not working for me this time.
At http://www.dbforums.com/ ( http://www.dbforums.com/showthread.php?t=1198047 ) someone ran into this problem and mentioned to re-install Microsoft Full-Text Search. I have followed the article http://support.microsoft.com/kb/827449 but run into problems at step 2C. At step 2C it says to start the Microsoft Search service via the Services console, but Microsoft Search was nowhere to be found.
Going back a step to Step 2B it says that all registry keys that were previously-deleted are now available and the previously-deleted 'MSSearch' folder is recreated. I took a look, but both the keys and folder WERE NOT there.
It is a fresh OS installation of MS Windows 2003 Standard SP1, 768MB RAM and is running fine.
Can you please point me in the right direction here?
Brad.
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
View 3 Replies View Relatedhow to find who delete/Truncate the data from table ,because i don't have any trigger on the table.
View 5 Replies View RelatedHi everyone I have been trying hard to unserstand the way my isp does things sort of a communication gap I guess. The problem is I want to use the personal website... yes I know this is a SQL forum and that is what I am going to get at. I having a problems with the database aspect of the whole situtation, I can't creat a aspnetdb.mdf on their server because that name aready exists and I am assuming that it is the system and not a user that ows it. Also I can't name the other database personal.mdf either for the same reasons, so someone at the isp has had me creat a different name database during the install process of the application and that worked fine for a little while and now it does not work no more and I am getting old faster than I'd like. So here is my plan my plan is to wipe out the current database for the application on my workstation but first make a list of tables and then create a new database for that web application. So I ask is it feasible and what do I have to look out for and if it is not to much to ask what steps might take to accomplish such a task. I want to thank anybody that is willing to help me in this matter.
DKB
Can anybody provide me with the guidelines to install a fresh copy of SQL Server 2000 Enterprise Edition? The previous installation included a system account and it was on the same drive as the operating system. I remember reading this was not a good practice. Am I wrong?
thanks in advance
greets, im coding a few queries to a table. im storing sets of records into the table, each set of records will haev a different batch id. so basically 2 sets of records can occupy this table at the same time, and their batch id is the main key (with 2 other fields also being PKs). i want to compare the 2 sets in the same table and get the differences:
1. records that were added
2. records that were updated
3. records that were deleted
ive written queries for the added records, and the updated records but i cant get the query for finding deleted records. the logic looks good to me but im obviously missing something so i could use a fresh pair of eyes.
here is the table def:
Code:
CREATE TABLE [dbo].[UPCXREF_BATCH] (
[BATCH_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHAIN_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_CODE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITM_CATEGORY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UPCXREF_BATCH] WITH NOCHECK ADD
CONSTRAINT [PK_UPCXREF_BATCH] PRIMARY KEY CLUSTERED
(
[BATCH_ID],
[CHAIN_CODE],
[UPC]
) ON [PRIMARY]
GO
and here are the queries ive gotten so far
Code:
/* This section retrieves all UPDATED records */
SELECT 'U' as FLAG, b2.CHAIN_CODE as CHAIN_CODE, b2.UPC, b2.ITM_CODE, b2.ITM_CATEGORY
FROM UPCXREF_BATCH b2 INNER JOIN UPCXREF_BATCH b1
ON b2.CHAIN_CODE=b1.CHAIN_CODE
AND b2.UPC=b1.UPC
AND (b2.ITM_CODE<>b1.ITM_CODE OR b2.ITM_CATEGORY <> b1.ITM_CATEGORY )
WHERE b2.BATCH_ID='BTC0002' AND b1.BATCH_ID='BTC0001'
/* This section retrieves all NEW records */
SELECT 'A' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0002'
AND (CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001')
OR UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001'))
here was my attempt to get deleted records which looks like it makes sense but isnt working
Code:
SELECT 'D' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0001'
AND CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
AND UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
here batch 'BTC0001' is the older set of records that already existed and batch 'BTC0002' is the new set we just inserted into the table. am i missing something else?
I have just completed a default install of MS SQL Server 2005 x64 edition on a 1 processor Quad core, 8GB, Windows Server 2003 Standard x64 R2 machine in an Active Directory Domain. It has 2 arrays configured with 2) 36Gb drives mirrored for the OS as C: and 3) 147Gb drives in a RAID5 array as E: drive.
I am simply the server guy who was asked to install SQL server for some projects in the future. The company will be bring in people to to the dev.
I wanted to setup the server as best as I can and so I've been searching around for "best practices" for fresh installs.
Anyone have any suggestions on things to configure/setup to improve the performance of the default install?
In addition, I want to redirect the databases to the E: drive and separate them from the OS drive (C:). Does anyone have any information on doing this? I have searched and found a few articles, but thought I would ask first.
It is probably noticeable that I have limited SQL configuration skills.
Thanks,
A simple IT guy trying to build a system right
Is it possible for SQL 2005 Express SP2 to ignore the UPGRADE parameter during a commandline install? We would like to use one commandline to handle both a fresh intstall and an upgrade depending on which is needed.
Does it make any difference which order the commandline parameters are used?
Do I need to write something to check if an upgrade or fresh install is needed and then feed the appropriate parameters?
Thanks