I am in the process of importing data that is in a text format to the
sql server, I want to add couple of fields and insert general data in
the fields added, this data is going to be similar to all the records
imported. Your help in this regard will be greatly appreciated.
I have a table with 201 columns . I am importing 200 columns from a file using DFT. I want update the 201th column with the fileId of the file that just imported. I am storing the fileId of the file in varFileID .
How do I go back and update the 201th column ( column name sfileId) with the varFileID value?
I can use Execute SQL Task but how will I know it's the records of the files that I just imported not other rows.
I am trying to manipulate the data being imported from another data source. See below:
I need to make an IF THEN statement: If KeyDate =< 01/01/2013 THEN STATUS = 'Disposed'
Notice I am adding data in the last column as everything is 'Active'
SELECT dbo.UserConfig.Id,
dbo.UserConfig.ServerConfigId, dbo.UserConfig.DisplayName,
dbo.UserConfig.UserName, dbo.UserConfig.MailboxSMTPAddr,
dbo.UserConfig.OverrideSMTPAddr,
[Code] ....
Hi,
I have imported some tables from msaccess but as the datetime field from access is different from SQL Server I had to change all columns type from datetime to varchar...
Now in SQL Server i'm trying to convert this data into datetime, and I'm using cast(field as datetime) and sql give me this message..."The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
how can I select these rows that are giving me errors ??? OR is there a way to convert this data ??
I don't know if in this data it will give the error... but as the table is too big...I can't post everything here...
------------------------------- EDIT
I got the problem...
I have data like 4/11/2006 9:23:19 AM and others like 11/21/2005 6:02:13 PM
So the first one 4/11/2006 9:23:19 AM it converts without problem, but wrongly...cause the month is "4" and not "11"
the other one 11/21/2005 6:02:13 PM it tries to convert the month as 21 and not as 11....
How can I solve this ??
I'm importing floor machine data from SQL7 into SQL2005 using SSIS. I import the SQL7 data into a SQL2005 master table and then attempt to match the import data with the data in a current table for either update or insert of new machines. The SQL2005 master table was imported from a SQL2000 database. When I run the first Lookup import, it does not recognize the PK matches between the SQL7 import and SQL2005 master and imports all the SQL7 as new machines. The first Lookup branches to a second lookup that checks for changes in the SQL2005 master. When I run the package a second time, the second Lookup treats all the records as updates when it gets to the second Lookup, but should treat these as found. Any suggestions as to why this process is not working properly would be appreciated. Is there a way I can embed a picture of the process from SSIS in this post? Thanks
View 3 Replies View Related After designing a SSIS package in Visual Studio 2005 that had two connection manager defined to keep the password. After I deployed the package to a file system. I then Imported the .dtsx file after making a Integration Services connection in Sql Server Management Studio. When I tried to run the package it failed when it tried to make the connection. When I edited the connection manager connection string and added the password and the package ran fine but it does not retain the password!. I need to have this package scheduled to run daily so I need to know how to have the package keep the password in the connection string. I have seen other posts on this issue but not seen a good solution. Could someone point me to the proper MSDN article that would explain how to implement this ? Is it a SQL Server configuration issue or a property in Visual Studio SSIS design time ?
thanks.
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
Thanks again in advance
RB
Env: SQL Server 2012
I'd like to import some tables into MDS entities.
So I found MDSModelImport Program.
Importing is worked. And I confirmed to imported columns of tables as leaf Attributes.
I tried to insert data. But In Explorer Leaf Attributes are not shown except Code and Name.
So I couldn't insert data. In Excel also it does.
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies View RelatedWe have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies View RelatedHi,
I have imported 3 DTS from SQL 2000 to the SQL 2005 server. The wizard went fine, everything is ok. When I close the wizard window, I cannot see any of them. If I reimport it, it asks confirmation to override it. Where are the 3 DTS in the management Studio if they are not under Legacy DTS?
Thanks
I am using the following SQl statement in a DTS vb file.
The data is coming from an Excel spreadsheet and being put into a SQl server table.
oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
I want to validate the data being put into the table to ensure no duplicates records are entered UNLESS the record has changed in any way.
E.g. If record 1 had a column called "NumberOne" which changed to "Number1" change this information and move the original value to another table.
I installed SQL 7 on a new computer and imported DB from another SQL 6.5.
Tables were imported but index and SPs were not.
I had to script those objects and run it on SQL 7.
Any shortcut to import all objects directly?
Again I was importing not upgrading.
Hi. I am working on a dynamic website and backed up the data from our database (SQL Server) into a .bak file from our server. I want to develop the site locally on another machine (doesn't have SQL Server, but MSDE) using the database so I restored the database backup using RESTORE DATABASE FROM... (in the DOS prompt). I found a tutorial how to do this at
http://www.popkin.com/customers/customer_service_center/tip_of_the_week/01_19_04/tipofweek.htm
It did the restore successfully (or so it said) but all that it restored was the database structure (table structure, user information, etc.) but not the actual data in the database. I am using Specify (www.specifysoftware.org) as a database manager.
Could anyone help me out? Can I not go from SQL Server to MSDE? This doesn't seem to be the case because the structure restored okay.
What should I do to correct this problem?
Thanks for your help in advance!
Nik
I am developing a DB with others in my group. When I import tables created on other servers to my server, the primary key and other properties do not import with the tables. Can anyone explain why this is happening? Is there a setting I have over looked?
View 3 Replies View RelatedDear All,
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End.
Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel.
Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel)
Please suggest me some idea.
Thanks
We have an OLTP application with multiple one to many tables relationships that are edited client side. For Example the primary table has descriptive components. One of the multiple table have things like parts and quantity ordered linked by a record number back to the primary table.
When our client transmits their transaction, they actally running a batch job which sends all the client side tables to the SQL Server and then we are trying to issue a stored procedure server side to push this data into our 'dbo' owned server side master tables.
Our problem is generating the fully qualified table name for the from clause.
We have tried declaring a variable but the stored procedure won't compile. Tried user, user_name().
Any solutions would be greatly appreciated.
Has anyone import into SQL Server a text file generated by DB2?
the DB2 files has bunch of weird characters in it that look like they are used for formatting...as opposed to actually being data.
Also, SQL can't find a row terminator.
I received a cobol declaration copy book for the data, but I have no idea what to do with the special characters.
Any help would be appreiciated.
There's a sample of the copybook...
* COBOL DECLARATION FOR TABLE @TNJ00.PL_JUDGM_ATTORNEY *
************************************************** ****************
01 DCLPL-JUDGM-ATTORNEY.
10 PJATY-ATTORNEY-KEY.
15 PJATY-JUDGMENT-ID PIC X(14).
15 PJATY-GROUPING-CDE PIC S9(4) USAGE COMP.
15 PJATY-ROLE-CDE PIC X(1).
88 PJATY-CREDITOR VALUE 'C'.
88 PJATY-DEBTOR VALUE 'D'.
88 PJATY-TRUSTEE VALUE 'T'.
15 PJATY-GROUP-SEQ PIC S9(4) USAGE COMP.
15 PJATY-ENTRY-SEQ PIC S9(4) USAGE COMP.
10 PJATY-NAME-PREFIX PIC X(4).
10 PJATY-LAST-NME PIC X(25).
10 PJATY-FIRST-NME PIC X(12).
10 PJATY-MIDDLE-NME PIC X(12).
10 PJATY-NAME-SUFFIX PIC X(4).
10 PJATY-CITY-NME PIC X(25).
10 PJATY-STATE PIC X(2).
10 PJATY-ZIP-CDE PIC X(9).
10 PJATY-TELEPHONE PIC X(10).
10 PJATY-LST-MNT-DTE PIC S9(9) USAGE COMP.
10 PJATY-REC-LOCK-CDE PIC X(1).
10 PJATY-ALT-NAME.
49 PJATY-ALT-NAME-LEN PIC S9(4) USAGE COMP.
49 PJATY-ALT-NAME-TEXT PIC X(75).
10 PJATY-ADDR-LINE-1.
49 PJATY-ADDR-LINE-1-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-1-TEXT PIC X(50).
10 PJATY-ADDR-LINE-2.
49 PJATY-ADDR-LINE-2-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-2-TEXT PIC X(50).
10 PJATY-ADDR-LINE-3.
49 PJATY-ADDR-LINE-3-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-3-TEXT PIC X(50).
************************************************** ****************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 20 *
************************************************** ****************
HiI have a script that uses bcp to import data from an ascii text fileinto SQL tables. The french characters are not copied properly. Theyare converted to letters of the alphabet. I tried to change all thefields to nvarchar instead of varchar and nchar instead of char, but Igot Greek characters instead.How can I fix this?Here is some code:--------------------------CREATE TABLE [dbo].[1_HLGT_HLT_COMP_f9.0] ([hlgt_code] [int] NOT NULL ,[hlt_code] [int] NOT NULL) ON [PRIMARY]GOPRINT 'HLGT_HLT'DECLARE @s as nvarchar(300)SET @s='bcp MedDRA..[1_hlgt_hlt_comp_f9.0] in ' + char(34) +'F:MedDRA9.0FrenchMedAsciihlgt_hlt.asc' + char(34) + ' -c -t' +char(34) + '$' + char(34) + ' -r$ -e' + char(34) +'F:MedDRA9.0Frenchlogshlgt_hlt.err.txt' + char(34) + ' -b250 -m50-SDEV -Usa -Ppassword -h' + char(34) + 'TABLOCK' + char(34)EXEC master..xp_cmdshell @s
View 8 Replies View Related
Hi All,
I am importing data from an oracle db and one of the columns is a nclob. I would like to truncate the value if it is more than a certain length. I want to use a derived column transformation. I cannot use the len and substring functions because it is not a string. I could convert it to a unicode string and use those functions, but I am afraid there is a size limitation (4000 characters) on it. Is there smth I am missing? Can anybody please suggest any ideas?
Thanks a lot.
I have a file I'm pulling from another type of database into an Excel spreadsheet and then using my dtsx package to import the spreadsheet into my SQL database. The problem I'm having is that one of the fields coming out of the database to the spreadsheet has the thousands seperator in the field that I want to use as a numeric field without the ",". Right now I have a macro that I run on the spreadsheet to reset the field to straight numbers without commas before importing it, but would like to configure my Integration package to do it automatically.
Any ideas would be appreciated.
Thanks in Advance
I have used the wizard to import a DTS package from a SQL 2000 server to our new SQL 2005 server and need to make edits to reference the new server and database. I am able to see the package within the SQL Management studio under Integration Services, but I cannot find it in the Development studio?
What am I missing?
Thanks
I used a DTS package to import data from one database to a new database (different servers) and it seemed to work fine with the exception that it lost all the passwords. I got one error telling me that for security reasons, it left all passwords NULL. Sure enough - they are now all blank.
Ideas - any good articles with quick fixes ???
Thanks a bunch - I am just getting into a lot of unusual problems this week.
Nancy
By the way - what happened to the SQL*Pass message boards ??? Nobody visits there now....
hi, I am importing data daily to many tables, I want to keep track of the #of rows for each import process. I already have created a trigger as follow:
CREATE TRIGGER tr_bcp_log ON dbo.A
FOR INSERT
AS
declare @name varchar(30),
@row_count int
select @name=name , @row_count= @@rowcount
from inserted
insert into bcp_tracks (name,row_count)
values(@name,@row_count)
GO
The problem is that I am getting a row for each inserted row in table A.for instance if I have 500 rows in table A, I will get 500 rows in the log table like this
table_name,#of rows
A 1
A 1
A 1
etc up to 500 rows for table A
This is not what I want, I want to capture the num of rows for every bcp process , so in the log table I want to see the following :
table_name, #of rows
A 500
B 600
C 450
A 250
etc
Any help ?
thanks
Ali
I have a stored procedure which will run automatically. I've got try...catch code in the procedure, but I found a bug with the code where if there are any import errors, it doesn't recognize that that there was an error and it runs through the try code as through there was no problems. (I reported the bug).
I added some code using @@rowcount to check if there were rows imported, and if not, it moves the data file to a error folder so I know there was a problem with the import. But this only checks if at least one row was imported, not if all the rows in the datafile have been imported. (i.e. if the first row imported correctly, and the second did not, it still sees it as successful).
The problem is some of the data files have only one row to import and some have multiple rows. Is there a way to count the number of rows in the datafile, then count the number of rows imported, to verify they are the same number imported?
Thanks,
Laura
Hi there,
Today I tried out Integration Services and after a couple of hours of confusion I am impressed by the power of this product. I was wondering if I might get some help on the 'best practice' for the following requirements:
I have a foreach container that scans a directory and calls a data flow task for each file in the directory. The files import to the database fine, but I want to modify the procedure so it only imports files that have not yet been imported. There are a couple of scenarios:
- a file is created in the directory
- a file is modified in the directory
In both cases I want to insert (or reinsert) the file. How can I modify my package to accomodate this behaviour? Storing the filename is an option, but I am not sure how to also bring in the file creation/modified dates.
Is this the right approach?
Any other ideas?
Cheers,
Alex
I have a stored procedure which will run automatically. I've got try...catch code in the procedure, but I found a bug with the code where if there are any import errors, it doesn't recognize that that there was an error and it runs through the try code as through there was no problems. (I reported the bug).
I added some code using @@rowcount to check if there were rows imported, and if not, it moves the data file to a error folder so I know there was a problem with the import. But this only checks if at least one row was imported, not if all the rows in the datafile have been imported. (i.e. if the first row imported correctly, and the second did not, it still sees it as successful).
The problem is some of the data files have only one row to import and some have multiple rows. Is there a way to count the number of rows in the datafile, then count the number of rows imported, to verify they are the same number imported?
Thanks,
Laura
hello everyone,
i have few fields that contain foreign characters with diacritic marks which are not getting imported correctly.
below is the import format:
- File type: ASCII
- Row delimiter: carriage return and line feed {CR/LF}
- Column delimiter: Tab
- Text qualifier: None
Please advice.
Here is the errors i'm getting:
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Country_str_local_long_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Country_str_local_long_name" (37)" failed because truncation occurred, and the truncation row disposition on "output column "Country_str_local_long_name" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "L:Country.txt" on data row 6.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Country_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)