SSIS OLE DB Command- run a PL/SQL

PL/SQL code
DECLARE
P_BOOKINGS_SK NUMBER;
BEGIN
P_BOOKINGS_SK := ?;
DELETE FROM F_BOOKINGS WHERE BOOKINGS_SK = P_BOOKINGS_SK;
DELETE FROM B_BOOKINGS_INVOICE WHERE BOOKINGS_SK = P_BOOKINGS_SK;
DELETE FROM B_BOOKINGS_QUOTE_SN WHERE BOOKINGS_SK = P_BOOKINGS_SK;
DELETE FROM F_OIC WHERE BOOKINGS_SK = P_BOOKINGS_SK;
DELETE FROM F_OIC_BOOKINGS_BRIDGE WHERE BOOKINGS_SK = P_BOOKINGS_SK;
DELETE FROM D_BOOKINGS WHERE BOOKINGS_SK = P_BOOKINGS_SK;
END;

step 1
ole db command should have a input

 

 

step 2

under the tab of input and output properties , create external columns ( when you create multiple columns , note the parameter sequence should be same as the external columns sequence )

step 3 column mapping

step 4 type in the pl\sql code under sqlcommand

 



SSIS pass a child package varaible value to the parent package

Introduction

my project has two package , one is child package , the other is parent package , that means in the parent package , the child package will be executed , below is data flow screen shot , in the child package , i created a variable called child_row_count , which will assign row count number

 

next is parent package , which will execute the child package , and send the child_row_count variable's value to me by email , note that there need create another variable called parent in parent package

 

next step is create script in child package , which was used to assign child package variable "child_row_count" to parent package variable "parent"

 

 

1 setup the ReadOnlyVariables and ReadWriteVariables

note that ReadWriteVariables should be parent package variable , but they are not in the list , you have to manually type in without "User:: "

 

 

2 script

 

Dts.Variables(
"parent").Value = Dts.Variables("child_row_count").Value

if you want to pass the parent package variable to child , you also can use this approach



 



 

SSIS: Reading and Writing to Variables in Script Task          

SSIS: Reading and Writing to Variables in Script Task A lot of people still consistently ask me about how to read and write to variables in the SSIS script task. In this post I will demonstrate for you the two ways in which you can go about this, one from native SSIS functionality and the other from code. In SQL Server 2008 both will generally work out equally as well. In previous versions, you may want to stick with the coding piece as sometimes the a€˜automatica€™ integration with the variable dispenser was a little hooky. So for our example, I am going to set up a simple SSIS project. First I create 2 variables: MyName & YourName. I have scoped these at the packages level as it has always been my instinct that unless there is a compelling reason to scope it otherwise. Now that we have our variables set up, we are ready to begin making our project. Since this example is going to be pretty straightforward, we will just drop two script components onto the control flow design surface. The first will be the one using the native way of handling variables in the script component and the second will be using a little bit of custom code to do it programmatically. The setup is simply shown below. Now opening up the script task you can click on the a€| button next to the readonlyvariables and readwritevariables areas. For this sample, I am setting up the MyName variable to be read only and the YourName variable to read-write.   When completed the screen will show our two variables in the boxes as shown below. Now it is a matter of simply making a call, like below, to access your variables. For my example I am simply going to display the values in some message boxes. Reading from the MyName variable and overwriting the YourName variable. Pretty simple. Public Sub Main() Dim MyName As String = Dts.Variables("MyName").Value MsgBox(MyName) Dts.Variables("YourName").Value = "Tom" Dim YourName As String = Dts.Variables("YourName").Value MsgBox(YourName) Dts.TaskResult = ScriptResults.Success End Sub The variables integration with the script tasks has gotten much better in SQL Server 2008 and I have yet to run into any problems like I have previously. Still, there may be some instances that you come across that you would rather programmatically access variables. For this the code below should do the trick. I have two functions: one for reading a variable and the other for writing to a variable. You will notice in both instances that you must lock the variable first before trying to access it. Think of it as SSISa€™s version of row locking. You want to make sure that you are getting the most accurate version of the variable at that point in time. What good would it do you,since things can run in parallel, if another task is updating the variable at the same time you are trying to read it? Public Sub Main() Dim MyName As String = ReadVariable("MyName") MsgBox(MyName) WriteVariable("YourName", "Tom") Dim YourName As String = ReadVariable("YourName") MsgBox(YourName) Dts.TaskResult = ScriptResults.Success End Sub Private Function ReadVariable(ByVal varName As String) As Object Dim rtnValue As Object 'Create a variables collection to hold you object Dim var As Variables Try 'Lock the variable first to make sure that you have exclusive access 'Think of it like a database object lock Dts.VariableDispenser.LockOneForRead(varName, var) 'Now populate your result rtnValue = var(varName).Value Catch ex As Exception Throw ex Finally 'You must make sure that you unlock the variable before exiting routine var.Unlock() End Try Return rtnValue End Function Private Sub WriteVariable(ByVal varName As String, ByVal value As Object) 'Create a variables collection to hold you object Dim var As Variables Try 'Lock the variable first to make sure that you have exclusive access 'Think of it like a database object lock Dts.VariableDispenser.LockOneForWrite(varName, var) 'Now populate your result var(varName).Value = value Catch ex As Exception Throw ex Finally 'You must make sure that you unlock the variable before exiting routine var.Unlock() End Try End Sub Please note on the code above it is also possible to use something like the following for locking and getting the variable into your collection Dts.VariableDispenser.LockForRead(varName) Dts.VariableDispenser.GetVariables(var) But it includes an extra step so it is not the most elegant solution. Additionally, you may notice that in the Finally block. This is not required as Microsoft a€˜promisesa€™ in their documentation that variables are automatically unlocked when the execution of the script task stops. So you can trust that will happen or call it explicitly like me just so that you will sleep better at night. Hopefully, this helps out some of my colleagues out there that may be struggling finding a good outline of this in the documentation.

