March 2009 Blog Posts
SSIS - “OLE DB provider ‘STREAM’ for linked server ‘(null)’ returned invalid data for column ‘[!BulkInsert].column_name’."

I've been trying all day to solve the problem with export of data using SSIS and keep encountering this error: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].{column_name}'.

It seems that data in that column is invalid so I've tried to add validation to my source OLEDB source. I've created a dataflow to filter all records that have bad data and redirect it to a file. To do this in the query of my source OLEDB I've add the following condition: WHERE isnumeric(convert(varchar(250), coalesce([{column_name}],0))) = 0 then put the results in the file IDs and etc, but I removed the {column_name} to prevent errors.

I now have the IDs of bad data and will try to some fixes before do the import.

posted @ Friday, March 06, 2009 6:24 PM | Feedback (2)
SQL 2005 Configuring SQL Job to send email notification if it failed.
This are the simple steps to enable it.
1) Run SQL Server Surface Area Configuration - > Surface Area Configuration for features ->
    Select Database Mail - > Check Enable Database mail stored procedures.
2) Open SQL Management Studio - > Management - > Database mail - > Configure SMTP Settings.
3) Go to SQL Server Agent - > Operators - > Create New Operator
4) Go to SQL Job - > In the Notifications, Check email and select the Operator. You can either
select  the option to send notification if job fails / succeeds/ completed.
Lastly, don't forget to restart the SQL Agent.
posted @ Wednesday, March 04, 2009 11:24 PM | Feedback (3)