Conquering errors in T-SQL and SSIS: Act 1 Scene 1

Problem background: So I have four tables in a database. Table1 has four columns, Table2 has 3 columns, Table3 has eight columns and Table4 has 10 columns. Table3 and Table4 have a same column called Year. The datatype for the Year column is nvarchar(255). The data in the Year column in Table4 is coded as 2001-02, 2002-03 and so forth. All four tables have one common column called as schoolCode dataype is varchar(255).

Problem objective:
To write a T-SQL query that can extract all columns in all four tables based on the year and then save it into a new table.

My initial faulty solution: 

I wrote a T-SQL query  select * from Table3 union select * from Table4 left join Table3
on Table3.year=Table4.Year

When I executed this query, I got the following error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Solution:

Make sure that all the statements using UNION, INTERSECT or EXCEPT operator have same number of expressions(columns).
Use column names instead of using * in the select list.

So the correct T-SQL query should be

select Table3.column1, Table3.column2 from Table3 union select Table4.column4, Table4.column8 left join Table3 on Table3.year=Table4.Year

Ensure that in the select statement you have an equal number of columns selected from the selected tables. What I mean is that if suppose you select two columns from Table3 and six columns from Table4 then you will again get this error on run time or on executing the above query.

Tempdb “Monster” in SQL Server 2008 r2 swallows free hard disk drive space

In my quest to transfer excel data to SQL Server today, I was stumped by an error message. This error sprung up as soon as I had executed a query in SQL Server 2008 r2 that joined two tables. The error message was “Could not allocate space for object ‘dbo.SORT temporary run storage:  in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full.  Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

You will see that the primary partition of your HDD has turned red in colour and that Windows OS is prompting you to clean up HDD space.

Why this error message comes up?

Answer is that when you use SQL Server to execute any operation on the database be it execute a query or sort a table it will process and store the query in a temporary location called as “registers” technically. In SQL Server this is called as tempdb.

Solution:

First, check what is the size of the tempdb folder. Its location is shown below. Its generally located at

tempdb

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SAMSQLSERVER\MSSQL\DATA\

In the above path, notice the text that is bold. This will be user specific instance of your SQL Server.

The easiest solution is to close SQL Server application and restart it again. If this doesnt solve the problem Then navigate to Services.msc and stop the SQL Server service & SQL Server Agent service. Once these two services are stopped, navigate to the tempdb location again and you will see that it has released the space that it was occupying earlier.

Optionally you may set the recovery model of tempdb to SIMPLE

References
Optimizing tempdb Performance in SQL Server 2008 R2
To view or change the recovery model of a database

How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 3

Greetings reader.

Pre-requisite: This post is in continuation to series on “How to move data from Excel to SQL Server2008“- Act 1 Scene 2

After you have configured the excel connection manager, the next step is to set the actors for this play in existence. Meet “For each loop container”. You will find it in the Toolbox under “control Flow Items”. So you have to drag and drop it in the ‘control flow’ space as shown in as shown in Figure 13.1

Figure 13.1: Control Flow & For each loop containerFigure 13.1: Control Flow & For each loop container

For each loop container as the name suggests iterates or hops from one file to another file within the directory. Figure 13.2 shows the properties that you must set for this actor. Don’t forget to change the ‘Delay Validation’ property in Package, for each loop container from False to True.

Fig 13.2Figure 13.2: Package property

Also ensure that you have set the property of your project for “run 64 bit…” to False. By default its selected as ‘True’. This is shown below

Fig 13.2.0Fig 13.2.1 Now, within the for each loop container, you will now drag and drop the “Data Flow” task that is also in the “control flow items”. Don’t forget to change the ‘Delay Validation’ property in Data Flow container from False to True. Now double click on Data flow or alternatively you can click on the ‘data flow’ tab on the top of the screen. You will now see the Data Flow screen as shown below in Fig. 13.3

Fig 13.3Figure 13.3: Data Flow Task

Once you are in the Data Flow screen, next Drag and drop the Excel source on the blank screen. Remember you will not see the Excel source populated in the toolbox unless you are in the Data Flow screen. The same is shown in Fig 13.4

