SSIS Control Flow Conditional

This short blog showing you how to create conditional in SSIS Control Flow.

1. Drag Script Task to your Control Flow.

2. Put another two of any component in the Control Flow.

For here, you can see I have one Data Flow name Data Flow XML to Full ETA and Script Task Update Log.

You do not need to edit the script in the first Script Task, Script Task Detail Flow.

3. Create a SSIS Variable, in my example is varMCValue.

4. Double click on the green line to the right.

5. Select Expression on the Evaluation operation dropdown.

6. In the Expression: box enter @[User::varMCValue] == 1.

7. Double click on the green line down.

8. Select Expression on the Evaluation operation dropdown.

9. In the Expression: box enter @[User::varMCValue] == 0.

That is done.

Reference:

http://bi-harpreetsingh.blogspot.my/2011/10/how-to-implement-conditional-split-in.html

Advertisements
Posted in .Net, Community, Computers and Internet | Tagged , | Leave a comment

SSIS Loop XML files in folder

This short blog is showing you how to loop through the XML files in a folder using SSIS.

This is modified from http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx. Below highlight the key elements for the loop.

At first, do have varFileName and varSourceFolder variables, and varFilePath expression.

Choose Foreach File Enumerator for Enumerator field.

For Filesput *.xml because the files we want to loop is xml files.

In the XML Source instead of XML file location select XML file from variable.

In the Variable name: drop down choose the varFilePath expression. You still need to choose the XSD file, this cannot be dynamic.

Do feedback to me if this does not work for your xml files in a folder.

Reference:

https://stackoverflow.com/questions/29401713/ssis-reading-multiple-xml-files-from-a-folder

Posted in .Net, Community, Computers and Internet | Tagged , , , | Leave a comment

SSIS how to use Row Count

This short writing shows you the right approach to use Row Count Component in SSIS. This writing assume you know what is Row Count and how to use the Row Count despite the right or wrong way. Just be reminded you need a SSIS variable (only int32 type) in order to use Row Count.

The below data flow will not work for Row Count. Row Count will only be updated when the data flow completed. This is my finding of SSIS packages.

To illustrate this I created a simple flow with a Script Task.

Below is what in the Data Flow Task.

This is the code you need to inside into Script Task.
What I did was, I was writing the Row Count value into a text file.
public  void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;

     StreamWriter sw = new
StreamWriter(“c:\\temp\\RowCountOut.txt”);
sw.WriteLine(Dts.Variables[“RowCounting”].Value);
sw.Close();

}
Reference:
https://stackoverflow.com/questions/15384108/does-variable-value-set-by-row-count-transformation-take-effect-during-execution

Posted in .Net, Community, Computers and Internet | Tagged , , , | Leave a comment

SSMS Upgrade from 17.x to 17.3

This blog shows you SSMS Upgrade from 17.x to 17.3 without first uninstall the existing SSMS.

Previously to upgrade SSMS I need to manual the previous version of SSMS until this version, 17.3.

I was using 17,1, when I have seen the new version notification then I downloaded the SSMS version 17.3 from the following link.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms


Once you finish downloading then you will get the file SSMS-Setup-ENU-Upgrade.exe. Run the exe,

Click Install. You will see your installation is in progress.

Posted in .Net, Community, Computers and Internet | Tagged , , | Leave a comment

Passing PRINCE2 Foundation Exam

This short article gives you some hints to pass PRINCE2 Foundation Exam.

As many sources out there to help me to pass this exam.

Go to the file share here https://1drv.ms/f/s!AuZYHSwsevw1hh1FT8nTPAMItRrq.

The key sample question file sorted by ranked of higher chances to have sort of similar question.

1. gratisexam.com-PRINCE2.Actualtests.PRINCE2-Foundation.v2015-05-18.by.Elbert.120q.pdf

2. PRINCE2-2017-Foundation-Sample-Paper.zip

3. PRINCE2_foundation_sample_paper.pdf

4. PRINCE2-Foundation-Exam-Paper-v1-1-English.zip

5. PRINCE2-English-Foundation.zip

These web pages will give some helps too. Hope by the time you read this, there are still around.

https://www.prince2primer.com/foundation-exam-questions-progress-theme/

https://www.prince2primer.com/foundation-exam-questions-part-3/

https://www.prince2primer.com/foundation-exam-questions-part-2/

https://www.prince2primer.com/foundation-exam-questions-ip/

The YouTube series of videos of PRINCE2 from SimpliLearn is pretty good for your exam. I listen all of them.

https://www.youtube.com/watch?v=bBxeXC8K8lM&list=PLDe5459FKLk7ME9CanSVi4igdhcHZBGzE

Of course, you can buy additional books but don’t buy the Dummies book, Passing the PRINCE2 Exam for Dummies. This book is completely hopeless to me.

Posted in Community, Computers and Internet | Tagged , , , , | Leave a comment

DateTime.ParseExact String was not recognized as a valid DateTime

This short blog shows one of the possible causes of the error “String was not recognized as a valid DateTime”.

You have a code

CultureInfo provider = CultureInfo.InvariantCulture;

DateTime dte = DateTime.ParseExact(“2017-07-28”, “YYYY-MM-DD”, provider);

The code seems fine but actually it is not. The code below will not cause the problem.

DateTime dte = DateTime.ParseExact(“2017-07-28”, “yyyy-mm-dd”, provider);

Posted in .Net, Community, Computers and Internet | Tagged , , | Leave a comment

Message=ORA-01843: not a valid month

This short blog is to resolve .NET C# insert a record to Oracle table with date format

You will very likely get the error Message=ORA-01843: not a valid month when you insert a record to Oracle table with date format using .NET C#.

You might get a link like https://www.tekstream.com/resources/ora-01843-not-a-valid-month/ and telling you to do like.

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(15/07/99, ‘DD/MM/YY’);

So I will do this to my statement

INSERT INTO BILLS_OF_LADING (NBR, LINE_ID, CREATED, CREATOR) VALUES (‘TESTIN1’, ‘MAE’, TO_DATE(2001/03/19, ‘YYYY/MM/DD’), ‘HILMY’)

Unfortunately the error is still there because you also need the single quotation mark for the date itself

INSERT INTO BILLS_OF_LADING (NBR, LINE_ID, CREATED, CREATOR) VALUES (‘TESTIN1’, ‘MAE’, TO_DATE(‘2001/03/19’, ‘YYYY/MM/DD’), ‘HILMY’)

Posted in .Net, Community, Oracle | Tagged , , | Leave a comment