SSIS's BULK insert error msg

Problem

When you are trying Bulk insert from csv file, here is the error message you may run into:

“Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).”

Solution

First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying to create the file to a remote location then the path should follow UNC, i.e:

             \\Server_Name or IP_ADDRESS\Shared_Folder_PATH(Name)\FILE_NAME

Note that the path may be OK if you are trying to create the file on the SAME server running SQL Server.

Secondly, make sure that the SQL Server service account has permissions to SQL Server instance.
For doing this you can use a Domain user or, create a new local user and start the services with that account.
Go to the lower left corner of the desktop, click START, input “services.msc”, then choose “SQL Server” as shown below:

Check Log On AS, a new window will pop up.

The default setting is Local System account. Try changing it with a domain user account and restart SQL Server Service after new changes.

Last but not least, make sure that this account has Read and Write permissions on the folder where you are creating the file. To do so, right click on the folder --> sharing and security --> permissions.

That’s it! Now the error message should have been swept away.

SSAS error msg: Unable to retrieve data mining algorithms

Problem

When you tried to create a new mining structure in SSAS, you may notice a warning message appears in the window as shown below:

“Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms.”

Solution:

Step 1: Check Analysis Service has been started from SQL Server Configuration Manager.

Step 2: Check the deployment property to make sure that it has been set up correctly.

How to: Use Check Constraints to Enforce Business Rules

As a developer, you may often find that it is essential to ensure data integrity before the data get into database. Otherwise, you will face a cleanup work or the situation of “trash in, trash out” afterwards. Today we will address how to enforce business rules by using check constraints on columns.

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]

Add a CHECK CONSTRAINT

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'.

 

Test a CHECK CONSTRAINT

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.

Decipher SSIS Error Codes: -1071607685

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: 

• Integration Services Error and Message Reference
• header file dtsmsg.h

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!

SSIS Validation Status “VS_NEEDSNEWMETADATA”

Problem                                                

Here is the error message:                                                   

Error on Component:[DFT_xxx]: "component "OLE DB Destination" (2327)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

It looks like some data mapping for columns in the SSIS package were lost after being deployed to the production. Both dev and production have the same number of columns. Also the name looks the same!

Solution

Check the columns to see whether they have the same case in the production as in the development. SSIS is CASE sensitive and it cannot automatically recognize those columns if the only difference is the case.

How to update column name?

You need to use sp_RENAME to update column name as below.

sp_RENAME '[dbo].[tableName].columnname', 'ColumnName' , 'COLUMN'
GO

Hope it will help you debug faster!