SSIS Package Configurations

I have a scenario where I use Excel Source and SQL Destination to move the data. I have a Lookup and Conditional Split in the data which check for conditions
If FacilityId in Excel is equal to FacilityID in table then I have to  update facilityaddress,city,state and zip  in database table.
I encountered an error when I was running the package.

[Conditional Split [981]] Error: The expression "([Copy of FACILITYID] != Dest_FacilityID)" on "output "MatchedRows" (1005)" evaluated to NULL, but the "component "Conditional Split" (981)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.
[Conditional Split [981]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Conditional Split" (981)" failed because error code 0xC020902B occurred, and the error row disposition on "output "MatchedRows" (1005)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Conditional Split" (981) failed with error code 0xC0209029 while processing input "Conditional Split Input" (982). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Conditional Split" (981) failed with error code 0xC0209029 while processing input "Conditional Split Input" (982). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

I have set IsDefaultLocal=true in dataflow and confugured error o/p to failcomponent.Wish to know the various options of config error o/p like Ignore Failure,redirectrows,Failcomponent under which scenarious do we set this.
I need to set configurations for connectionstring  for excel file path and database table so that when i move from one environment to other it will be easy for me to change the connectionstring.Can you suggest me which is the bestway to do so.
 I am new to SSIS.Help me in this regard.
pprasadraviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EmesCommented:
excel is very difficult to export from and to.  
I  always take the data into a staging table that will work easily with excel.
(I have done a import data from sql server to find the table that excel likes)

then move the data from the sql table to the table in the working database.

8080_DiverCommented:
I second the recommendation for bringing the data into a staging table before trying to manipulate the data into the final target table.  I also recommend setting up the staging table so that it only has varchar columns.  That lets you accept any kind of data (e.g. even bad dates) and then figure out what the data is/was/should be without blowing your process off the map.
ValentinoVBI ConsultantCommented:
To answer the bottom part of your request: you can set the connection string for the Excel through an expression.  The best way is to create a package variable that represents the path + filename of your Excel file.  This variable can be used in the expression for the Excel connection string.  Then configure the variable through Package Configuration and you've got a dynamic package as far as Excel file is concerned.

The same can be done for your destination table.  Configure the server name for your SQL Server connection through Package Configuration and that's it.

More info on Package Variables:
http://msdn.microsoft.com/en-us/library/ms140216%28v=SQL.100%29.aspx
http://msdn.microsoft.com/en-us/library/ms141085%28v=SQL.100%29.aspx

More info on Package Configurations:
http://msdn.microsoft.com/en-us/library/cc895212.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pprasadraviAuthor Commented:
thanks.it useful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.