SELECT convert(DATE, GETDATE() )
SELECT CONVERT(VARCHAR(10),GETDATE(),126)
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which kind of integer data types you should use for SSIS for strings in the source table. Hope it helps!
| SSIS | SQL Server | Range | Maximum String Converted |
| DT_I1 | tinyint | 0 to 255 | varchar(2) |
| DT_I2 | smallint | -2^15 (-32,768) to 2^15-1 (32,767) | varchar(4) |
| DT_I4 | int | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) | varchar(9) |
| DT_I8 | bigint | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | varchar(18) |
LEN(strCol) == 0 ? NULL(DT_I4) : (DT_I4)(RTRIM(strCol))
You can also refer to BOL: int, bigint, smallint, and tinyint (Transact-SQL)
Here's an example of a Member table with name and phone number as columns. Let us check constraint on the phone number column. Constraint will suggest that phone numbers should follow the format of xxx-xxx-xxxx.
IF OBJECT_ID('[dbo].[Member]') IS NOT NULL
DROP TABLE [dbo].[Member]
CREATE TABLE [dbo].[Member](
MEMBERID INT IDENTITY NOT NULL,
[Name] varchar(25) NOT NULL,
[PhoneNumber] varchar(12) NULL
)---- INSERT RECORDS
Insert INTO [dbo].[Member] Values
('Susan', '697-555-0142'),
('Kevin', '819-555-0175')
If you check the table, you can see it already has two records.
SELECT *
FROM [dbo].[Member]
Then we add a CHECK CONSTRAINT to help check the format of phone numbers.
ALTER TABLE [dbo].[Member]
ADD CONSTRAINT CK_Member_Phone
CHECK ([PhoneNumber] LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
GO
If any of existing records in tables violate the constraint to be added, it will pop up messages like this:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “CK_Member_Phone”. The conflict occurred in database "xxxx", table "dbo.Member", column 'PhoneNumber'.
Next, you can test the check constraint by trying to insert a record that has a phone number not following the format defined in the constraint CK_Member_Phone.
Insert INTO [dbo].[Member] Values
('Charles', '212-555-0187'),
('Chris', '612555-0100')
An error message will be returned like the following one:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Member_Phone". The conflict occurred in database "DemoSQL2012", table "dbo.Member", column 'PhoneNumber'.
The statement has been terminated.
When using SSIS as tools to loading files, you usually can get a very clear error message that indicates what is going wrong. You can tell which column is wrong from ErrorColumn and for which reason the column brought failure from “ErrorCode – Description”.
However, when loading a source file that is not formatted as expected, if you have got an error output with "No Status is available" as ErrorCode and “0” as ErrorColumn as shown below, what do you feel?
Do you feel like lost in darkness? Somewhat …
Here is my recent experience in helping out troubleshooting file loading problem. Since there is no clue, all I can do is to check all constraints on all columns for potential trouble-makers.
Finally, it turned out that the trouble-makers is one of the obsolete columns that used to be NOT NULL, but no more input at the current loading. The solution is easy. You need to allow that column to have NULL values.
During the research for the clue, I uncovered two helpful resources to decipher SSIS error codes:
The header file dtsmsg.h is under the folder
C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include (for SQL Server 2012)
The two resources covered the five kinds of messages as shown. The online one is in table form and easier to follow, while the header file is more precise and detailed in technical terms.
So next time, hope you will feel more confident when you get SSIS error messages!
---- To check SNAPSHOT_ISOLATION_STATE and READ_COMMITTED_SNAPSHOT for the database
SELECT name as database_name
,snapshot_isolation_state
,snapshot_isolation_state_desc
,is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'DATABASE_NAME';
GO
--- To check isolation levels for other connectionsHere @@SPID returns the session ID of the current user process.
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id =
---- where session_id = @@SPID
DBCC useroptionsSet Option Value
--------------------------- --------------
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed snapshot