Fig 13.4Figure 13.4: Excel Source

To configure the excel source, you first have to double click it so as to open it. Its configuration is illustrated below in Fig. 13.5

Fig 13.5Figure 13.5: Configuring Excel Source

Important points to remember

  • Please ensure that you have set the value “True” for Delay Validation in For each loop container, Data Flow, and Excel Source and package properties.
  • Make sure you have set the disabled the run 64 bit runtime value to False.

If you were able to configure the excel source then you should see it without the red x as shown in Fig 13.6

Fig 13.6 Fig 13.6: Excel source configured

Next, you will drag and drop the flat file on the Data flow screen. As shown below in Fig 13.7

Fig 13.7 Fig 13.8

Fig 13.7: Configuring Flat File Destination

So now when you double click on the Flat file destination, you will see a screen like Fig 13.8

Fig 13.9.0 Fig 13.9

Fig 13.8 Configuring a Flat File destination

After you have click on OK button as shown above in Fig 13.8 you should see a screen like the following Fig 13.10 Configure it as shown and then click on the OK button. When you have done so, then you have to click on Mappings as shown below

Fig 13.11Fig: 13.9 Mappings warning

So you have to click on Mappings so as to make sure that the input column mappings are correct to the output column. The same is shown below

Fig 13.11.1Fig 13.10: Configuring Mappings

Once you click on Ok button you have successfully configured your Flat file destination. The same is shown in Fig 13.10.

Fig 13.12Fig 13.10: Source & Destination Configured Successfully

Great! You have successfully configured the source and the destination. Now when you run or execute the package you should see an output similar to the one shown below

Fig 13.13 Fig 13.13.1 Fig 13.13.2I hope you have enjoyed it so far. For I have equally enjoyed writing this post. Feel free to post your comments. Next post I will explain how to achieve the same process but this time the destination will be SQL Server. Till then stay safe and take care.

How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 2

Today, I will discuss on how to configure variables and excel connection manager in BIDS 2008. The same can be applied to BIDS 2012 and later too.

I spent over a week trying to find out a method by which I could read multiple files into a database. Initially, I began with trying to read multiple excel files into SQL Server database table. No luck, I had to face many errors to which I couldn’t find any satisfactory solution. Then I devised a strategy. First break the problem at hand into sub-problems and then try to solve them. This strategy in Computer Architecture is termed as “Pipelining”. Anyway, here I’m not going to discuss on Pipelining. Continuing further, I decided to try reading a simple excel file and writing its content to a text file. By the way I’m using MS Excel 2013. I then created two excel files named Book1.xlsx and Book2.xlsx. This example will work for MS Excel 2007 too. The following figure shows the file content of the two Excel files.

Book1Figure 1: Book1. xlsx
Book2

Figure 2: Book2.xlsx

As already stated in my previous post I’m using Business Intelligence Development Studio (BIDS) 2008 and SQL Server Enterprise edition. Now, I will detail on how to transfer data from multiple excel files into a flat file or a text file. In the next post, I will describe on how to achieve the same task but the destination will be SQL Server database.

Before I proceed any further, a couple of points you must remember if you don’t want to pull your hair like I did while grappling with BIDS. I’m going to underline them because they are important points to remember.

Important points to remember

  1. Ensure that the structure of excel file is same. By similar structure I mean if you look at Figure 1 & Figure 2 above, you will see that both of these files have exactly two columns each. So this is meaning of same structure. Your extraction project will fail if the source files (in this case the excel files) are different to each other in structure.

Alright, let’s begin now. Open up BIDS 2008 or Visual Studio 2012. Steps on how to create a new package has already been detailed in my previous post. If you have not read it, I recommend you do so before continuing further here. Assuming, you have created a new project and given it a worthwhile/suitable name. So the very first step is to create two variables with package level scope. How to do this is easy. Ensure that you are in the Control flow tab as shown below in Figure 2.0

controlFlow

