Category Archives: SSIS

Visual Studio 2012 / SQL Server 2012 create SSIS – 在Visual Studio 2012 或 SQL Server 2012 如何建立SSIS Package?

今天嘗試在新安裝的電腦上建立一個SQL Server Integration Service的 專案..來做一個Import CSV的工作..[主要是用來寫一個功能..可以令到將來可以執行來Import和更新資料]

SQL Server 2008的時候..
我們可以開啟 Microsoft SQL Server 2008 中的 “SQL Server Business Intelligence Development Studio” 來建立SSISPackage
SQL Server Business Intelligence Development Studio - Visual Studio 2008
誰不知..在我的Windows 8 + Visual Studio 2012SQL Server 2012中卻找不到
SQL Server Project in Visual Studio 2012

Visual Studio 2012 中只有”SQL Server Database Project

試了一段時間後終於找到解決方法了

解決方法:
原來在SQL Server 2012中的 “SQL Server Business Intelligence Development Studio” 程式改了名稱為 “SQL Server Data Tools
SQL Server Data Tools in Windows 8

開啟了 “SQL Server Data Tools“後便看到了 似曾相識的 Project Template
Business Intelligence” -> “Integration Services
SSIS In Visual Studio 2010
Integration Services Project

終於可以開始開發我的SSIS Package

Happy Coding

SSIS Script Task language From C# to VB — 在SSIS 中改變Script Task 使用的 程式語言

今日公司有個朋友問…
為什麼你的SSIS Script Task 元件只可以用C# 來寫程式碼的?
我記得好像有地方可以修改的…
最後在自己的電腦上嘗試了建立一個SSIS Script TaskPackage
原來要改變用VB C# 來寫這個Script Task 是十分簡單的
只要在Script Langange 選項上更變便可
但是一旦選擇了便沒有很再改變了
Hope you find it useful

Run SSIS Services From Client Machine How to use SSIS WCF Services Part I—在客戶端中使用SSIS Services 怎樣使用SSIS WCF Services Part I

今日想在這裡和大家分享怎樣發佈 這個SSIS WCF Services
大家可以到CodePlex Download 這個Zip 檔案

http://ssisservices.codeplex.com/releases
Download 完成後我們可以把檔案解壓縮到一個地方儲存..
以備一會兒使用

以下這個例子是用Windows 7中的IIS 7.5 來做的
使用這個SSIS WCF Services 的 系統一定要已安裝 SSIS Services
因為我們需要通過這部電腦的 來執行SSIS Package

首先我們要在設定好IIS…
1) 我們可以 以”Start” -> “Control Panel” ->”Administrative Tools” ->”Internet Information Services (IIS) Manager”  我的這個例子是使用Windows 7 中的 IIS 7.5

2) 在” Default Web Site” 中 按右鍵 -> 選擇 ->”Add Application…/加入應用程式

3) 在”Add Application” 視窗 填入適當的資料 , “Physical Path…” 中選擇之前 “SSISServices.zip” Download 完成後解壓縮到的地方, 之後按 “OK/確定

4) 之後你便可以看到剛剛建立的”Web Application“, 按一下”Content View” 分頁

5) 選擇 “SSISServices.svc” 檔案後, 用右鍵 按一下 “Browse/瀏覽

6) 之後電腦便會在”瀏覽器“中把開這個SSIS WCF web services

7) 按一下WSDL 連結後便會看到 WSDL 的內容了

之後你便可以把 這個SSIS WCF Services 加進到你的 Project上使用了

Hope you find it useful

SSIS WCF Services on CodePlex — Execute SSIS Package Via Web Services

今日終於在CodePlex 上和大家分享了我第二個Project了
這個Project 是一個WCF Service, 如果執行 SQL Server Integration Services [SSIS] 的…
在大概一年多前才剛剛在朋友身上學會了使用 Data Transformation Services [DTS]
一個在SQL Server 2000 或之前版本的 SQL 功能
令我們可以寫一些DTS Package 之後在自己寫的.Net Application 上執行..
最好的地方是可以在客戶端執行..
又不需要客戶端的電腦上安全 SQL Server 2000 有關DTS 的元件
當然要在你的 Project 上的安裝包上做一些動作
令到 用戶在安裝程式時 把適當的 DLL 複製到客戶的系統中…
而我對DTS 的認識不多…
所以當公司升級了SQL ServerSQL Server 2008 之後我便開始使用 SSIS
誰不知…當我把 之前寫的DTS Package 轉成了SSIS
當我在客戶的電腦了執行這功能時便出現錯誤信息…
嘗試了很多方法也解決不了…
到最後有朋友說要在客戶的電腦上安裝 SSIS Service 才可以執行SSIS Package
又說不可以在客戶的電腦上安裝SSIS Service 因為版權問題 …ETC
Please correct me if my concept is wrong
所以最後便想到用WCF 的做法…
做一個服務端來解決 SSIS 的問題了..
如果大家遇到差不多的問題 可以嘗試用這個SSIS WCF Services 看看能不能解決你的問題
或者如果你有更好的解決方案..希望你能夠和我們分享
SSIS Service
ShareChiWaiLib
當我有空的時候會在程式碼中加入註解
和寫多一些文章關於怎樣使用這個 SSISServicesShareChiWaiLib
希望大家可以給我一點Feedback 令到這個2個Projects 更完美
Happy coding =)

The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304

When I try to use SSIS to import data from Excel to Database, it has failed and return the error message below.

The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304

SSIS package “ShareChiWai.dtsx” starting.
Information: 0x4004300A at Import So from Excel, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC020801C at Import So from Excel, Standing Order Excel File [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Import So from Excel, SSIS.Pipeline: component “ShareChiWai Excel File” (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Import So from Excel, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import So from Excel: There were errors during task validation.
SSIS package “ShareChiWai.dtsx” finished: Success.
The program ‘[4972] ShareChiWai.dtsx: DTS’ has exited with code 0 (0x0).

I have done some research related with the error code 0xC00F9304. It said:
SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Fortunately, I have worked out how to solve this issue: [because I have similar issue before with Visual Studio after upgrading the operating system to 64bit…]

Here it is my solution:
Right click on your SSIS project in your “Solution Explorer
Then Select “Properties
On the “Property Pages” click on “Debugging
and Under “Debug Options” ->”Run64ButRuntime
Switch it from “True” to “False
so that the project will not start 64 bit SSIS runtime, even 64 bit SSIS runtime is installed. [This mean it will use the 32 bit runtime]

Hope you find it useful

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft OLE DB Provider for SQL Server” Hresult: 0x80004005 Description: “[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.”

When I open the SSIS project from I can see the following error from the “Error List” Panel

‘Error    1    Validation error. Export Data Flow Task1: Package1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft OLE DB Provider for SQL Server”  Hresult: 0x80004005  Description: “[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.”.      Package1.dtsx    0    0    ‘

I am wondering why SQL Server access denied, at the end, I realise the SQL service do not start automatically.

TO solve it I just need to
Start” -> “Control Panel“-> “Administrative Tools“-> “Services
Then select the SQL Server instance that you want to start up the service.
Right click on it and select “Start“.

Then you may want to close down the project and re-open it again.
The error message should disappear.