I am new to this forum, but it looks like you all know your stuff.
We have an Access database that we have attempted to import into SQL server. Everything comes on over fine except for the date fields--we set them to long and all of that--but they all show up offset by two days (+2 from actual or previous unimported value). Any suggestions?
I have posted to several forums but nobody has had any comments. Please help.
--the Desperate Fat Kid
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies View RelatedHelp!I have a table that has datetime format field, I exported the table toa csv while I dropped it and tried some other data, but now sqldoesn't recognise the date format for importing, heck I don't!The dates look something like:40:58.1Whick means nothing to me, or any of us here for that matter...Any ideas?John
View 1 Replies View RelatedI have a DB2 database with 300 tables that I need to pull over to a SQL database. I really want to make the import/export wizard work for this because of how much time it will save me, but the import fails when it hits a table that contains an invalid date for SQL Server (< year 1753). Apart from going in and adding a derived column task for EVERY ONE OF 300 TABLES, is there anyway I could automate this process either in the wizard or some other way? I just want to set all dates out of range to null.
Manually setting up each table import -- 10 minutes each table x 300 tables = 3000 minutes = 50 hours.
Using the wizard to import all tables -- 10 minutes.
That's gonna be a really tedious 50 hours....
Any ideas?? Appreciate anything you got!
chigasakigaijin
I'm having a hard time trying to transform a date that comes in for import in this format: 2015-04-08T18:00:48.000Z to DT_DBTIMESTAMP, I get potential loss of data error. (I've tried other SSIS data types.)
I had never seen this type of date before so I'm at a loss.
I have extracted the data from Point of Sales. it has two columns one for date and one for time in the database.
I need to combine both column into single column with the format "mm/dd/yyyy hh:mm:ss" before i import the data into the SQL server for my BI project.
Example: Data extract from Point of Sales
FDATEFTIME
20060114063616
20060115070743
20060116071020
How can i combine those two when i import into SQL server like below:
FDATE
01/14/2006 06:36:16
01/15/2006 07:07:43
01/16/2006 07:10:20
million thanks.
Hello,
I am currently trying to import following Excel Datasheet into my SQL 2005 Database with SSIS:
Column: Created
For example cells: 18.03.2008 17:47:43
So now I want to get the time, the year, the month and the day seperately out of this column for a later OLAP Analysis.
I created a Data Flow Task, where I imported the Excel source and set the SQL database as target. But now I don't know really how to work with the "Data Conversion" where to extract the dates. In Excel this is just a "customized" cell.
Anyone who can help me in this situation?
Thank you very much.
Greets from Germany,
chle
I am importing from a flat file source that is pipe delimited. I have a few files that all follow the same format but now that I am dealing with subsequent files, they are reacting differently and the date is being discarded. I have opened the subsequent file into Excel without any column conversion issues. But whenever I run the package, about halfway through the dates become NULL. I have defined the field as DATE, DatabaseTimestamp, String (convert to Date using substring parsing method) and all of these yield the same results. Any ideas?
View 1 Replies View RelatedFirst off, it has been a few years since I have done extensive work with SQL and that was using Oracle. But I am trying to develop a simple asset database for work, as we have nothing in place. I started out with Access, and decided to move to SQL express for many reasons.
What I have now is that I imported my data from my access 97 database to Excel, only my AssetTable did not import dates, I assume because Access and Sql Express handle dates differently... so a the time I just ignored that column.
Is it possible to insert the dates into the now populated SQL Express database AssetTbl where the AssetID's match? Here is what I have.
Sql Express Database Name: BAMS
Table Name: AssetTbl
fields: AssetID, SerialNum ...(many other fields)... DateAcq <- currently Null
Excel file: AssetDateAcq.xls
fields: AssetID, DateAcq (in format 07/12/2007)
To me it sounds like I need to do a short script/program to loop through the file read an AssetID from the excel file, and the DateAqcuired and then have it do an update on the DateAcq field, but it has been so long since I've done any work with SQL that I am finding there is a lot of "Dust" to blow off, and I don't know if I'm heading down the right track... or completely off course.
Thank you.
Hi
I have an excel file with 2 rows that I am importing into a staging table. The format on the excel sheet is a custom one which is DD-MMM-YY so in the actual cell the data appears as 01-NOV-07 and 30-OCT-07. The format of the data in the input window above the spreadsheet is 01/11/2007 and 30/10/2007.
The format in the Excel source of the data flow task is Unicode String [DT_WSTR] length 255. I then have a data conversion step that changes this to a string [DT_STR] length 255 as the staging table is non unicode. I then have a derived column function that simply does a ISNULL replace on NULL values to blank. Finally there is an OLE DB destination which is the staging table.
This is where the problem occurs. If I breakpoint the package here and look at the staging table the dates now read as 11/1/2007 and 10/30/2007. The data type in the staging table is varchar 255. I am not doing any other transformations or T-SQL stuff on the data - it is being flipped to MM/DD/YYYY during the import.
By the way I am english hence I need the english variation on the date. I know i could put some T-SQL in to flip it around to english again but wondered why it was happening like this
thanks in advance
I am using vs 2010 and I have an .xls file that I am trying to import into SQL Server 2012, and I have most of it figured out, but I have a date field that is giving me problems, and what I would like to do is put that date in a variable so I can add it to every record in my SQL Table.
I am using a SQL Task Editor with an excel connection and I have no problem getting other data from the excel document and putting into my variable, its just the date that I have problems.
I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.
The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.
<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>
The SQL Column is defined as Decimal ((15,2), NULL))
I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values
INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;
I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.
I am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).
View 9 Replies View RelatedI created a simple SSIS package that takes a Flat File Source (CSV file) and Imports it into a OLE DB Destination ([TestCSVImport].dbo.Table1). I have other CSV files I'd like to import, but I don't want to import entries where column "ordereID" (PK) are the equal. Just want to import the new data found in the CSV files. I tried adding a Lookup in-between the Flat File Source and the OLE DB Destination, but I'm not sure how to accomplish only importing new data.
View 2 Replies View RelatedI'm trying to use the Import/Export Wizard as I used to, as a handy tool to figure out what a series of T-SQL statements (in an SSIS package) is doing - or, if I'm lucky, what on earth the original dev intended them to do.
Version: SQL 2014 64-bit running on Win 7 64-bit
The code is pretty dreadful:
SELECT DISTINCT on one set of column names,
join this set to another table but not on exactly the same set of column names,
embedded (SELECT MAX(bla) FROM SameTable WHERE [match to outer set on another set of columns] GROUP BY [hey, yet another set of columns!]) inside the SELECT column list...
and it all goes to a nasty #Tmp, which is then abused with further bad code further down.
Imp/Exp is always handy to quickly get the intermediate results into an auto-created real table, so I can figure out exactly what the effect of this is. I use it to export from the database back to the same database, but to a persisted table.
This time (first time with SQL2014) it's not working. The source is "write a query" (paste the actual query). The destination I set to a new table. The auto-generation of the new table creates every column as type date. Not surprisingly, this doesn't work, as the original data is mostly not of date time.
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
I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard. When pointed to the spreadsheet ("choose a data source") the Import Wizard returns this error:
"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)
How can I address that issue? (e.g. Where is this provider and how do I install it?)
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
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
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!
Hi all,
when trying to ímport files to our database server from a client, I keep getting an error:
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
(SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
best regards
Musa Rusid
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedHi,I am having trouble importing data from an excel spreadsheet into MSSQL Server 2000 using DTS Wizard. The DTS import process issuccessfull, no errors, but only 50 rows of approx. 1500 rows of dataare imported. I tried to remove 20 rows in the excel spreadsheet inthe interval row 0-50. When i later ran the import, only 30 rows wereimported. I deleted almost every row in the interval 0-50, with theresult of the import having 0 rows imported (but job ransuccessfully). I decided to delete rows 0-100 in the spreadsheet inorder to see if the resolved the problem, but it didn't. As Isuspected something in the excel file to be the cause, I exported theexcel spreadsheeet to a tab delimited textfile, with only one row. ADTS import resulted in importing approx 100 rows, double the amount ofthe textfile, but the other 1400 rows were not imported. The data inthe column is containing numeric values only.Please help me! What could possibly be the cause of DTS skipping rowslike that. DTS doesn't feel reliable at all :/Regards,Bjrn
View 3 Replies View RelatedIn SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies View RelatedSo I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Thanks in advance,
Kieran.