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)

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






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!














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 ^_^



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




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 ^_^

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 ^_^

SSIS stop warning same user when opening package

Today I have an assistant to help me how to create package by using SSIS

but He have got the problem when he tried to open the package was created by me

It have error such like "The owner who created these package were different from the one who are using"

He have to clicked "OK" about 20 times to accepted those warning!

That made me not happy for this situation then I tried to find the solution from website but seem like no body ever met with this problem like me before

Then I have to asked this solution from an other developer, He suggested me how to solved that problem and unbelievable, It work!

STEP-1
Please see the error as it showed me on the popup



STEP-2
Close Visual Studio program that, and open it again by click right on the menu on the name of program "Visual Studio" then select "Run as administrator", This is help, even at the first time you have to accepted "OK" but after you save package and close window then always run Visual Studio as Administrator, This 's way can help you feel stop to upset. ^_^ 


Have a great day and enjoin life!




Change Data Type in SQL Table



No No No!
I tried several times to changed my data type in table of SQL Server but it was always said "Could not save!"

I was wondered why? I ever have changed the data type in MySql table very simple by click on the field and select the data type as I wanted but for SQL Server I could not do like that.

However I have a solution ^_^



STEP-1
Click on "New Query"



STEP-2
Fill your code as details below but please change tb_name and field_name as your table data, nvachar(20) and int(10) they are new data type I wanted to change.

ALTER TABLE tb_name ALTER COLUMN field_name1 nvarchar(20)
ALTER TABLE tb_name ALTER COLUMN field_name2 int(10)

STEP-3
Click on "Execute" and now your data type field have changed to the new one as you needs ^_^





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


Change blogger to real your domain name, very simple!

THIS IS FOR SETUP WITH GODADDY ONLY !
Wow! I can say it was such a wonderful site for me to create a personal website just spending time only one day or maybe more or short that depend on your skill and environment too ^_^.

After I knew about blogger.com I fail in love this tool so much because I can have my own website and post simple content as I needs.

However blogger it suitable for website developer who do not like more complicated and if you know a bit about the website you can have your own site in one day!.

And now it such so amazing! you can have your own domain instead blogger.com for the last name. You do not worry to have long last name on your domain name such as www.mymemoboxs.blogger.com, We can change to only www.mymemoboxs.com, That sound pretty cool! ^_^

Okey! let's see what we can do to make the personal domain name on google blog.


STEP-1
Please make sure that you have already have gmail account then you can use any tools of google products and for this details, let's me skip please ^_^ then go to our topic, Okey! after you have create your own blog with blogger.com and sign in and click on your blog name as you had build and click at "Setting", The menu tools on the left site of the page, you will see the page set up as below



STEP-2
The page will present you in basic default setting, Look at Blog Address, now you will see your full domain URL and have the last name is blogspot.com, Fill you domain name in Blog Address for example  "mymemoboxs.com" then click on "+Setup a 3rd party for your blog"




STEP-3

What you will see after click on "+Setup a 3rd party for your blog", blogger will generated code for put in CNAME on your domain name configuration as below, You have to copy those code to add on your CNAME, for me I have register my domain name with godaddy.com,  I loves it because very simple to use however I also do not like some thing after they have changed style, There are many too popups, Okey! now go ahead with our job.

STEP-4

You have to sign in to Domain name site for add CNAME, I guess other domain service site will have this feature too. And here it's a godaddy style, Go to "Domain name" management , Click on your domain name that you have register


STEP-5
Click on "Add Record"



STEP-6
Selected option on CNAME,


STEP-7
Copy code from blogger.com as STEP-3 at line number 1111, We need to add
"www" in Host box and number 2222  add in Point to box, That not finish yet, you have to edit an other CNAME after save


.WARNING! Please save all alert godaddy remind you and also click on "Finish" too


STEP-8


Edit www CNAME, the default of godaddy it was set to www in Host box and  @ in Point to box but now We have to change to an other one instead here, leave www as it was ever been but for @ replace it by "ghs.google.com" and save


STEP-9
And now every look perfected, You just return to your blogger.com and go to "Setting" menu on the left hand again, Fill your domain name then click save, and now you can go to your website by use your own domain name as you wishes ^__^ CHEERS!!!
SEE! before you need to wrote www.mymemobox.blogger.com but now I can go my site by use my own Domain name is www.mymemoboxs.com, It's such a pretty cool!


Enjoin your code and Enjoin your life ^_^