• 赞赞赞赞
• 转载
• 分享
• 评论
• 复制地址
• 举报
• 编辑

上一篇 |下一篇：SSIS Save output...

个人日记 |公开 |原创： 鬼宿

签名档

编辑签名档

取消

我的热评日志

• SSIS script component call an oracle procedure2012-12-05 02:13
• 去中国的签证要求2012-11-06 02:17
• SSAS 2008 Stored Procedure compatible with VS20102012-08-03 07:02
• SSAS Implement Dimensional Hierarchy 20082012-08-02 01:06

本文最近访客

设置 查看最近位访客↓收起↑

• 评论
• 互动(0)

显示评论签名

全选取消批量删除批量删除

还没有人发表评论 来坐第一个沙发

提示QQ空间郑重承诺：致力于为用户提供绿色、健康的网络空间！坚决杜绝低俗、恶意、反动等不良信息，在此愿每位QQ空间用户共同参与维护！对您的举报信息我们将第一时间核查处理，请点击：了解QQ空间举报入口

发表评论

系统正在进行升级维护中,暂不支持日志评论,敬请谅解!

表情 |更多功能高级评论编辑器暂时无法加载

您可以在这里发表评论

马上开通空间，体验权限日志、记事本等全新日志体验！

分享此文章

匿名评论(隐身草)

分享

• 分享到空间
• 转播到微博

编辑

• 编辑
• 删除

  ---

• 使用该信纸

  ---

• 字体：大中小

  ---

• 权限设置
• 修改分类
• 推荐日志
• 设置置顶
• 转为私密日志
• 举报
• 申述

日志地址:

请用Ctrl+C复制后贴给好友。

由于IE浏览器升级禁用了alt+x快捷键，请用alt+q快捷键来快速进入写说说入口

正在加载中...

SSIS-VB.net connect to Oracle

 

SSIS-VB.net connect to Oracle          

• 赞赞赞赞

 

Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim dr As OleDbDataReader myConnection = New OleDbConnection("Provider=MSDAORA.1;UserID=xxxx;password=xxxx; database=xxxx") 'MSDORA is the provider when working with Oracle Try myConnection.Open() 'opening the connection myCommand = New OleDbCommand("Select * from emp", myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader MessageBox.Show("EmpNo" & dr(0)) MessageBox.Show("EName" & dr(1)) MessageBox.Show("Job" & dr(2)) MessageBox.Show("Mgr" & dr(3)) MessageBox.Show("HireDate" & dr(4)) 'displaying data from the table End While dr.Close() myConnection.Close() Catch ee As Exception End Try

 

SSIS Save output file as the dynamic name like file .txt

SSIS Save output file as the dynamic name like file <yyyymmdd>.txt          

 

Introduction

One of my project team members came up with a requirement, wherein she was developing a SSIS package to generate text file as output. The source is a Microsoft-SQL Server 2005 database. She was using a T-SQL Query to read data from the data source. The text file should be saved in file system with dynamic name. For example, File<yyyymmdd>.txt.

Solution

The Package consists of DataFlow tasks to generate, Header, Body and Footer for the flat file (since the requirement demands the flat file to have 3 sections: header, body/details, footer with different set of data from the database) and a File System Task to set the name of the file dynamically.

Define a variable with the package scope. To create a variable, right click on the Control Flow workspace and click on Variable. It will open the variables tab and from there, we can create variables.
Go to the properties of the variable and click on the button beside the Expression property.

The Expression Builder dialog box opens up. Here we need to build the expression for the dynamic file name.

In the Expression Text Box, put in either of the following expressions:

Collapse | Copy Code

"C:\\FlatFile\\File" + SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 6, 2 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 ) + ".txt" 

