SQL Server Import and Export Wizard step by step explained. Building your first SSIS package.

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"

4-From the start menu, navigate to "Microsoft SQL Server 2012" and select the "Import and Export Data" of your windows version 32 Bit /Vs 64 Bit"

The Wizard...

Once the wizard is up, you'll be welcomed with the following screen, unless you click on "Do not show this starting page again", so any subsequent runs this screen will not appear.

Press on next and you'll be prompted with the Data Source, which could be any data source you need to pull information from. such as Database table, excel file, flat files (Text File) or a delimited file.

I will be using my local SQL server engine to pull information from, specifically from the "Adventureworks Database".

Next, you will need to specify your destination.  Here I will be saving the table's data to an Excel file

Or a flat file (Text file)

Next, you might let the wizard guide you through your tables or views, or write your ready query to pull your desired information.

I choose the first option and here is the screen to select which table or a view I need to pull information from.

You could click on "Preview" to check your data, or "Edit Mapping" to map only the desired column to an output in your text file.



Clicking on Edit Mapping would give you the following screen shot.





 Next would prompt you to "Run Immediately" the package, or save it for future use or edit.

I will select all the options, since I need to run it and save it for later use, i will be saving the package in the MSDB database, you could use the file system if that is what you are looking for.

Next the wizard will display a confirmation summary.

Clicking on finish SOMETIMES turns to be successful and displays the following screenshot.
Otherwise, it will tell you what happened wrong and you will have the option to go back to fix it.

Checking the file existence, and voila! it has been created.

Connecting to the SSIS for checking  on the package if it was saved as promised or not, and voila!! it was saved, now you might edit it, run it or schedule it to run at your desired interval using the "SQL Server Agent"

Hope that helps someone, and please let me know if it wasn't clear enough.

For more info about the wizard, please check http://msdn.microsoft.com/en-us/library/ms141209.aspx

Change SSIS 2012 Catalog DB encryption algorithm

Hi,

By default, when you install the SSIS catalog, the encryption algorithm is  AES_256..
You can read about the various configurable options here.. http://msdn.microsoft.com/en-us/library/ff878147.aspx

To change it, you'll need to get the database in a single user mode, then use the  "catalog.configure_catalog" SP to change it. Yes you'd use T-SQL to control that now...

you may read more about it here http://msdn.microsoft.com/en-us/library/ff878042%28v=sql.110%29.aspx

so the whole script would be..

select * from catalog.catalog_properties

ALTER DATABASE SSISDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

catalog.configure_catalog 'ENCRYPTION_ALGORITHM','AES_192'

select * from catalog.catalog_properties

ALTER DATABASE SSISDB
SET MULTI_USER;
GO

Please note that you need to be a SSIS_Admin or SysAdmin to be able to do that...

Hope that helps someone...

A DBA’s Ethics T-SQL Tuesday #30

Hi, this month's T-SQL Tuesday is hosted by Chris Shaw, and the topic is around the DBA's ethics at the work place...
The database administrator (DBA) is the person whose responsible for managing the relational database and its access permissions.
Well, first of all I am not a DBA and never been one, But I am a developer, and I must admit that I share the same ethics and responsibilities towards the data that I deal with.  The ethics here are cross all  database professionals whose has access to the back end of the company's data, whom can access sensitive information about patients in health care organizations, or sensitive financial information about clients in financial organizations.
Every database professional should be striving for technical excellence to the best of his/her knowledge. they should and constantly invest in their career by learning and gain new skills.

Privacy and confidentiality is the most important.  Obtaining permissions and documenting access rights is vital in an audit situation and data theft.  And any DBA should be aware of his/her responsibility regarding the data and how to protect it from others.

Every DBA or database professional should have manners and conduct business in a matter of integrity, can't access information they shouldn't access, or try to snoop around client's personal information, or even around work co-workers personal information.  and resist the temptation.
So the question is, what's stopping the DBA? well it's his/her ethics towards the Job, career and the employer.

SSIS 2012 - New expression language functions(ReplaceNull,Left, Token, TokenCount)

SQL Server 2012 is full rich of new features for SSIS, really many new useful stuff, such as the script task debug and the renovated Flat File Source, that could handle uneven number of columns...


Today I am going to demonstrate 4 new SSIS expression functions...


1-Left

Which exactly the opposite of the RIGHT functions, and similar to the LEFT function in T-SQL, which allows you to grab a number of characters from your string, starting at point zero or in other words from the LEFT side of the string.
http://msdn.microsoft.com/en-us/library/hh231081.aspx

2-ReplaceNull

Which as the name means, it replaces your NULL values, with another string expression.  Before you would use ISNULL which returns Boolean injunction with a condition to change the value. ISNULL(LastName) ? "Unknown last name":LastName 
http://msdn.microsoft.com/en-us/library/ms141184.aspx
http://msdn.microsoft.com/en-us/library/hh479601.aspx

3- TokenCount

Which allows you to separate your string into tokens based on a specified delimiter, and returns back the numbers of occurrence of that expressions (Number of Tokens)
http://msdn.microsoft.com/en-us/library/hh213135.aspx

4-Token 

