Importing Data Using Import Service/DTS
Dec 6, 2006
Hi,
I'm using Sql server 2005 and I need to import about 15000 records from a txt file which is delimated using " | " character. Part of the problem is that the file has about 109 fields. The other part of the problem is that there aren't any headers defined. The field name and their respective data types are defined in a sepearte excel sheet. When I'm trying to import it using Sql Server 2005 import wizard, I would have to modify the names and the length manually and change the data types which is a time consuming job. My boss has claimed that he did it in Sql Server 2000 without using BCP utility and he got the table columns from the excel file (he didn't have to modify it manually somehow he import it) but he can't really remember !!!!!!!!
Now i'm really confused and a bit ashamed as well as he is around 62 years old.
Any idea of how it could be done using Sql Server 2005 or Sql Server 2000?
Thanks in advance.
View 5 Replies
ADVERTISEMENT
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
Dec 28, 2005
When Importing data from Access 2000 to SQL Server 2000 I get an error when the column's data type in Access is Memo if I try to set the column's data type in SQL Server to text or ntext. Here is the error that I am getting.
Query-based insertion or updating of BLOB values is not supported.
I have tried to change this to a varchar data type but the fields can be very large and some are over the 8000 limit.
Short of programmatically adding the data, is there another way to do this in SQL Server? I cannot use the Access upsize wizard.
Miranda
View 1 Replies
View Related
Oct 20, 2006
Hi All,
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Help!!!
View 8 Replies
View Related
Feb 5, 2007
Hi,
I'm trying to import text file (generated by UNIX - collation ISO LATIN 2) into the database using SQL SERVER 2005 Import and Export Wizard. I have got a problem with importing a decimal number, because in that column are not only decimal numbers (that's OK), but there are also spaces (not null, the column is filled by spaces and it looks like | |). When I'm trying import that file, then will occur the problem of truncation and import stops.
I can import that data by BULK INSERT, but I would like to import it by Import and Export Wizard at once without using subsequent conversions.
View 2 Replies
View Related
May 30, 2006
I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:
1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".
2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.
A further note: I created all tables in the SQL2005 database before running the Import.
View 4 Replies
View Related
May 5, 2006
I saw this post by dterrie in the Wishlist thread and I just wanted to second it:
"How about bringing back a simple dBase import. The SSIS guys are clearly FAR out of touch with reality if they think people who handle data no longer need to work with dbf files. I've seen alot of dumb stuff in my day, bit this is just sheer brilliance. I just love the advice of first importing into Access and then importing the Access table. Gee, why didn't I think of such a convenient solution. I could have had a V-8."
I've been struggling with this the last couple days and finally decided to import the dBase III file into Access and then import that into SQL Server 2005. Imagine my surprise when I discovered this was the current recommended method.
That's just ridiculous. Can someone tell me why they would reduce some of the functionality of SQL Server from 2000 to 2005? This was a very easy process in SQL Server 2000...
View 3 Replies
View Related
Jul 28, 2015
I try to import packages into integration service catalog, i am getting the master key error .
" Please create a master key in the database or open the master key in the session before performing the operation (error:15581)""
Version is SQl 2012
View 1 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
Jan 28, 2008
Hi,
I have report that in access 97. When i run that report every morning, it's update technician time, mileage and money. I want that report to be update every min like real time report in sql reporting service.
The main thing i want to know is how to import report from access 97 to sql reporting service. I am not really familiar with reporting services in sql, can you be little more specific where to set up time?
The report only update when i run query every morning. Queries and Tables are in access 97. When i tried to migrate tables with ssma, i wan not able to convert all of the query.
Thanks.
View 3 Replies
View Related
Jan 7, 2004
Hello:
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
Any advice will be greatly appreciated!
View 3 Replies
View Related
Sep 28, 2015
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
View 4 Replies
View Related
Jul 7, 2015
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
View 6 Replies
View Related
Jun 18, 2007
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
thanks
View 6 Replies
View Related
Jul 17, 2007
hi
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
ex:
ARIZONA
ALABAMA
STATE
but i m getting imported
as :
ARIZONA ALABAMA STATE
How to Format a single column while importing?
Regards
Raj
View 1 Replies
View Related
Mar 19, 2007
Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
Thnx :)
Wouter de Kort
View 6 Replies
View Related
Jun 11, 2006
I'm new to SQL and DTS packages. I am trying to import data from an excel spreadsheet to an SQL server table via DTS package. It seems that the excel task looks at the first few records in a column to determine the datatype for that column. If the first few records are text, the entire column is imported as text. If numeric, the entire column is imported as numeric.
There are about 25,000 records. In one field, the most important one, about half of the records begin with letters and the rest are all numbers. It is the subscriber ID field, and some subscriber IDs are all numbers, some are letters and numbers. The entire column should be imported as text. However, when I run the transform data task from the excel connection, none of the records that are all numbers are imported. I end up correctly importing only 13,000 of the 25,000 records. The rest are imported with the subscriberID field as <NULL>.
I tried using the CAST or CONVERT function in the SQL query, but get the error message "Undefined Function."
Can anyone give me some help? Thanks,
Jim
View 4 Replies
View Related
Jun 27, 2006
hello,
I create a txt file with a bash script, and i need to use it in a DTS package. But, i don't know how i can specify the type of my column. So in the transformations task, i have an error due to an incompatible type. what can i do to fix this error ?
thanks,
View 8 Replies
View Related
Nov 28, 2005
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment
into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN
The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View 3 Replies
View Related
Feb 12, 2005
I have created a DTS package for SQL Server, saved this as a VB file and upgraded this to the .NET framework.
It is now saved in a file called Shortages.vb.
What I want to do now is add this to an existing ASP.NET project and be able to call this DTS function by the click of a button.
Is this possible and how can I go about it? Can I just add it to the Click event of a button?
View 3 Replies
View Related
Mar 17, 2002
Hallo every one,
I', working for a school project.
The setuation is an old database that has to be repaird.
All the data from each table of the old data base are stored in different tabdelimited text files.
I designed a new database, but the tables are a little different.
When I try to import the data I get some errors, because of the changed tables.
An example of my new setuation is a new table thas as colums from 2 different tabdelimited text files.
My question is, how do I solve thos problem!
Do I have to use DTS?
What do I have to do.
tnx
icheron.
View 1 Replies
View Related
Jan 12, 2001
How can you can import results into table from system procedures.
For Ex. if you exec sp_spacesued, how do I import the results into the table.
Thank You
View 1 Replies
View Related
Sep 29, 2004
I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process. Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?
View 6 Replies
View Related
Dec 4, 2006
I am trying to import data from excel into my server, but get this error message:
Error during transformation 'directcopyXform' for row number 1. Errors encountered so far in this task 1. TransformCopy 'DirectCopyXform' conversion error: Conversion invalid datatypes on coulumn pair 19 (source column '*9' (DBTYPE_WSTR( destination column 'F19' (DBTYPE_R8)
Anyone get me in the right direction?
Scott
View 6 Replies
View Related
Apr 24, 2007
we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).
This method worked ok in DTS2000 and it works with Excel. Any suggestions?
Thank You
View 3 Replies
View Related
Mar 21, 2007
There's alot of discussion in this Forum concerning the importation of data into SSE.
I recently discovered that you can quite easily export tables directly from MSAccess to SSE. Simply 1) select the desired MSAccess table, 2) choose 'Export' from the file menu, 3) in the 'Save as Type' drop down, select ODBC databases(), 4) at this point, an 'Export' dialog with the name of the selected table appears, 5) click 'OK', 6) the Data Source Manager appears - go to 'Machine Data Source' and select a DSN that connects to SSE (you need to have set this DSN up before), 7) click 'OK' and and your table will be exported from MSAccess to SSE.
For whatever reason, the table you exported will always end up in SSEs 'Master' database tables. There is probably some work-around to correct this, but I havnt spent much time fooling with it.
Now, MSAccess has a very good parser for importing several types of external data, including Excel worksheets, CSV files, and text data. However, to get a satisfactory into MSAccess, you may need to edit your data files. Then, to import the data, from MSAccess select 'Get External Data' -> 'Import' from the File menu and just follow the instructions.
BTW, there is alot of confusing discussion concerning the ability of SSE to use DTS. Some have said that it is not supported, only in the full-blown version. Others have suggested you can download the DTS application from Microsoft and use it with SSE although it does not show up in the SSEMS directory tree.
I downloaded the file 'SQLServer2005_DTS.msi' and tried to install it. It appeared to install OK but I cannot find it anywhere on my machine. Weird, eh.
While most of this thread is in the way of a comment, I have a couple of questions:
1 - Is there some way to connect a machine DSN to a database in SSE that is other than 'Master' ?
2 - Are there better ways to import data into SSE ?
3 - Is there some way to move or copy a table from one SSE database to another ?
BTW, please do not respond to this thread by posting some link. Many of the links I have attempted to follow from this Forum are either irrelevant to the posted problem or are no longer available. If I wanted that kind of help, I could use Google or go to the library. Try to answer the question(s) directly. If you don't know the answer, say so, or at least, don't answer at all.
View 13 Replies
View Related
Aug 30, 2007
With SQL 2000 there was an Import/Export facility for importing data into a Sql database. Could somebody tell me how to import an old database which could be in Csv text or Paradox into my new SQL 2005 tables. Thanks
View 4 Replies
View Related
Nov 9, 2007
I have created an application that uses the login, create, etc login components in .net. How hard is it to convert all my old users, passwords, usertypes into the new tables. It almost looks like I have to do them by hand and created a new guid(userid), along with the same guid in the aspnet_usersinroles and aspnet_Membership. Is there a script to do this programatically?
View 1 Replies
View Related
May 23, 2008
Hi i have an excel spreadsheet in which I want to take the data and put them in a table, the table and excel speadsheet have the same unique-ID, what i need to do is retrieve the extra fields of the excel spreadsheet and match them up with the table. Is this possible, if so how?
View 6 Replies
View Related
Feb 3, 2006
Here is the scenario: I have an excel spreadsheet that contains 182 columns, and I need to move this data into a semi-normalized database for reporting. The SQL Server database schema has 11 tables. Some of the tables are going to use identity columns for their PK, other tables are using a value that comes from this spreadsheet for their PK values.Anyway, I have never done a DTS package of any significance before, and know I most likely need to write some VBScript to handle sticking data into the proper data tables, etc.I am just hoping someone can point me at a good resource, give me an alternative means of doing this (this is a process that will need to happen whenever a new Excel spreadsheet is dropped into a folder or on a schedule, either one). I would love to write some C# code to handle these things, but a DTS package would probably be the best, I just don't know where to start.Thanks,
View 2 Replies
View Related
Mar 23, 2006
Hi All,I'm coming from using MySQL, and in their dialect you could pull data from one table to another using the following: INSERT INTO Table1 (fname, lname)VALUES( SELECT fname, lname FROM Table2 )Let's assume Table1 is a simple table with the fields ID (PK/Identity), fname, and lname. This query would grab all the first and last names out of Table2 (fname and lname fields) and insert them into Table1, generating the ID for each new row.How would I do this in T-SQL?
View 1 Replies
View Related
May 19, 2004
I am building a aspx/c# application with SQL Server 2000 backend. Now i want to have the option for "Importing" the data into one of the tables in my database.
The source file for the import is a text file , CSV format. I want the users to click on the "Import" button placed on my webform and supply the souce file and the data should get imported into the SQL Server 2000 database table.
I want to know the various ways to implement this. Is it possible to invoke the DTS and then DTS will itself guide the users do the import? or if i need to write a SQL query , what would that be like??
View 2 Replies
View Related