OR

Collapse | Copy Code

"C:\\FlatFile\\File" + SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 6, 2 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 ) + ".txt" 

There is a button in the Expression Builder dialog box called “Evaluate Expression”. Click on the button to check the file name and to validate whether the expression is correct or not. And then, click on OK. Refer to the following screenshots:

 

 

Now finally, use the variable in the File System Task (Rename File). Double click on the File System Task in the Control Flow to open up the File System Task Editor and in the Destination Connection section, set IsDestinationPathVariable to True and select the defined variable corresponding to the DestinationVariable property. Refer to the following screenshot:

 

由于IE浏览器升级禁用了alt+x快捷键，请用alt+q快捷键来快速进入写说说入口

正在加载中...

转发评论

Hadoop Leaning Note

This installation and configuration are under winXP OS

prapare three software package

1、cygwin(http://cygwin.com/setup.exe)
2、hadoop (http://mirror.bjtu.edu.cn/apache/hadoop/common/hadoop-0.20.2/hadoop-0.20.2.tar.gz)
3、jdk （ above version 6）

cygwin installed under D:\ directory

Exctract hadoop unser D:\cygwin
install jdk under C:\

and then do configaration , and below commad in.bashrc
export JAVA_HOME==/cygdrive/c/Java/jdk1.7.0_03
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar

addtionally , under hadoop/conf  we need modify conf/hadoop-env.sh
configure JAVA_HOME
export JAVA_HOME=/cygdrive/c/Java/jdk1.7.0_03

configuration is done

 

$ bin/hadoop Usage: hadoop [--config confdir] COMMAND where COMMAND is one of:   namenode -format     format the DFS filesystem   secondarynamenode    run the DFS secondary namenode   namenode             run the DFS namenode   datanode             run a DFS datanode   dfsadmin             run a DFS admin client   mradmin              run a Map-Reduce admin client   fsck                 run a DFS filesystem checking utility   fs                   run a generic filesystem user client   balancer             run a cluster balancing utility   jobtracker           run the MapReduce job Tracker node   pipes                run a Pipes job   tasktracker          run a MapReduce task Tracker node   job                  manipulate MapReduce jobs   queue                get information regarding JobQueues   version              print the version   jar <jar>            run a jar file   distcp <srcurl> <desturl> copy file or directories recursively   archive -archiveName NAME <src>* <dest> create a hadoop archive   daemonlog            get/set the log level for each daemon or   CLASSNAME            run the class named CLASSNAME Most commands print help when invoked w/o parameters.

next we may run a program wordcount

1、create an input folder(program will automatically create output) 2、put some test file into input forlder 3、$ bin/hadoop  jar hadoop-0.20.2-examples.jar wordcount input output 12/03/05 04:05:43 INFO jvm.JvmMetrics: Initializing JVM Metrics with processName=JobTracker, sessionId= 12/03/05 04:05:43 INFO input.FileInputFormat: Total input paths to process : 1 12/03/05 04:05:44 INFO mapred.JobClient: Running job: job_local_0001 12/03/05 04:05:44 INFO input.FileInputFormat: Total input paths to process : 1 12/03/05 04:05:44 INFO mapred.MapTask: io.sort.mb = 100 12/03/05 04:05:44 INFO mapred.MapTask: data buffer = 79691776/99614720 12/03/05 04:05:44 INFO mapred.MapTask: record buffer = 262144/327680 12/03/05 04:05:44 INFO mapred.MapTask: Starting flush of map output 12/03/05 04:05:44 WARN mapred.LocalJobRunner: job_local_0001 java.io.IOException: Expecting a line not the end of stream         at org.apache.hadoop.fs.DF.parseExecResult(DF.java:109)         at org.apache.hadoop.util.Shell.runCommand(Shell.java:179)         at org.apache.hadoop.util.Shell.run(Shell.java:134)         at org.apache.hadoop.fs.DF.getAvailable(DF.java:73)         at org.apache.hadoop.fs.LocalDirAllocator$AllocatorPerContext.getLocalPathForWrite(LocalDirAllocator.java:329)         at org.apache.hadoop.fs.LocalDirAllocator.getLocalPathForWrite(LocalDirAllocator.java:124)         at org.apache.hadoop.mapred.MapOutputFile.getSpillFileForWrite(MapOutputFile.java:107)         at org.apache.hadoop.mapred.MapTask$MapOutputBuffer.sortAndSpill(MapTask.java:1221)         at org.apache.hadoop.mapred.MapTask$MapOutputBuffer.flush(MapTask.java:1129)         at org.apache.hadoop.mapred.MapTask$NewOutputCollector.close(MapTask.java:549)         at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:623)         at org.apache.hadoop.mapred.MapTask.run(MapTask.java:305)         at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:177) 12/03/05 04:05:45 INFO mapred.JobClient:  map 0% reduce 0% 12/03/05 04:05:45 INFO mapred.JobClient: Job complete: job_local_0001 12/03/05 04:05:45 INFO mapred.JobClient: Counters: 0 above problem can be solved by configuring LANG export LANG=en.utf8 $ bin/hadoop  jar hadoop-0.20.2-examples.jar wordcount input output 12/03/05 04:07:18 INFO jvm.JvmMetrics: Initializing JVM Metrics with processName=JobTracker, sessionId= 12/03/05 04:07:18 INFO input.FileInputFormat: Total input paths to process : 1 12/03/05 04:07:19 INFO mapred.JobClient: Running job: job_local_0001 12/03/05 04:07:19 INFO input.FileInputFormat: Total input paths to process : 1 12/03/05 04:07:19 INFO mapred.MapTask: io.sort.mb = 100 12/03/05 04:07:19 INFO mapred.MapTask: data buffer = 79691776/99614720 12/03/05 04:07:19 INFO mapred.MapTask: record buffer = 262144/327680 12/03/05 04:07:19 INFO mapred.MapTask: Starting flush of map output 12/03/05 04:07:19 INFO mapred.MapTask: Finished spill 0 12/03/05 04:07:19 INFO mapred.TaskRunner: Task:attempt_local_0001_m_000000_0 is done. And is in the process of commiting 12/03/05 04:07:19 INFO mapred.LocalJobRunner: 12/03/05 04:07:19 INFO mapred.TaskRunner: Task 'attempt_local_0001_m_000000_0' done. 12/03/05 04:07:19 INFO mapred.LocalJobRunner: 12/03/05 04:07:19 INFO mapred.Merger: Merging 1 sorted segments 12/03/05 04:07:19 INFO mapred.Merger: Down to the last merge-pass, with 1 segments left of total size: 5204 bytes 12/03/05 04:07:19 INFO mapred.LocalJobRunner: 12/03/05 04:07:19 INFO mapred.TaskRunner: Task:attempt_local_0001_r_000000_0 is done. And is in the process of commiting 12/03/05 04:07:19 INFO mapred.LocalJobRunner: 12/03/05 04:07:19 INFO mapred.TaskRunner: Task attempt_local_0001_r_000000_0 is allowed to commit now 12/03/05 04:07:19 INFO output.FileOutputCommitter: Saved output of task 'attempt_local_0001_r_000000_0' to output 12/03/05 04:07:19 INFO mapred.LocalJobRunner: reduce > reduce 12/03/05 04:07:19 INFO mapred.TaskRunner: Task 'attempt_local_0001_r_000000_0' done. 12/03/05 04:07:20 INFO mapred.JobClient:  map 100% reduce 100% 12/03/05 04:07:20 INFO mapred.JobClient: Job complete: job_local_0001 12/03/05 04:07:20 INFO mapred.JobClient: Counters: 12 12/03/05 04:07:20 INFO mapred.JobClient:   FileSystemCounters 12/03/05 04:07:20 INFO mapred.JobClient:     FILE_BYTES_READ=325874 12/03/05 04:07:20 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=356160 12/03/05 04:07:20 INFO mapred.JobClient:   Map-Reduce Framework 12/03/05 04:07:20 INFO mapred.JobClient:     Reduce input groups=383 12/03/05 04:07:20 INFO mapred.JobClient:     Combine output records=383 12/03/05 04:07:20 INFO mapred.JobClient:     Map input records=75 12/03/05 04:07:20 INFO mapred.JobClient:     Reduce shuffle bytes=0 12/03/05 04:07:20 INFO mapred.JobClient:     Reduce output records=383 12/03/05 04:07:20 INFO mapred.JobClient:     Spilled Records=766 12/03/05 04:07:20 INFO mapred.JobClient:     Map output bytes=6912 12/03/05 04:07:20 INFO mapred.JobClient:     Combine input records=663 12/03/05 04:07:20 INFO mapred.JobClient:     Map output records=663 12/03/05 04:07:20 INFO mapred.JobClient:     Reduce input records=383