Figure 2.0 Control Flow

 Then click on the variable menu as shown in Figure 2.1

variable2 Figure 2.1 Variables menu

Next you create two variables with Package level scope and give them the default values as shown below in Figure 2.3

createVariable variable3

Figure 2.3 How to create Variables and assign values to them

The next step is to create an excel connection manager string. So right click on the Connection manager as shown in Figure 3

connectmgr Figure 3: Add a connection

and then choose “New Connection” as shown in Figure 4 connectmgr1 Figure 4: Add Excel Connection Manager

When you have clicked on the button Add as shown above in Figure 4, you will then see the following as shown in Figure 5 connectmgr2 Figure 5: How to configure Excel Connection Manager

As shown in Figure 5, step 2 states that you have to choose the version of your excel software. So if you are using a version of Microsoft Office earlier than 2010 then you will choose “Microsoft Excel 97-2003” but if you are using a version of Microsoft Office 2010 and later then you will choose “Microsoft Excel 2007” from this drop down list. Also I have suggested in step 3 of Figure 5 that you remove the check mark on the box because if you leave it there, then the data will be copied with the row names each time a file is read. This anomaly is shown below in Figure 6. If you do not want the output data in this format then remove the check mark. column names So when you click on the Ok button, you will on the bottom pane of the Connection Managers, your Excel connection manager created as shown in Figure 6 excelconnmgr Figure 6: Excel Connection Manager

Now, you want to set some properties of the Excel connection Manager which are as follows; If you click on the Excel connection Manager once it will be highlighted and you will see the properties window as shown below in Figure 7, excelconnmgrProperties Figure 7: Excel connection Manager Properties

You will notice a small + sign to the left of the Expression. I have magnified it and shown below in Figure 8 expressn1 You have to click on this ‘+’ sign so as to expand it. When you do so you will see an ellipsis button on the right of it. ellipsis Figure 8: Ellipsis button

Click this button and the Expression builder window will open up as shown in Figure 9 expressn2  Figure 10: Expression Builder Property

In Property column click on the row to choose “Connection String” as shown in Figure 10 and then Under “Expression” click on the ellipsis button and the “Expression builder” window will open up as shown in Figure 11 expressnBuild Figure 11: Expression Builder

Important Point to remember

Ensure that the expression is typed as follows in the “Expression” box as shown above in Figure 11. And the same is shown below in Figure 12.

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+@[User::filePath]+@[User::fileName]+”;Extended Properties=\”Excel 12.0 XML;HDR=YES\”;”

 expressn Build Figure 12: Expression Builder and Evaluation

Please note that it is this expression that iterates from one excel file to another excel file. So if you get this wrong, you will not be able to read multiple excel files.

Now that we have successfully configured the connection manager, perhaps you need a break for I need one too. In the next post I will detail how to use this connection manager and the variables defined above to dynamically read multiple excel files and populate them into a text file.

Till then eat healthy, stay safe and take care.

How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 1

So, I have undertaken a massive BIG project in Educational Data Mining which the first step is data collection. After collecting the data and most of it is in excel format. So to make any sense out of it was imperative that I convert it to a relational database format. Now this gets interesting, for I have a couple of choices to accomplish this feat;

a. Manually copy and paste the data which is like “Yucks”… and really I have no intention to go insane at such a young stage of my life :-)

b. Programmatically, but how….? Million dollar question.. Read on,

  1. Ensure that you have first installed Visual Studio 2008 and then download and Install MS SQL Server 2008 Enterprise Edition R2 and install Service Pack 1 for the same. The benefit of installing SQL Server 2008 is that it comes bundled up with Business Intelligence Studio. You must install Visual Studio should first followed by installing SQL Server 2008. I have wasted several productive hours on trying to integrate higher versions of VS and SQL Server has led me to believe that this is the correct method to avoid throwing your computer out of sheer frustration.
  2. After the installation is completed you can restart your computer.

In this example project, I will describe on how to import data from excel table into SQL Server 2008 by using the SQL Server Integration Services (SSIS) tool.

Let the fun begin

