Update Override, why? where? when?

A little light on Update Over ride and its use:

We have a an Update strategy transformation to perform any kind of update on the target table, then what is it all abot the usage of Update over ride in a target.

Before we can jump into our topic, for all those who are new to Update startegy -> Update startegy transformation is something that can help you in updating your target table with the new values on the basis of the Primary key match.
Making it simple,”UPDATE tableA set ColumnA = New_Val Where Primary_Key matches”

So now lets take up the use of Update Over ride option, as i was going through this particular scenario i had to Update my Target table with the new values, but not for the Primary Key Column. Rather i was in a position where i had to update the table on the equation of non Primary key column.

As an instance, i had to update the salary of Employees of country Australia. WHere as in my table i do not have the Country column as a Primary key (Employee_ID shall be a Prmary key here). We got to update this!! but how?

Then comes the use of having Update over ride, where i have to lookinto the Properties of my targe table. There we have the “Update Override”. SImple on we have the expression or the Update query to be written as one below,

UPDATE TableA
SET Employee_ID = :TU.Employee_ID, Country = :TU.Country, Salary = :TU.Salary
–Here is where you add condition of check on basis of Non Primary key
WHERE
Country = ‘AUSTRALIA’

And this shall update the records with the new records (changed salary) that have the country = ‘Australia’. SO this is where the Update Override comes as a help in hand.

Thanks for reading, please share. sharing makes us feel good

pmcmd – Start a workflow from a task

Just to get back my touch on blog, going for a small article on something i just gave a try. Its a pretty easy and a quick to understand command. But for those who have never gave this a try here i go.

My aim was to get a particular worklet/Session to run in a workflow. Means like i want to run the workflow but not from the start task but some where from the middle. So i can just have a pmcmd command to do it as is below,

pmcmd startworkflow -u User_Name -p password_here -f FLD_name -startfrom WORKLET_name -wait WorkFlow_name

So this makes the command to kick start the Workflow “WorkFlow_name” from the task/Worklet “WORKLET_name” and wait command ensures the command task to run until the called WorkFlow_name completes.

Dynamic Informatica Target file generation (with looping on the files!!)

This was some good activity i was doing as part of some excercise on Informatica. All i had to do was to get a source and try loading the source into a target flat file. And here comes the real twist, i was required to load the data to a target and in a dynamic fashion, viz. to load the data to target file sat target_flat_file_1 at first run of session and so on till target_flat_file_5 as 5 different runs. Making 5 last backup of each loads.

After this we are also required to have the target load in a looping format, where after file5 the target has to load back from File1 and this way we have a last 5 target loads preserved. How to do this??

So here is how i made it:

Have your mapping designed with all that you require for your load run, and this is where we have a thing to work for us. In the Target creation of the Flat File we need to add one extra port ‘File_Name’ by clicking on the top-right end as is shown in below image – “Add FileName”.

Then added he target to our mapping. Also we have one more task pending to decide on what way the name should be given, this can be achieved by an expression for the File Name port. Before that let me define a Mapping Variable as is below. (Make sure we have the Aggregator type as ‘COUNT’)

Then as we have our File_Name port to have an expression as is shown below, with a Variable Post to have any value between 1 to 5 as a normal Counter logic.

The Variable then is used in the FileName port as below and the output port from expression connected to the FileName port of the target as we had defined earlier.

Thereby we have things ready. Just define your session and run the mapping which shall load the target to the File named as per our expression.

NOTE: In doing this i had also to give some dummy file name in the Target file name in the session, say ‘Dummy_File.txt. And so every time i run the session it loads data to the File named as per FileName port and also there is a dummy file created with no data. Any one please let me know if i can have no target file defined at the session level?

A Smart way for creating a table (SQL) for Informatica guys!!

This was something i got to learn from my team mate. A very good and a simple way of making your work much easier.

So as the requirement said, create a SQL table and load the data from a MS Excel file. Say there were almost 50 columns and so i had a tiring and huge manual work of listing down the column names and then write a SQL script to create the required SQL table. Though i was sure, that once the table is ready i can very well make the Excel file to a tab delimited text file and later load the whole data through a mapping with source as the flat file(text file) and the target as my SQL table that i will be creating in SQL.

Not a big challenge, but needs a smart solution to handle the table creation having plenty of columns to be created. Here we have a better way to do this for the Informtica developers.

All we have to do is, just import the Flat file source into the informatica source analyzer and just drag drop the same Source object into our target designer,i mean copy the source as a target. Now we have a target created same as the source. And here is all we need to act smart, change the target Database Type to ‘Microsoft SQL Server’ and so we have made our Flat file target into a SQL server target table.

Now comes the trick of making things easier, just get on to Target menu -> Generate/Execute SQL.. and we have the wizard window to generate the SQL script for us. Since we need a Create table script, just have your options selected as is shown below.

