SSIS ตัวแปรวันที่ปัจจุบัน date_now

การสร้างตัวแปรวันที่เพื่อกำหนดขอบเขตในการดึงข้อมูล

สร้างตัวแปรดังนี้
date_start
(ตัวแปรหลักวันเริ่มต้น หากไม่ได้ใส่ค่าอะไร ตัวแปรนี้จะดึงค่ามาจากตัวแปร fix_date_start)

fix_date_start 
(ตัวแปรวันที่เริ่มต้น Default ไว้)

date_end 
(ตัวแปรหลักวันสิ้นสุด หากไม่ได้ใส่ค่าอะไร ตัวแปรนี้จะดึงค่ามาจากตัวแปร fix_date_end)

fix_date_end 
(ตัวแปรวันที่สิ้นสุด Default ไว้)

จากนั้นใส่ Function เพื่อกำหนดค่าให้วันที่เริ่มต้น
@[User::date_start] =="" ? (DT_STR, 4, 1252) DATEPART("yy" ,DATEADD("dd",-1,GETDATE()))+ "-01"
+ "-01":@[User::fix_date_start]


จากนั้นใส่ Function เพื่อกำหนดค่าให้วันที่สิ้นสุด
@[User::date_end] =="" ?
 (DT_STR, 4, 1252) DATEPART("yy" ,DATEADD("dd",-1,GETDATE()))+"-"+(DT_STR, 4, 1252) DATEPART("mm" ,DATEADD("dd",-1,GETDATE()))
+"-"+(DT_STR, 4, 1252) DATEPART("dd" ,DATEADD("dd",-1,GETDATE())):@[User::fix_date_end]

ปล.
เวลาเรียกใช้ ให้เรียกใช้ตัวแปรชื่อ date_start, date_end

Read More

SSIS Parameters in Different Type of Data in Package

How can I image that parameters in SSIS that quite different when you have to use it in different type of data flow. Yes I know that at first we have to create parameters in same level but when you have to select data by use different type of database you have to change the syntax for query, OKEY! let's see what is different.

Set Parameter for Execute SQL Task Step 1

STEP-1
You have to create parameters as below by click on menu  SSIS -> "Variables"

STEP-2
Define parameters as you want for example, year, month etc..

STEP-3

After you created and define all parameters as you want you have to set parameter for "Execute SQL Task", you have to set type of parameter as your data config on table of database

STEP-4

Then click on query of Task and you have to replace the value of parameter by use =? instead

STEP-5

But if you have to put parameter on "ADO Net Source" object, you have to replace it by =@parameter_name

Set Parameter for Execute SQL Task Step 2

STEP-6

Any if you have to use "Execute SQL Task" in same package and need to set parameter, you have to add parameter in that task again

STEP-7

After you set parameter on this step, you have to replace parameter by use "=?" in "Execute SQL Task" it the same as step 1

STEP-8

To use parameter for query on OLE DB you have to click on "Parameters" button on the right page of window popup

STEP-9

And now you have to replace parameter on OLE DB for query data from SQL Server as
"=?"

STEP-10
It's perfect your job package will look like this image, click on "Start" for running package

Notice :
Define parameter for a package you have to set it just one time but for use parameter in different type of object, you have to replace in different symbol.

Read More

Import File System to SQL Integration Service Catalogs for SQL Sever Agenda

Before you will set schedule on SQL Server Agent, you have to import file package from SSIS to Integration Service first. OKEY! it's time to go.

STEP-1

Go to  SQL Server Studio 2014 -> Server type : Integration Services -> fill "Server Name or IP" Server

STEP-2

Click on "Store Package" you can create new folder by click right and "New Folder" for collect your project jobs

STEP-3

After you created folder to keep your package, you have to import package from SSIS to folder by click right on folder you have just created then select "Import Package"

STEP-4

Select package as your job path then click on "OK" to finished

Next Step to create job schedule on SQL Server Agent
http://www.mymemoboxs.com/2015/02/create-agenda-on-sql-server-agent-for.html

Notice :

If you changed any thing on your SSIS package, you have to reimport that package again, otherwise after you create job schedule and click on run, you may see many error.

GOOD LUCK 

Read More

SSIS How to stop testing connectivity while opening project

I feel so bore while I opening project and I have to wait for long time because SSIS requested testing connectivity and I have to clicked at "OK" several times if my some of my package lost connection from database.

I tired several times how to stop to do this process but seem like there is tiny person feel same me and today I have to say Congratulation with me ^_^ I have found a solution, OKEY! let's go.

STEP-1
You have to open your project and click on your project name on the right page as image as below

STEP-2
Go to on SSIS menu then click at "Work Offline" then save your project

STEP-3

Close your project and reopen the project again, this time you will not see any alert to interrupt you anymore.

NOTE
but please you want to run some package of your project, you have to remove a click on "Work Offline" again, otherwise you will can not to run any package.

Read More

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

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

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

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!

Read More