Assume, you have an excel spreadsheet of student records that you want to import into SQL Server. Given below is a sample screenshot. Student records Sample

 Step 1: Create a SSIS Project

Go to SQL Server Data Tools or Visual Studio and then choose to create a New Project. Ensure that you choose the right project template as shown.New Project

SSIS will now create a new project, and also (by default) a new package too, Package.dtsx that is the default naming convention. There are two ways you can see that this is what’s happened. One is that you can see the package in Solution Explorer:packageName

(if you can’t see Solution Explorer, choose‘View’ from the top menu bar and then choose ‘SolutionExplorer’ from the menu to show it. Alternatively you can also press the keys ‘Ctrl’, ‘Alt’ and ‘L’ together on the keyboard and release them together to show the solution explorer window.)

The other one which you see shown to you on the screen packageName1Please note that by default you are put in Control Flow view, which is like a flow diagram showing the sequence in which tasks that you create will execute.default view Step 2: Create a connection to the SQLServer Database

Method 1: If you look at the solution explorer, You will see ‘Data Sources’. Right Click on it and choose ‘New Data Source’. Add new data Source Wizard

b. When you click the button ‘Next’, you will see the following: New Data sourceBy default the radio button titled ‘Create a data source based on existing or new connection’ will be selected. Click the button ‘New’ to create a new data source. When you click the New button you will see this connectionMgr10

c. As shown in the above screenshot if you have chosen the server name and the database name correctly and when you click on the button ‘Test Connection’ you should see the following ‘Test Connection succeeded’ message box. connectionMgrSucess

d. Finally, if you look at the Solution Explorer, under Data Source you should see the database connection as shown: connectionMgrSucess1

Hope you were able to accomplish this much. If you have encountered any problem at any step, please leave your comment and I will be happy to answer it for you. In the next post I will show you how to create tables in SQL by using the Business Intelligence Development Studio (BIDS) 2008, populate data from excel files into sql server database. Till then stay safe and be well.

Thank you for reading.

SSIS unable to bulk copy data you may need to run this package as administrator

I have started on a project work. One of the preliminary tasks is to extract data from excel tables and populate them into SQL Server Tables. One method was to do a copy and paste but the data size that I’m dealing with is in several millions of rows.SSIS

Therefore the alternative and easier solution is to use SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications.

So I installed MS SQL Server 2014 Enterprise edition with Business Intelligence package in it. In another post I will detail the steps to create a simple ETL package but for now I will stick to the solution of this problem.

The solution is instead of using SQL Server object use the OLEDB Destination instead.  Otherwise you may need to edit your local policy to grant the “Create global objects” permission in order for the SQL Server Destination to work properly i.e.

“Users who execute packages that include the SQL Server destination require the “Create global objects” permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the “Create global objects” permission.”

SQL Server Destination Reference http://msdn.microsoft.com/en-us/library/ms141095.aspx.

Error: Python version 2.7 required, which was not found in the registry- Solved

Python errorToday while installing, scipy for Python ver 2.7 I came across this error message on my Windows 7 OS 64 bit system.
Please note, I already have Python 2.7 installed.
The solution is;

1. Open notepad and copy paste the following code in it.

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python]
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore]
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7]
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\Help]
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\Help\Main Python Documentation]
@="C:\\Python27\\Doc\\python276.chm"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\InstallPath]
@="C:\\Python27\\"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\InstallPath\InstallGroup]
@="Python 2.7"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\Modules]
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Python\PythonCore\2.7\PythonPath]
@="C:\\Python27\\Lib;C:\\Python27\\DLLs;C:\\Python27\\Lib\\lib-tk"

2. Save the file on desktop as key.reg

3. Click Start menu on desktop then click on Start search and type regedit and press enter key.

4. Registry editor window opens. Click on File menu and then click on Import. Navigate to desktop and then import the file named key.reg. You will see a message that will say something like the key was sucessfully imported.

5. try installing scipy again. It will work

Follow

Get every new post delivered to your Inbox.

Join 36 other followers