RSS

Tag Archives: SSIS

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

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 Decimals and Numeric values in table“.

I wanted to describe this with an example but there is already a very nice explanation by MSDN  on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx

You will notice in this article that, how any arithmetic operation will affect size and space  allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can  handle BIG mathematical figures during operations.

From MSDN, for your quick view:

Operation

Result precision

Result scale *

e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 – e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 – s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Important Note: Don’t forget to check for your SQL Server Version.

I hope you will find this informative.
Thanks, Khilit

 

Tags: , , , , ,

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

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  

  Read the rest of this entry »

 

Tags: , , , , , , , , ,

MSSQL: Find total records of each Table in given Database

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

 

Tags: ,

Check if file exists: using SSIS

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 Set Boolean variable’s value for later use.
(I have used C# for demo.)

Read the rest of this entry »

 

Tags: , , , , ,

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

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.

Read the rest of this entry »

 

Tags: , , , , ,

Customizing the Default Logging in SSIS

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, how you can tweak (1st type of custom logging :) ) the “Default Logging Framework” of SSIS and turn into “Custom Logging“.

Well, it’s really simple.
First you set the “Default Logging” – just configure the “Logging” on control flow tab in SSIS and done. What SSIS does for you internally is following list:

Read the rest of this entry »

 

Tags: , , ,

Configuring “Merge Join” Task in SSIS

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.

  1. Both sources should have data sorted.
  2. Columns which will take part in joining condition should have same metadata.

Now, how to configure “Merge Join” task. Just follow the steps explained in following scenarios.

Read the rest of this entry »

 

Tags: , ,

Passing Multiple Parameter Values from Parent Package to Child Package

Hi All,

I would like to bring to your attention a potential improvement in SSIS. When we need to pass parameter value from parent package to child package, we mostly use “Parent package variable” configuration type in child package. But when we need to read multiple values from parent package, we need to create multiple “Parent package variable” configuration type in child package.

There are few work-arounds in order to pass multiple parameter values from parent package to child package without creating multiple “Parent package variable” configurations in child package. But we do need a good method of implementing this requirement. So we can better concentrate on development of business solutions rather than work-arounds.

I have posted a suggestion to Microsoft regarding this. Kindly have a look at it and support it if you believe its worth.

Read the rest of this entry »

 

Tags: , , , ,

Ways to execute SSIS package

Hi All,

There are lots of methods documented and available on internet. But if you think about the execution of anything you may find more options.

Particularly for SSIS packages, there is a golden rule to be bear in mind when planning for executing ssis packages, i.e. you will need SSIS RunTime.

Following is the excerpt from my previous post “http://bigator.com/2012/02/17/the-task-scr-get-task-start-time-cannot-run-on-this-edition-of-integration-services-it-requires-a-higher-level-edition-ssis-2005-dtexec-execution-result/

Read the rest of this entry »

 

Tags: , , ,

The task “SCR Get Task Start time” cannot run on this edition of Integration Services. It requires a higher level edition. – SSIS 2005 – DTEXEC Execution Result

Hi All,
I was testing an SSIS package (Developed using SSIS 2005 version) using DTEXEC command line tool. But it got crashed and showed me long list of exceptions/errors occurred during the “TRY” of execution. (I used the word TRY here because I have logging enabled in my SSIS package, but it did not log anything, that means it did not even start.) And at the end I see this message – “DTExec: The package execution returned DTSER_FAILURE (1)”. That means package was failed.

The main error description was “The task “SCR Get Task Start time” cannot run on this edition of Integration Services. It requires a higher level edition.”

Read the rest of this entry »

 
2 Comments

Posted by on February 17, 2012 in DTEXEC, SSIS

 

Tags: , , ,