Import data from Excel to SQL by SSIS

How to import data from excel to SQL server
The concept seem like import data from other format but you have to prepare your excel file in correct data.

Merry X'Mas and Happy New Year 2015
Wish all of you get lucky and happy life all of the year.










STEP-1
Prepare your excel file data in correct format.

STEP-2
Create new package and drag and drop "Data Flow Task" on Control Flow page

STEP-3
Double click on "Data Flow Task" box, then drag and drop "Flat File Source" then double click on "Flat File Source" box
 then you will see the popup for configuration as image below

STEP-4
Click on "Browse" button to select excel data source file as the part you saved on computer

STEP-5
Click on "Column" at the left menu for check fields on excel file, after that click on "OK"

STEP-6
Drag and drop tool on menu left is "OLE DB Destination" then select connection (you can see here how to connect to database)
and select table destination

STEP-7
Click on "Mapping" on the menu left for check your data field, you have to select data to be match with destination

STEP-8
If you see the error after mapping data, please be sure that your data type, data length is the same as table destination
if not, you have to convert data (you can see how to convert data here)

NOTICE!
This is very important, if your excel file have empty value, you have to check and convert to zero install (in case that column is numberic)

Read More

I can not install iTunes after uninstall new version

I really do not like the concept of iTunes some feature, Yes I am a big fan of MAC

Because they have unique design and always moderns. At the first time you may get confuse how to use mac products because they tried to design in simple way and not complicate but you have to learn a bit if you are not a big fan of apple.

Anyway I have iphone 4s and I loves it, It will be great if apple have new products by design same body like iphone 4s but should light then before, I still do not like the body of iphone 5 because it difficult for me to bring it any where by put it in to my pocket clothes. And one big thing that why apple do not develop iphone memory card like sumsung, that easy to use, we can go to fields just double click on memory card but for iphone we need to trough all fields by iTunes instead and a few week ago I have tired to installed a new version of iTunes and after I installed then I changed my mind to use the previous version of iTunes, It did't not allow me to install down version any more.

I tired to delete all iTunes field by go to register of windows but it still did't help, After I have installed and opened iTunes it said "You have to install a new version or iTunes library.ilt can not replace" or some thing like that (sorry I can not remember all of them error)

I tried many ways to fixed this problem and finally this may help all of you, the way it very easy, it seem to hard if we do not know but after we can do, it look very simple. OKEY! go ahead!

STEP-1
Uninstall iTunes by go to "Control Panel" -> "Programs and Features" -> "iTunes"->"Uninstall"




STEP-2
Go to iTunes library "C:\Users\AccountName\Music\iTunes" and Delete all fields there

Read More

SQL 2014, Set Primary Key, Remove Primary Key in Table

I still do not understand why I can not change data type of field and also if I would like to set "Allow Null" to be "Not Allow Null" then SQL Server stop my dream.

For set primary key in Table of SQL server that very easy, you just click right on the field and select "Set to be primary key" That's it!.

STEP-1
Click right on the field then select "Set Primary Key" but please be sure that field is not allow null and have no double data in field.

Or you can hard code by (Open New Query)
This can set multiple primary key in the same time (Please do not push in Execute ^_^)
ALTER TABLE tb_name ADD PRIMARY KEY (primarykey1,primarykey2)



STEP-2
After you have execute you have to refresh table and click right select "Design", then you will see now you already have primary key in your table.




How to remove Primary Key ?
Click right on the table and select "Design", you will see your fields and attributes of each field then you will see a key icon in front of field was primary key, you have to click right on that primary key field then select "Remove primary key" as image below.





Beware!
- If you want to set a field is primary key, must be sure that field is not allow null
- You have to be sure that field is unique data
- If you created field is not null before, you have to changed to null by use

ALTER TABLE tb_name ALTER COLUMN field_primary int not null


Special STEP Simple

Go to Tool -> Option -> Design then remove checked list as detail below
- Warn about different detection
- Warn about tables affected
- Prevent saving changes that require table re-creation

Good Luck and ENJOIN!

Read More

How to create parameters in Package of SSIS

If you do not want to configuration your select data in script query, you have to send the parameters to the package instead and I'm sure this way very helpful, OKey! how we will do?


