Tuesday, April 15, 2014

T-SQL Month Name,Number,Start and End of month

Hi,

Today's requirement is to create a month's table, to be used in a SSRS report parameters, to be passed to a stored procedure.

the parameters needs to have Month Name, Number, Abbreviation, Start and End of month.



Please find the T-sql code below:



;WITH     months (MonthNumber)
AS       (SELECT 1
          UNION ALL
          SELECT MonthNumber + 1
          FROM   months
          WHERE  MonthNumber < 12)
SELECT   MonthNumber,
         DATENAME(month, DATEADD(month, MonthNumber, -1)) AS [MonthName],
         UPPER(LEFT(DATENAME(month, DATEADD(month, MonthNumber, -1)), 3)) AS MonthAbbr,
         DATEADD(month, DATEDIFF(month, 0, CONVERT (VARCHAR (2), MonthNumber) + '/1/' + CONVERT (VARCHAR (4), YEAR(GETDATE()))), 0) AS StartOfMonth,
         DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, CONVERT (VARCHAR (2), MonthNumber) + '/1/' + CONVERT (VARCHAR (4), YEAR(GETDATE()))) + 1, 0)) AS EndOfMonth
FROM     months
ORDER BY MonthNumber;  


Hope that helps..
Jason

Sunday, September 1, 2013

SSIS trim leading zero from a string


Today I was checking a question on one of the forums about trimming a leading zero from a string, and the string has characters, also has middle and ending zeros, so the methods that comes to your mind quickly won't work !!  If you're thinking about converting to INT then to a string again !

so I thought a bout a script component that uses the .NET (String.TrimStart Method) you can read about it more here : http://msdn.microsoft.com/en-us/library/system.string.trimstart.aspx

here's the input scenarios that I came with and the output results....



Steps:

1-I created a flat file (TXT) with punch of rows to test
2-Only one output from my flat file for simplicity, named Col0

3-script component (transformation Type), using Col0 as input.



4-created an output column Col0Cleaned
 

 

5-added the above highlighted line... and thats' it!

 

Please let me know if you have any scenarios that this solutions couldn't fix!
package and solution can be downloaded from here....


Hope that helps ......

 

SSRS Strip Domain from User!UserID

In SSRS, if you want to be fancy and display the USER NAME in the header or footer of your reports, like what I do.  SSRS uses Domain\UserName format... So someone this morning was asking in the forums about how to strip the domain out.

So I provided two solutions...


="Welcome " & MID(User!UserID,InStr(User!UserID,"\")+1, Len(User!UserID))

="Welcome " & =right(User!UserID, InStr(StrReverse(User!UserID),"\")-1)

Hope that helps someone...

                                           


Tuesday, April 30, 2013

SQL server date formats

Today, I've stumbled upon a nice resource of various SQL server date formats, and here I'm sharing it.

http://www.sql-server-helper.com/tips/date-formats.aspx

hope it helps when converting from one format to another !

Wednesday, April 17, 2013

Change SSIS Package Store location


SSIS stored the packages on 2 locations, on MSDB DB and on the file system.

If you're using the file system, it's very important to make sure that your package store location
points to the location of your packages.


The default packages location is:

C:\Program Files\Microsoft SQL Server\90\DTS\Packages
C:\Program Files\Microsoft SQL Server\100\DTS\Packages
C:\Program Files\Microsoft SQL Server\110\DTS\Packages

and the file that needs to be edited, by default located at :

(2005) C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
(2008) C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml
(2012) C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

Just make sure to search for "MsDtsSrvr.ini.xml", you might have installed SQL server on a different
drive or location.


Edit that XML file to point to your current location....

After you modify the service configuration file, you must restart the service to use the updated service configuration.

Please refer to this post on how to restart the SSIS service. http://www.jasonyousef.com/2013/03/restart-ssis-service.html

now refresh your SSIS FIle System Folder.


 Now, I can refer you to this article if you need to get the list of these packages http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Hope that helps!

Tuesday, March 5, 2013

Restart SSIS Service

If you need to check if the SSIS service is installed or if whether it's running or not.

If you need to know more about the SSIS service, let me refer you to this post...http://www.ssistalk.com/2009/11/04/ssis-what-does-the-ssis-service-actually-do-anyway/

Anyway...

You can pull the RUN command and type SERVICES.MSC and you'll get to the list of the locally installed services, or go to :
CONTROL PANEL --> ADMINISTRATIVE TOOLS --> SERVICES

Search for SQL server Integration Services, by clicking on S or scrolling down.

 
right click and you'll get all the options like START/RESTART/STOP.

Hope that helps!