Monday, 22 December 2014

Wednesday, 10 December 2014

The flat file parser does not support embedding text qualifier in data...Amazing and simple solution

Hi Guys,
I am trying to migrate from DTS to SSIS and 1 of the task was to import data from text file which had double quotes in it. But I will get the below error

"The flat file parser does not support embedding text qualifier in data". I also noticed that the column delimter was not highlighted in the "Columns" tab

After spending hours to resolve this issue, I found  the solution and it is to Just click on "Reset Column" in your connection manager and you will get the "Column Delimiter" highlighted,

This solution is not available in most of the google blogs

Tuesday, 2 December 2014

Trim first few characters in SSIS derived column

Hi Guys,

If you want to remove first few characters in a word in one of the column in derived column in ssis then you can use the below statement
REPLACE (<columnname>,"Word to be replaced","")

Example - SUPER_CLIENT in column PORTFOLIO will be replaced by nothing or that word will be removed using the below statement
REPLACE(PORTFOLIO, "SUPER_CLIENT:","")

Monday, 1 December 2014

If Else If and else statement in derived column in SSIS

Hi Guys,
Follow the below statement type to achieve if else if condition in ssis

[MyStringVar] == "TEST1" ? "IT WAS TEST1":
[MyStringVar] == "TEST2" ? "IT WAS TEST2":
[MyStringVar] == "TEST3" ? "IT WAS TEST3":
"MATCH NOT FOUND"

Monday, 24 November 2014

"parameter name is unrecognized" SSIS

Hi,
Today I got error while making use of SSIS parameters in variables

"parameter name is unrecognized"
To resolve this error - set New Parameter Name as 0 in the Parameter Mapping window

Wednesday, 12 November 2014

Replace "NA" or any other values with NULL in derived column SSIS

Hi Guys,
To Replace "NA" or any other values with NULL in derived column SSIS use the below code

ColumnName == "" ? NULL(DT_WSTR,50) : ColumnName

I noticed that the below code did not work

ColumnName == "" ? NULL(DT_STR,50,1252) : ColumnName. So I just used DT_WSTR as it did not make much difference

Tuesday, 11 November 2014

Failed to set property expression on the output column

Hi Folks,
Today I faced a strange issue.
In the DFT task there is a text file as input file, derived colun and output oledb destination.
When I am adding a new add to new column , I am not getting the datatype. After much research the workaround for this was create a new field for this new column in the data conversion task before the derived column and then make use of this column.

The reason for this failure is that the text file does not contain proper input data field. Enjoy coding ...

Left() in SSIS derived column


Hi Folks,
There is no Left() in derived column in SSIS and hence use SUBSTRING(<your string>, 1, <num of chars>)

Is Not Null in SSIS

Hi Folks,
Did you ever wonder how to implement the IS Not NUll condition in SSIS derived column. Its easy just write the below code

!ISNULL(D_Stock) ? (DT_NUMERIC)Stock : 0

Thursday, 22 May 2014

Convert decimal to string in derived column in ssis

ISNULL(ContractItemNumber) ? "NOT_PRESENT" : (DT_WSTR,50)ContractItemNumber


You can use the above expression to convert a column namely "ContractItemNumber" from decimal to string


Wednesday, 21 May 2014

Run the SSIS package again and again even after failure.

Hi Guys,

There was a requirement to run the SSIS package again and again even after failure. ( SAP connection should wait for infinite time). I was able to achieve this by setting the "For loop" task and "Data Flow Task" Event handler's "Propagate" variable to False. This I could achieve by following the steps in the below link.

http://gqbi.wordpress.com/2013/06/27/ssis-stopping-an-error-from-propagating-to-its-parent-enabling-an-ssis-package-to-continue-when-an-error-happens/



This could be achieved in another way (but I don't know if this is advisable)
Set the "ForceExecutonResukt" property of the SSIS package, For loop and Data Flow task to "Success" This also produces the same result.  

SSIS Package Execution Stops with SQLDUMPER.EXE Popup

Hi Guys,
I have set my package to run again and again by setting the "Propagate variable" value to "False" in eventhandler . But after this my package still failed due to Merger Join task. My package will show the SQLDUMPER.EXE Popup and then stop the execution. This does not serve my purpose of changing the Propagate variable.

This is how I resolved the issue,

My Merge Join inputs were sorted. But 1 of them had "Sort" from the data flow task while the other was sorted manually i,e its sorting property was set to True and the key was set to 1.
I removed this and added the "Sort" task in Data flow task. Bingo! This fixed the issue.



Sunday, 11 May 2014

unable to load librfc32.dll ssis 2012 - SAP BW Connector

Hi,
I was getting the below error while I was first time trying to configure SAP BW connector in my SSIS 2012. To resolve this issue, I downloaded linrfc.32dll and place it in "C:\Windows\syswow64" folder. 

Friday, 11 April 2014

nullreferenceexception was unhandled in ASP.Net or C#

Hi Folks,
I got the below error

nullreferenceexception was unhandled

And I browsed so much for the solution....finally i understood that all I had to do was check the connectionstring name

i,e i had
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CCDBRegistrationString"].ConnectionString);

while the correct connection string name was

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CCDBConnectionString"].ConnectionString);

Cannot insert the value NULL into column 'id'? ASP.Net

Hi Folks,
If you get the below error while being a fresher in ASP.net ...

Cannot insert the value NULL into column 'id'?


Then please add identity property to your primary key

[ID] [int] Not NULL IDENTITY(1,1),


check the below link for more clarification
http://stackoverflow.com/questions/10013313/why-is-sql-server-throwing-this-error-cannot-insert-the-value-null-into-column

Friday, 21 March 2014

Add 2 labels to a bar chart or stacked chart in SSRS or add a label at the middle in the Bar chart

Hi Folks!

As soon as I came to office, I got a call from my collegue to make some changes to the allready existing report. 1 such change was to add a data label at the center of the bar chart. I performed the below steps to make it work. Enjoy charting !!!


https://www.youtube.com/watch?v=c7qDBWVAymE&feature=youtu.be



https://www.youtube.com/watch?v=c7qDBWVAymE&feature=youtu.be

Wednesday, 5 March 2014

Connect to SAP source and Destination from SSIS

To connect to SAP Source and destination you will first need to download Microsoft SQL server 2008 Service Pack 2 Feature Pack from the below link

http://www.microsoft.com/en-in/download/details.aspx?id=6375

Click on Download and try to download SAP related file only (to avoid time consumption). Later run this file. After installing, open SSIS project in BIDS. Click on Tools> Choose Toolbar options and then under SSIS data flow objects you will find SAP BI destination and SAP BI Source. You can now proceed to connect to. You will also fine the SAP BI connection manager in the Connection manager window in SSIS.