SSIS Error: Error: ForEach Variable Mapping number n to variable "User:: n1 " cannot be applied.

The exact error I got was
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of  variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable  User::i_HospitalCode  in the variable declaration window and it worked!

Restore database

use master
GO

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE


GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO

Alter Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC sp_updatestats

I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE




Then after successfully restoring it I changed it to the multi user mode.
GO

Guidelines for index-fragmetnation

Some starting points to keep in mind for index fragmentation

•  If an index has less than 1000 pages and is in memory, don't bother removing fragmentation
• if the index has:
  • less than 5% logical fragmentation, don't do anything
  • between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)
  • more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)

 The guidelines are taken from 
http://sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx

Error: SSIS package : A rowset based on the SQL command was not returned by the OLE DB provider

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode.  It returns values when using the preview but when I test the SSIS package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider


Solution

I just had to add SET NOCOUNT ON in the begining of the stored procedure that I was using.

Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------

• In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
• Repace the space and token in the string result from step 1 with  an empty string.' ~'
• Replace the result of the above atring with a token and the space

DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

Counting the occurence of a substring within a string

Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
 Logic
--------------
Find the substring ball and replace it with ''   e.g.abcdmnopqrst
Find the length of the new string                   12
Subtract the new length from the lenth of the original string  24-12 = 12
Divide the new number with the length of the substring   12/4 = 3

Here is the script - taken form the November issue of SQL server magazine

DECLARE
@str       AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr  AS  VARCHAR(1000) = 'ball';


SELECT  LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)

This is so awesome!

COALESCE - Selecting each record with all not nul values

I had three rows of data that had the same acount number but differnt variation in the three columns. I had to select each account with not null column . This was achieved by the following...

My data
create table #tmpcol(c219 int, c220 int,c221 int)
INSERT INTO #tmpcol
SELECT null,220,null
INSERT INTO #tmpcol
SELECT 219,null,null
INSERT INTO #tmpcol
SELECT null,null,221


select top 1 * from #tmpcolorderby coalesce(100*c219,10*c220,c221) desc