Category Archives: SSIS

SSIS Configurations – Deep Dive Session

Khilitchandra's Business Intelligence Blog Posted on by

Hello everyone, Here are the slides of my session of “SSIS Configurations – Deep Dive” – @ Microsoft SQL User Group, Singapore Thank you very much for all your support 🙂 My apologies for machine issues and I could not show you demo. I will talk to team and ask for 20 minutes to show you this demo during next sessions (if possible). Event Info: SG SQL User Group Meeting – Oct 2015 http://www.meetup.com/mssgug/events/228948056/ Kindly stay tuned for more upcoming events @ Meetup page.(Group Name: SG SQL User Group). See you in again & thanks for coming. Thanks, Khilit 🙂

Continue reading →

The Story of Precision, Scale, and Length in SQL Server

Khilitchandra's Business Intelligence Blog Posted on by

Hi guys, Did you ever faced issues with BIG mathematical figures – related to either overflow or any  other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic  operations you do with SQL table in SSIS etc. Well, check 1 more time, if you have any issues in your database design related to column  data type which stores the BIG numbers. I ran into this issue couple of times, and reason behind this issue was same all the time. So  decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing … Continue reading →

Tracking Down Ghost Characters: WHERE Clause Does Not Work Properly In T-SQL Query

Khilitchandra's Business Intelligence Blog Posted on by

Hi guys, I would like to share an experience with you when I was working on 1 SSIS related issue. Scenario was: I have to exclude some records from Table A by matching some keys of Table B.  Table A Year Month CustomerCode <Extra Columns> 2012 JAN CX1000   2012 JAN CX2000   2012 JAN CD3000      Table B Year Month RecCustomerCode <Extra Columns> 2012 JAN CX1000   2012 JAN CX7000   2012 JAN CD8000      What is expected was – Table A’s following records: Year Month CustomerCode <Extra Columns> 2012 JAN CX2000   2012 JAN CD3000   … Continue reading →

MSSQL: Find total records of each Table in given Database

Khilitchandra's Business Intelligence Blog Posted on by

hi guys, i wanted to do a quick post on how you can find total number of records in each table of the database. here is the script you can use: USE <Your Database Name> GO ;WITH CTE_DATABASETABLESIZE (DATABASENAME, SCHEMANAME, TABLENAME, TOTALROWS) AS ( SELECT  DB_NAME(), SCHEMA_NAME(SO.UID), SO.NAME, SI.ROWS FROM  SYSOBJECTS SO  INNER JOIN SYSINDEXES SI  ON SO.ID = SI.ID WHERE  TYPE = ‘U’ AND SI.INDID IN (0,1) ) SELECT * FROM CTE_DATABASETABLESIZE /*ORDER BY SCHEMANAME     ORDER BY TOTALROWS ASC*/ ORDER BY TOTALROWS DESC Note: Customize this query according to your needs 🙂 Thanks, Khilit

Continue reading →

SQL Server User Group Event on 4th Oct 2012, Singapore

Khilitchandra's Business Intelligence Blog Posted on by

Hello friends, how are you doing? There is an event (SQL User Group, Singapore) on 4th October 2012. Following is the detail about the event. Send an email to “Chen Liang <liangchen@dataraid.info>” with your name, working place and contact number for reservation of your seat. There is no charge for all members of SQL Server Singapore User Group. You can become the member of this group by joining this group: https://www.facebook.com/groups/sqlugsg/ Topic SQL Server Integration Services 2012 Unplugged Time 4th Oct 18:30 – 21:00 Place One Marina Boulevard, 22CF-15 Speaker Dr. Wee Hyong, Tok Dr. Wee Hyong is a Senior … Continue reading →

E-Book Gallery for Microsoft Technologies

Khilitchandra's Business Intelligence Blog Posted on by

Hi guys, good morning !!! here is the list of cool FREE E-Books from Microsoft Press.

Continue reading →

Check if file exists: using SSIS

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, Many times, we have to do this simple checking “if file exists/available .. then proceed” in SSIS operations. And Microsoft did not put any facilities to achieve this task in SSIS with default components. People also have raised this concern to Microsoft, but Microsoft explains that they have more priority works to deal with so this feature is not coming soon. you can check complete story here. Follow these steps: 1. Create a Boolean variable (I call it a Flag_Variable). Assign default value to “False“. 2. Use “Script Task” to check if file exists in given location. And … Continue reading →

Important Fact: Default Logging in SSIS which store log entries in SQL Server

Khilitchandra's Business Intelligence Blog Posted on by

Hi all, Few days back I have posted an article on “Default Logging in SSIS“. You can refer that here. In that article I have forgotten to mention a behavior of “enabling a default logging in SSIS package“. Let me show you what you need to know about this fact. Fact: When you enable a “Default Logging” with SQL Server in SSIS package, it does not create any table (i.e. dbo.sysssislog) or stored proc (i.e. dbo.sp_ssis_addlogentry) at the same time of setting-up in SSIS package. But when you run your package first time, these objects gets created automatically.

Continue reading →

Customizing the Default Logging in SSIS

Khilitchandra's Business Intelligence Blog Posted on by

Hi Guys, Sometimes definitions are wacky. I thought of writing this article on default logging where I could modify it with my application requirements. And guess what… “Customization with default logging” title rendered in mind. Just for fun if you think about it, when you customize the default logging; it would be custom logging. But custom is also that where you define everything from scratch. So ultimately it makes 2 types of custom logging: one – by using somebody’s readymade logging work and extend it. And second – by developing it from the scratch. 🙂 Today I would show you, … Continue reading →

Configuring “Merge Join” Task in SSIS

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, Today I would like to tell you the required settings to be done for using Merge Join in SSIS. Merging the data from 2 different sources can be implemented in various ways. And that depends on many things like both sources are on same SQL server or on different SQL servers but available as linked server or one source is MS SQL Server and other is Oracle etc. But apart from all these, you concentrate on mainly 2 things, which will quickly get your job done. We know basic requirements of using “Merge Join” data flow component. Both … Continue reading →