Monday, December 21, 2009

SSIS Error logs

SSIS Error logs and workarounds

1.Error for 'Excel Destination'
Description : [Excel Destination [5606]] Error: The "input "Excel Destination Input" (5617)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (5617)" specifies failure on error. An error occurred on the specified object of the specified component.
Reason : I was getting this error when my excel file's max row count of 65536 is reached and SSIS package try to write to it.
Workaround : My rowcount of the result was definately not that huge, only thing was it was always appending on to the excel. Few workaround
1. use excel 2007 , it has 1 million rows.
2. Create a 2 execute sql task before the 'dataflow task, such that 'execute sql task1' drops the excel table and 'execute sql task2' will create the table again.

2.Error when executing SSIS

Description : Product level is insufficient for component
Scenario : Trying to execute a SSIS package from SSMS and its fails on "rowcount","Raw file source", "Audit"
Reason : SSIS is not installed on the machine where you are trying to access the package however you have SSMS
Workaround :
1. Write a scheduler/job that runs the SSIS and run it .
2. Login to the server where SSIS is installed and try accessing the package from there.
3. Install SSIS in you PC.

3.Error with 'Flat file source' + variable use

Description : The filename is a device or contains invalid characters.
Reason : In my case the execution on local machine was working and on deployment i was getting this failure result.
Workaround : I found the issue was the flat file location was both a input and output location and it was failing when both happens synchonously.

4a.Error with 'Send mail Task'
Description : Error occur when trying to send mail
[Send Mail Task] Error: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Unable to relay
Reason : SMTP server expect Window's authentication.
Workaround : To do this, Enable (check) integrated authentication for the SMTP server. See below

4b.Error with 'Send mail Task'+ variable use

Description :
[Send Mail Task] Error: " specified string is not in the form required for an e-mail address.".
Scenario : When you want to configure a Send mail's Toline as a user defined variable. using Property window or using Send mail task editor.
WorkAround :
step 1 : Double click 'send mail task'.Goto expression and then select 'Toline' and then click '...' and then select the user variable. (@user::updateEmail)
step 2 : Goto mail and then assign @user::updateEmail for Toline.

Execute the task. You will find that the value of variable is replaced in place of @user::updateEmail.