whateveris that



http://www.buyatt.qat/?cpid=1413

Force Hierarchy Permission in MDM 2012

In our organization we have been using MDM feature of MS SQL 2012, to maintain product data in central location. and all other system subscribe to this information.

This product (same entity) belongs to two different business unit with in our organization and we had to implement security model to achieve this.

In MDM 2012 there is out of box functionality for this. the closest feature that come close is Hierarchy segmentation.

Process starts with Declaring Explicit hierarchy for the entity in scope.

and than level entities are assigned to this Hierarchy, There is another way of creating hierarchy which is derived hierarchy and I am sure that would potentially work the same way.

once this assignment is completed next step is to assign permission on this Hierarchy rather than the entity itself.

In my experience I noticed that permission on entity does not readily get applied to leaf level. Sometimes there is small lag while other it does not even get applied at all.

The work around that to get implemented is running following code

DECLARE @Model_ID INT;

SELECT @Model_ID = ID FROM mdm.tblModel WHERE [Name] = N'ModelName';

EXEC [mdm].[udpSecurityMemberProcessRebuildModel] @Model_ID=@Model_ID, @ProcessNow=1;

GO

Upgrade MDM 2012 to SP1(SQL Server 2012 to SP1)

Effects on MDM while updating SQL 2012 to sp1.

Must: take back up of MDM Database:

Right after update MDM site will come down….

1.       You need to go to MDM configuration manager select MDM database again.

2.       Once you do so it will ask you to update the database and SP1 make some database changes.

3.       While doing so it will wipe of your custom Stored Proc so This is where having backup is big help.

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