SSIS: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

SSIS Error:
An error occurred in the requested FTP operation. Detailed error description: The password was not allowed:

Requirement: was to develop a SSIS package for reading MOST RECENT file from FTP server and load data to a Table in Database.

Solution consist of following steps:
1. Starting with script task which will read the file name listing from FTP server and identify latest file in the list of file names. (Script can be found at the end )
2. Check if file is already processed.
3. If it is new file use FTP task to move the file from FTP to local staging folder.
4. Move the existing data from destination table to a history table with the file name with the data.
5.Clear all the data from destination (an alternate approach have been to just to upsert to so that we don't have to truncate the whole table and just go with the update, in this scenario requirement was to reflect data from latest file only)

6. Load the data from this new file into destination table.

One of the challenge was to read FTP configuration only once and then use these values in script task as well as regular FTP task, It would have been easier to read it twice from configuration, but was not resulting in elegant solution and more over it could have been maintenance nightmare.

Approach was taken to create parameter  like FTP server name and FTP user name and  password and read  values from configuration only once.

upon further search found way to use these parameter to create connection string for FTP connetor for FTP task. (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3713e9a5-343a-4c5b-ac5d-1508cd5ab8be)

Connection string = @[User::FTPServer]+":21" + "." + @[User::FTPUser] + "." + @[User::FTPPass]

In my scenario since I was reading FTPServer value as = ftp://ftp1.servname.com

I had to use following expression
Connection string = REPLACE(@[User::FTPServer] ,"ftp://","")+":21"+"."+@[User::FTPUserName]+"."+ @[User::FTPPwd]

these program ran without any error in BIDS in debug mode, but when I deployed to prod server I started to get this error:

Error: 2013-05-07 16:06:15.06
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2013-05-07 16:06:21.56
   Code: 0xC001602A
   Source: pakage_name Connection manager "FTP Connection Manager"
   Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed.
End Error
Error: 2013-05-07 16:06:21.59
   Code: 0xC002918F
   Source: FTP Task Move the file to local folder FTP Task
   Description: Unable to connect to FTP server using "FTP Connection Manager".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

SOLUTION TIME:
Upon further search I found the solution (http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/912d43a5-975f-48b6-a9f1-660efeacc8ed/)


Job gets working by changing the ProtectionLevel on the FTP packages to EncryptSensitiveWithPassword.

:OM:

Following is code is used to read latest file name from FTP server:

        public void Main()        {                        string strFileNameLike= Dts.Variables["FileNameLike"].Value.ToString();            string strFTPServer= Dts.Variables["FTPServer"].Value.ToString();            string strFTPUserName= Dts.Variables["FTPUserName"].Value.ToString();            string strFTPPwd= Dts.Variables["FTPPwd"].Value.ToString();            string strFTPFolder= Dts.Variables["FTPFolder"].Value.ToString();            string strFTPFullPath=strFTPServer+"/"+strFTPFolder;               FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(strFTPFullPath);            request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;            request.Credentials = new NetworkCredential(strFTPUserName, strFTPPwd);            request.UsePassive = true;            request.UseBinary = true;            request.KeepAlive = false;             string[] list = null;            string[] listFilteredArray = null;             List<String> listFiltered = new List<string>();              using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())            using (StreamReader reader = new StreamReader(response.GetResponseStream()))            {                list = reader.ReadToEnd().Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);            }             string strStartsWith = strFileNameLike.Split(new string[] {"*"}, StringSplitOptions.RemoveEmptyEntries)[0].ToString();            string strEndsWith = strFileNameLike.Split(new string[] { "*" }, StringSplitOptions.RemoveEmptyEntries)[1].ToString();              foreach (string strname in list)            {
                if(strname.Substring(56, 25).StartsWith(strStartsWith) && strname.Substring(56, 25).EndsWith(strEndsWith) )                {                    listFiltered.Add(strname.Substring(56, 25));
                }         
            } 
            listFilteredArray= listFiltered.ToArray(); 
                        if (listFilteredArray.Length > 0)            {                Dts.Variables["FileName"].Value = listFilteredArray[listFilteredArray.Length - 1].ToString();            }
            Dts.TaskResult = (int)ScriptResults.Success;        }

SSIS: Value does not fall within the expected range

SSIS Error

[Execute SQL Task] Error: Executing the query "EXEC dbo.sp_name ?,?,? OUTPUT" failed with the following error:

 "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, 

"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When you are trying to execute a stored proc though execute SQL task:

you can call sp like 

            EXEC dbo.sp_name ?,?

when there is no out put parameter

and like 

           EXEC dbo.sp_name ?,?,? OUTPUT

where there is an output parameter.

If you try to assign parameter names like above in this case you will receive error that 

"Value does not fall within the expected range."

and it gives no clue whats so ever regarding the issue with the task

this can be fixed by replacing actual name of parameters in Parameter Name Column for Index location of the parameter that you have defined in your stored proc 

and  parameter mapping tab is going to look like and  Bingo your error is gone....

could not UPDATE table "[MSDASQL]"

--update any record

UPDATE ic SET

                        Display_Name__c =ISNULL(U.Display_Name__c ,'')

                        ,Display_order__C=ISNULL(U.Display_order__C ,0)

                        ,is_Active__c=U.IsActive__C

                        ,Name=ISNULL(U.Name ,'')

                        ,Parent__C=ISNULL(U.Parent__C,'')  

                        ,cost__C=ISNULL(U.cost__C,0)

                        