STEP-1
Go to "SSIS" menu, select on Variables for create parameters.

STEP-2

Click at the first icon for create parameter, fill the parameter name and select data type, if that parameter is number, you should pick up "int" for data type but in this case the parameter is text then pick up string and for value slot you have to set default value.

Set parameters name

Select Data Type

STEP-3
Double click on "Execute SQL Task" box as image below.






STEP-4
After you have done in STEP-3 you will see the popup and click on "Parameter Mapping", then select the parameter in first query you want to set (Please know that arrangement parameters is very important)

STEP-5
Set parameters at Parameter Name column and Set 0 for parameter size if you want to be unlimited.

STEP-6
Go back to your query area by click on "General" and set =? instead value as example as below

Enjoin Life and Enjoin Living ^_^

Read More

Store Procedure How to create on SQL Database?

Why must create store procedure? How it's work?
At the first time I really did't see how was store procedure help me, because I could create code or query by use SQL or MySQL format but after I know about procedure that sound pretty cool!

1. Safe performance of database because even if you have many jobs must query or updated in same time, store procedure can help and better then use query by an other code
2. Easy to use and create

OKEY! let's create

STEP-1Click right at "Store Procedure" menu in your database list, then go to "New" and click on "Store Procedure"

STEP-2
You will see procedure area to build the code. The system will generated default value as details below.

STEP-3
In line "CREATE PROCEDURE [dbo].[your_store_procedure_name]" you have to changed to your procedure name, Then click on "Execute" for create new procedure. After you refreshed your database, you will see the new procedure name appears as you just created.

STEP-4
Close window on STEP-3 and click right on "Procedure_your_name" and select "Modify" to programming.




STEP-5
A simple code to delete data in period as selected data.
Beware, Please arrange parameters were match with your selection data.
Download Example Code Here

Read More

ispac because it is being used by another process ERROR!

ERROR!

Why must be error
It's over many times

No alert no alarm
I just wanted to complied

I have got this message after I tried to run my package again after it frozen, I tried to kill that struck process but it not allowed me do any thing, then I have to closed windows and shutdown program then started Visual Studio again

If you have got this message please do not worry, I have an solution and just found by myself that I would like to present ^___^

Error 1 System.IO.IOException: The process cannot access the file 'D:\SSIS\JObs\bin\Development\Project.ispac' because it is being used by another process.     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)     at System.IO.File.InternalDelete(String path, Boolean checkHost)     at System.IO.File.Delete(String path)     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0 0 STEP-1Error message appeared when I tried to run process after it frozen and restart Visual Studio again.

STEP-2 The way you just clam down and started run Visual Studio again then click on "Project" and selected at "yourjobname properties" as image below STEP-3 Then go to Configuration Properties click on Build you will see the default was "bin" on the OutputPath, You have to changed to an other name such as "binnew, newbin" or whatever you want to set up for a new name. Click on "OK" and run your package again, then you will see any problem was gone, that sound pretty cool! right? ^_^


STEP-4 And if you go to the path of your project, "jobname" you will see bin (old name) and bins (new name), I guess it because of the processing was struck while running package and now we just changed to the new name instead Hope all of you enjoin and Happy ^_^

Read More

Safe Made on Window 8.1

I never go to safe mode of Windows for a long time ago since I have changed to use Windows 8

And today I have to removed some program out of my computer completely, That way could not only remove from Control Panel

I have restarted windows and press on F8 after I have started my computer and Windows were starting up again but for Windows 8.1 It was not easy like that

However I have a trick for this way (Honestly I really do not understand some feature of Microsoft, Why they have to developed, Have they ever tried to use it before launch products, For example Window 7,8 they remove my starting program on the left pace out, It very not use full to go every where by desktop and I have to install shell for help, Anyway! ^_^ I blaming too much)

STEP-1
Go to starting up program and fill "shutdown /r /o /f /t 00" as image below



STEP-2
Click on
Troubleshoot
Advanced Options
Startup Settings
Click on Restart
and then push "F4"

After that windows will restart and go inside safe mode space for you.

Wish all of you happy and Enjoin ^_^

Read More