Another BI & Programming Blog - Jason Yousef

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 about 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 that's' it!   Please let me know if you have any scenarios that this solutions couldn't fix! package and solution can be do...

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 se...

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!

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...

A recent question about how to skip a failure inside a Foreach Loop Container , I'm sharing the answer as it's easy and apparently not everybody is aware of it. So let's assume it's only a simple Foreach Loop Container  that's looping over a files on a network location or even the local disk, getting the file names into a variable and passing it to a file system task to move these files over to another location. The first step will be by creating an OnError event handler for the file system task, and what I mean by that is just click on Event Handlers from the top and then click on the BLUE link in the event handlers and that's it :)   Then look under system variables in the variables window, find the "Propagate" variable and set it to false. So now The Foreach Loop Container is not aware that the file system task has failed therefore does not cause the container to fail and exit. OPTIONAL--you can use the failure P...

This is fairly easy and simple, the requirement is to loop over a folder full of .sql files, that has some queries and run them on the server! This could be a stress testing queries, create multiple object or drop and recreate indexes queries as in my actual requirement. We'll need a "For EachLoop container "to iterate over the folder, read the files' location into a STRING variable. Add a "Execute SQL Task" inside the container, setup the SQL server connection, then setup the query to be from a file connection. create the file connection, pointing to one of the files. set the delay validation to true, then setup an expression over the Connection String property. That's it!! The package can be downloaded at: https://www.box.com/s/928b2f993c819b64dd53 Hope that helps someone...

Today I got that error message while using the import and Export wizard on windows 7, running SQL Server 2008 R2, 64 Bit. I was importing from an Excel 2010 sheet. The solution is to download and install the " 2007 Office System Driver: Data Connectivity Components " From:  http://www.microsoft.com/en-us/download/details.aspx?id=23734 Hope that helps someone...  

Hi, Today I will be talking about the SQL Server Import and Export Wizard, which is one of the easiest ways to develop your first SQL Server Integration Services (SSIS)  package, and it so useful to export and import data from multiple sources to a various destinations, varies from relational database tables to text files. Through the demo I will be using SQL Server 2012 on top of a Windows 2008 Sever, but the wizard is exactly the same as it was in erlier versions. You start by learning the different ways to get the wizard up, and it is straight forward and can be launched from various locations 1-from the run commend or the command prompt, you might type "DTSWizard.exe" and hit enter, which will get the wizard up and running..  2-By right click on your database---> Tasks--->"Import Data" or "Export Data"  3-From SQL Server Data Tools (SSDT) , right click on "SSIS Packages" and select "SSIS Import and Export Wizard...