And just a click on the Generate SQL File shall get the required script of creating a table having any number of columns. So here we are ready with a create table script to make our job easier. All we have to do here on is to get this running on the SQL Server Management studio to get the table created. And as all we know to populate this is pretty simple. We have our flat file source available and also the target. A mapping to load from source to target, execute it and we have the table created with the data populated.

Isn’t that simple!!?? Share and comment

Adding Adventure-Works sample database

Just I was done with my SQL Server installation  all that I need now is a sample database to work on. And this requires to download the sample DB that is available from the Microsoft Corporation to be used as sample DB.

All I had to do was to follow the below simple sequence of steps,

1. Download the AdventureWorks 2008 OLTP Script.zip, @ the link http://msftdbprodsamples.codeplex.com/releases/view/89502,
2. Copy all the files in the downloaded zip to any folder location, I just copied the files to the location – ‘C:\AdventureWorks 2008 OLTP Script’
3. And now all we have to do is open the ’instawdb.sq’ at the location C:\{AdventureWorks 2008 OLTP Script Path}\ with the SQL Servre management studio.
4. The script available has to be edited as per the need, as I have done below, uncomment the variables
5. Set the variable SqlSampleDatabasePath to the Data folder location which is mostly by default “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\” and also set the SqlSampleSourceDataPath to “C:\AdventureWorks 2008 OLTP Script\”, the location where you have your .CSV files placed.
6. Do not forget to set the Query mode in Query menu to SQLCMD mode as below,
7. Execute the query now and we are done, to see the sample database AdventureWorks2008 installed into our SQL Server. Share this to help some one.

   

   This results in the AdventureWorks2008 sample Database added to our SQL Server,

   

    

Installing SQL Server 2008 R2 on your PC

I was just getting my SQL Server 2008 setup on my desktop for the second time and I had lots of confusions and the take off was never going good, finally was able to get it done. So decided to document the whole of the process step by step for others to make the best use of this.

As usual start up the set up clicking on the setup.exe file,

Then we have the installation wizard to follow by,

Go as per the screen shot, click on the installation to launch a new instance of the SQL server,

Get the Product key that comes with your pack and type it as shown,

Then list of setup rules appear, with an exception on the warning of the Firewall setup, which can be ignored and can be fixed once our setup is installed.

Then select the type of installation. Preferably ‘All Features with Default’ would be good to go with,

Let all the features be selected,

Instance configuration, let this be the default MSSQLSERVER,

For the server configuration set the Account types as below, which should be fine to go with and  let the start up type be Automatic,

In the Database Engine Configuration, lets have a mixed mode to enable both Windows and SQL Server authentication.

Either add the current user or can add a new user by clicking on ‘Add..’ and then click on ‘Advanced..’ followed by ‘Find Now’ and select the user as required.

In case of Analysis services Configuration similar users can be added following the same series of steps,

For Reporting Services Configuration select ‘Install native mode’,

And we are almost done, give Next and then Install..

We are done and here appears the last screen of the installation showing the success message,

Please post your queries and comments and share to help someone.

Precision Property in Informatica Session.. avoid data loss!!

Found something interesting last week @ work. This was a failure we had due to data overflow. We were trying to load data from source of higher precision into the target of same data type but with lesser precision. We had a source column of Decimal(17,3). Before we proceed, we must all be aware that 17 here refers to the precision length (i.e. 14 digits before decimal + 3 digits after decimal). And the target was set of a size or precision Numeric(15,5) means 10 digits before and 5 digits after decimal.

This was all working good until all our source data was within the precision of (15,5), and finally when the size outlined this there came a data overflow error while running the Informatica session. so all we had to do was change the precision at the data base level as from Numeric(15,5) to Numeric(17,3). Then again the session failed, and it was right as we are yet to change the same in the Informatica Target transformation.

Now a change done on Target in informatica to match with the table in SQL Server (in our case the target table here). On running the session it all succeeded. But there is something we have to wonder on!! “The precision were not changed in all other transformation in the mapping!! still the session made it through with no truncate on the source data value.”

Well this was possible with one property set on your session level, enable high precision on the properties tab of the session. In case of Decimal values the Integration services handles the values in a different way as is explained in the
Informatica help:

“To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.”

For example, you have a mapping with Decimal (17,3) that passes the number 400120303049 and we have to convert from here to Numeric(15,5). If the session does not run with high precision, the Integration Service converts the decimal value to Numeric and passes 4001203030 to the next transformation.

To sum up here, the integration services allows the precision of the maximum allowed (say for decimal as 28) to pass through without any truncate on the result one and only if the high precision property is set on the session Property.

Thanks for reading.. SHARE