OK ! 。
look at the result

$ cat part-r-00000
"Glory  1
"Grandiose      1
"I      1
"Putin  1
"Putinism",     1
"These  1
"We     4
"every  1
"the    1
"unfair 1
"would  1
'victory'       2
(14:00  1
-       1
--------------------------------------------------------------------------------        1
17%.    1
18:00   1
2008    1
58.3%   1
6,000   1
60%     2
62.3%.  1
64%,    1
Alexey  1
Analysis        1
BBC     1
BBC:    1
Bridget 1
But     2
Continue        2
December's      1
December,       1
Diplomatic      1
Dmitry  1
ElectionRussia  1

the problem of cognos configuration for oracle database connection

1. 20:04:41, 'LogService', 'StartService', 'Success'.
2. 20:04:47, 'ContentManager', 'getActiveContentManager', 'Failure'.
DPR-CMI-4006 Unable to determine the active Content Manager. Will retry periodically.
3. 20:04:47, 'com.cognos.pogo.contentmanager.coordinator.ActiveCMControl', 'pogo', 'Failure'.
DPR-DPR-1035 Dispatcher detected an error.

4. 20:04:46, CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store.
CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store.
CM-CFG-5137 Content Manager was unable to complete the initialization of the content store. For more information, review the log file. Before you restart Content Manager, you may need to recreate the content store database or clean it using dbClean_*.sql.
5. 20:05:10, 'ContentManagerService', 'StopService', 'Success'.
6. 20:05:10, 'ContentManagerService', 'StopService', 'Success'.
7. 20:05:10, 'CPS Producer Registration Service', 'StopService', 'Success'.
8. 20:05:10, 'CPS Producer Registration Service', 'StopService', 'Success'.
9. 20:05:10, 'MonitorService', 'StopService', 'Success'.
10. 20:05:10, 'MonitorService', 'StopService', 'Success'.
11. 20:05:10, 'DeliveryService', 'StopService', 'Success'.
12. 20:05:10, 'DeliveryService', 'StopService', 'Success'.
13. 20:05:11, 'EventService', 'StopService', 'Success'.
14. 20:05:11, 'EventService', 'StopService', 'Success'.
15. 20:05:11, 'JobService', 'StopService', 'Success'.
16. 20:05:11, 'JobService', 'StopService', 'Success'.
17. 20:05:11, 'com.cognos.pogo.services.DefaultHandlerService', 'pogo', 'Failure'.
DPR-DPR-1035 Dispatcher detected an error.

18. 20:05:11, 'com.cognos.pogo.services.DefaultHandlerService', 'pogo', 'Failure'.
DPR-DPR-1035 Dispatcher detected an error.

19. 20:05:11, 'SystemService', 'StopService', 'Success'.
20. 20:05:11, 'SystemService', 'StopService', 'Success'.
21. 20:05:11, 'MetricsManagerService', 'StopService', 'Success'.
22. 20:05:11, 'MetricsManagerService', 'StopService', 'Success'.
23. 20:05:11, 'BatchReportService', 'StopService', 'Success'.
24. 20:05:11, 'BatchReportService', 'StopService', 'Success'.
25. 20:05:11, 'DataIntegrationService', 'StopService', 'Success'.
26. 20:05:11, 'DataIntegrationService', 'StopService', 'Success'.
27. 20:05:11, 'ReportService', 'StopService', 'Success'.
28. 20:05:11, 'ReportService', 'StopService', 'Success'.
29. 20:05:11, 'LogService', 'StopService', 'Success'.
30. 20:05:11, 'LogService', 'StopService', 'Success'.
31. [ ERROR ] CFG-ERR-0103 Unable to start Cognos 8 service.
Execution of the external process returns an error code value of '-1'.

how to solve this problem

1 alter oracle database character to utf8

2 create a new user and grant the user resource, connect, and dba privilage

3 restart database

4 delete the privious configuration in content manager, and setup a new one

5 happy, it can work