--DELETE FROM ic

--Select *

FROM

OPENQUERY(LINKEDSERVER,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__C

                        ,Name

                        ,Parent__C 

                        ,cost__C

                        

             from

                  item

                  ') ic

INNER JOIN #UPDATES U

 ON ic.id=U.Id

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "SetPos update return error".

Msg 7343, Level 16, State 4, Line 296

The OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" could not UPDATE table "[MSDASQL]".

And I get following error with as is_Active__C field in PostGres is boolean and OpenQuery unable to update , once remove this column and query works fine.

other alternative is delete this record and insert the whole record again.

DELETE Query:

DELETE FROM ic
--Select *FROMOPENQUERY(LINKEDSERVER,'Select
                        Id                        ,Display_Name__c                        ,Display_order__C                        ,is_Active__C                        ,Name                        ,Parent__C                         ,cost__C                                     from                  item                  ') ic
INNER JOIN #UPDATES U
 ON ic.id=U.Id

INSERT Query:

            INSERT INTO OPENQUERY(BUYBACK_STG,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__c

                        ,Name

                        ,Parent__C 

                        ,cost__C

             from

                  item

                  ')

SELECT

                        Id

                        ,ISNULL(Display_Name__c ,'')

                        ,ISNULL(Display_order__C ,0)

                        ,IsActive__C

                        ,ISNULL(Name,'')

                        ,ISNULL(Parent__C,'')  

                        ,ISNULL(cost__C,0)

                        FROM #UPDATES 

conversion from Informatica to SSIS

While most Informatica components will directly port into SSIS, some will require 3rd party components. The Pragmatic Works Task Factory product for example gives SSIS more powerful tasks and data processing components. The below list shows a few of the common Informatica components and how they will port to SSIS. For a more complete list, please click here. Informatica SSIS Session Native Data Flow Command Task Factory Advanced Process Task Worklet Task Factory Advanced Execute Package Decision SSIS expressions Assignment SSIS expressions Database connectors Standard SSIS OLE DB \ ODBC \ ADO.net source and destinations Cloud connectors Many handled by Task Factory Aggregator Aggregate Transform Expression Task Factory Advanced Derived Column Transform Filter Task Factory Filter Transform Joiner Merge Join Lookup Lookup Transform (connected) or Task Factory Advanced Derived Column Transform (disconnected) Router Task Factory Advanced Conditional Split Sequence Generator Task Factory Surrogate Key Transform Sorter Sort Transform Update Strategy Task Factory Upsert Destination XML Creation Task Factory XML components Maplet Task Factory Data Flow Nugget

Source:http://www.pragmaticworks.com/services/informaticamigration

Indexes

States about SQL2012 release:

clustered Index per table 1
non clustered Index per table 999
xml Index per tabel 249
columns in normal table 1024
columns in wide table 30,000
rows in table : limited by memory
nested level in trigger 32
size of varchar 2^31-1
size of nvarchar 2^30-1
database size 524,272 terabytes
foriegn key per table 253
SQL instance per machine 50
partition per table/Index 1000 for 32 bit and 15000 for 64 bit
user connection 32767

If there are indexes like

Create Index IDX_Tab1_Col1Col2 on Table1 (Column1,Column2)

and if there is query using filters on both the column in a where clause, than the order of filter given does not matter.

but if there only one filer in where clause than it matter whether this filtered column is first or second in Index,

• If it is first column in index definition, query plan uses Index seek
• If it is second column in the index definition, query plan used Index scan which is more costly, hence performance is not the same.

also the Index seek operation will be applied on the Second column in Index when first column will is have equality operation(remember that order filter in where clause does not matter in this case)

 Where Column1=1 and Column2>1   (Index Seek on both the columns)
 Where Column1=1 and Column2=1   (Index Seek on both the columns)

 Where Column1>1 and Column2>1   (Index Seek on Column1 but Index Scan on Column2 )
 Where Column1>1 and Column2=1   (Index Seek on Column1 but Index Scan on Column2 )

Transaction Isolation Levels IN SQL Server 2012

SERIALIZABLE

·         Statements cannot read data that has been modified but not yet committed by other transactions.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

·         No Phantom reads

REPEATABLE READ

·         Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.(hence you can repeatedly read the same values)

·         Possibility for Phantom read

READ COMMITTED

·         Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction (not in any other transaction), resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

No other transactions can modify data that is being modified by the current transaction until the current transaction completes. but if it transaction just read the record  in its scan not modify, it open for action by other transactions. hence values of few records may change or new record inserted by other transactions when it is just being read by read commited transaction.
·         Possibility for Phantom reads

READ UNCOMMITTED

·         Specifies that statements can read rows that have been modified by other transactions but not yet committed.

·         Allow dirty read

SNAPSHOT

·         The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

·         The transaction can only recognize data modifications that were committed before the start of the transaction.

·         Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Usage of sp_executeSQL

Usage of sp_executeSQL

SET @sql = 'Select * into ' + @delim_delta_table + ' from ' + @table_server + '...'   

  + @delim_table_name + ' where ' + @timestamp_col_name + ' > @LastTimeIN'  + ' and '   

  + @timestamp_col_name + ' <= @TimeMax '   

 

     

    select @parmlist = '@LastTimeIN datetime, @TimeMax datetime'   

    exec sp_executesql @sql, @parmlist, @LastTimeIN=@last_time , @TimeMax=@time_max