Substrings your string into Tokens based on a specified delimiter and brings back the number of requested token.
http://msdn.microsoft.com/en-us/library/hh213216.aspx

Let's use a demo as I learn better by examples...


The demo script as following..will create a test table, insert 3 records..

1 2 3 4 5 6 7 8 9 10 11 12 13

/* Create Table */CREATE TABLE FunctionsDemo( ID INT identity(1,1), Name VARCHAR(50), SSN Varchar(11), Phone Varchar(12)) /* Insert Test Records */INSERT INTO FunctionsDemo VALUES ('James','157-15-8877','201-342-3355') ,('John',NULL,'123-456-8965') ,('Liz','212-91-5618',NULL)

I'll basically add a "OLE DB Source" to get the records from the database, then a derived column to use these expressions goodies.


The package should look like that...I'll assume that you know how to use the source and will skip its configurations.

 And here's my expressions at work, in the Derived Column Transformation.

Which I’m using the above various expression, to get the last 4 digits of the SSN, to validate if the phone number in the right format, replace a null SSN or to get the area code from the phone number, which is simple and basic but will give you the idea of how you could use these new functions.
 And here's the results

The package was created using SQL 2012..

The package can be downloaded from https://www.box.com/s/1c5d52b9f0d42134068a

Hope that helps someone...

How to add a TABLE to blogger.com, blogspot.com

Hi,

Today I got the need to insert a table in one of my posts, I am lazy, and usually get around it by using a screen shot of my data, usually it's in excel or a SQL table, so it's already formatted in a table...

But for that specific post, I decided to dig around how to insert a table into my post...

There are several ways...you need to be a little familiar on using HTML or use an HTML editor, such as Microsoft Expressions, which I'm using...

All what you need to do is to click on HTML from the top bar...

And that will allow you to edit the HTML behind you post..

If you have a HTML editor, your solution will be by creating the table on your HTML editor, copying the code and pasting in your HTML post.

Or use a sample snippet code such as

<style type="text/css">.nobrtable br { display: none }</style>
<div class="nobrtable">
<table border="2" bordercolor="#0033FF" style="background-color:#99FFFF" width="100%" cellpadding="3" cellspacing="3">
<tr style="background-color:#0033FF; color:#ffffff; padding-top:5px; padding-bottom:4px;">
<th>Table Header</th>
<th>Table Header</th>
<th>Table Header</th>
</tr>
<tr>
<td>Table Cell</td>
<td>Table Cell</td>
<td>Table Cell</td>
</tr>
<tr>
<td>Table Cell</td>
<td>Table Cell</td>
<td>Table Cell</td>
</tr>
<tr>
<td>Table Cell</td>
<td>Table Cell</td>
<td>Table Cell</td>
</tr>
</table>

Which will give you that table....

<><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><> <><><><><><><><><><>

Table Header	Table Header	Table Header
Table Cell	Table Cell	Table Cell
Table Cell	Table Cell	Table Cell
Table Cell	Table Cell	Table Cell

Please note that the above snippet has been taking from this article, which has a very good tutorial, step by step on how to create a table...


http://blogknowhow.blogspot.com/2011/01/how-add-table-blogger-blogspot-post.html

Hope that helps someone...

SSIS Strip digits from a string.

Today's scenario is a "File Name" that contains digits and need to strip these digits out, it can be at the beginning of the string, middle or at the end.


If you are wondering, why there's digits! it's the size of that file and only comes at the end of the string, but just to demonstrate various scenarios that you might encounter.


For our demo, I'll create a table, has 2 columns, an ID and a FilePath column.
the output will be 4 columns, our 2 input columns along with 2 new columns, FileSize and CleanedFilePath.  Of course you can overwrite your FilePath with the digits stripped one.


Let's run the below script to create the test demo and insert 3 test rows.
<><>

SSIS Strip Digits

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

/* Create Table */ IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STripDigits]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[STripDigits]( [ID] [int] NULL, [FilePath] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] END GO /* Insert Test Records */ INSERT INTO STripDigits VALUES (1,'6590FolderProcessed') ,(2,'Patients32PCP') ,(3,'Manual1817') /* Verify Records */ Select * from STripDigits

Steps:

1-Basically I'll add a "OLE DB Source" to read from my SQL table, I'll assume you know how to configure that.


2-Will add a "Script Component", will use "Visual Basic .NET" as my scripting language.

Will grab the Input Column (FilePath)
Will Create 2 new output columns, a string for the cleaned File Path, and a File Size for the digits portion of the string.

 Add that VB Script...
I'm using Regular Expressions here, so simple 2 lines of code..


   

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Private StripDigits As New System.Text.RegularExpressions.Regex("\d") Private StripTxt As New System.Text.RegularExpressions.Regex("\D") Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Row.CleanedFilePath = StripDigits.Replace(Row.FilePath, "") Row.FileSize = CInt(StripTxt.Replace(Row.FilePath, "")) End Sub

Your package should look like that...Note that i'm terminating using a Multitask so I can use a Data Viewer t ocheck the results..

 And the results.... 

The package was created using SQL 2012, but ti's the same for SQL 2008.

The package can be downloaded from https://www.box.com/s/df9e48f7800927aac46c

Hope that helps someone...