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 !

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!

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!

SSIS - Split records and export to flat files

Today's issue that we've a source database Sybase ASE, and old version is being used as a replica, I'm trying to extract some records, it's almost 750K to 1 Mil records and it needs to be exported to flat files and sent somewhere for further processing.

The files has to be no more than 10K records a file and it needs to get a specific naming convention...

I've done it differently than what I'm showing in the demo here... and I'll explain why...


the main component in the package is the "For Loop Container", then inside the container I'll have a "Data Flow Task".

I'll create 4 variables as shown below..

 

 

 

Add a "For Loop Container", configure as attached.. to use the variables and set the limit to 100K here

 

 

 

 Basically the first task, is a "Script Task", I'm only using it to debug my variables in a message box, enable or disable to debug, you could use "BreakPoints" which I've used in my original package and here only it's for demo.

 

 The way I'm debugging is so easy, just to display a message with the current variables' value to check if I'm heading in the right way!  again use breakpoints, add a watch and check your variables values...

 

 The data flow task...

I'm using a data generator task, to create my input rows....you can use your own source tables of course, I was reading from a sybase ASE database, an old version which doesn't support the Row_Number() function, so I couldn't build a column for the row numbers, and yes...I thought about a SQL server staging table...yes but it wasn't an option !

 

 I'm generating 520,000! yes weired number but I want to show the rest of the 20K records will get into a file of their own!

 

Just a row count of all the rows , please note that it'll run 6 times and it's not efficient...
You could use an "Execute sql task" to get you count(*) in the "Control Flow" and that will run 1 time, but here I'm using the data generator task....

 

Here it's my optional "Script component" to create a Row Number column, again I'm using that because I'm using the data generator task..or if you're reading from a flat file directly or if Row_number is not an option and no staging SQL table either !

 

 Conditional split to filter which rows where exactly need to write to our destination...

And here I'm setting the flat file destination..

 

 

I've an expression over the connection string to get me the file name dynamically to reflect which records in the file.

 



The last step here is a script task to update the variables' values...I'm not really sure why I've it in the control flow, but you can do it in the data flow task !

 

 

 

 

That's it! the folder when it's empty...

 

 

The folder when it got the files !!

 

 

Wrap up... again.. you don't need to debug your variables
use the data generator task...
use the row number task
 
or even the split task ..Yes !!  if you're reading from your source table and not from a data generator task, you might build your query to use the 2 variables and get only rows where the RN between these 2 variables... I didn't illustrate that here for simplicity.
 
it'd look like that...

 

 And yes I've an index on the RN column ...


The last screen shot here is not meant for people with weak hearts !! it's scary!!

 
 
Hope I've explained it clearly... and hit me up if you have a better way of doing it...

NJSQL Presentation - Let's talk dynamic

Let's talk dynamic - SSIS variables and expressions

So yesterday I was honrored to speak before the NJSQL group about "SSIS variables and expressions" I do apologize for my lengthy presentation as I went so deep into small details... but I was just trying to go with the flow, and naswering the questions...

I've attached a RAR file,  containing the slide deck  along with the demo scripts.  I hope you get as much out of SQL Server as I have, and Thanks for coming!

The slides can downloaded from https://www.box.com/s/9lvnf2cy3mdbluy8ykqo

SSRS - Subscription comments location.

Today I was facing a client's problem in SSRS to edit the comments text for over 120 reports to point to a server name (Host Name) instead of IP, to begin with, it was a bad idea and judgment from the person who created the report. and secondly it's rarely to see a server that gets an IP change too!

But the two issues could happen and it's ok, let's face it and fix it.

Here's a screen shot, If you're not familiar with the subscription comments.

To locate it, just navigate to SQL server management studio (SSMS), and browse the :

[ReportServer].[dbo].[Subscriptions] Table

the column that has the comments is [ExtensionSettings]

just replace the text using your T-SQL query and you're done! the data type of that column in nText, so it won't with T-SQL replace function.

so you might use...



UPDATE  S
  SET     S.ExtensionSettings = T.ExtensionSettings
  FROM [ReportServer].[dbo].[Subscriptions] AS S
  JOIN (
  SELECT [SubscriptionID], REPLACE(CONVERT(VARCHAR(MAX),[ExtensionSettings]),'162.162.162.162','HostNameServer')  ExtensionSettings
  FROM [ReportServer].[dbo].[Subscriptions]) AS T
  ON S.SubscriptionID  = T.[SubscriptionID]
 


Hope that helps someone...
Jason


Code has been formatted using...http://extras.sqlservercentral.com/prettifier/prettifier.aspx