gpt4 book ai didi

Add Date from Source A to Source B(将日期从源A添加到源B)

转载 作者:bug小助手 更新时间:2023-10-25 21:26:47 28 4
gpt4 key购买 nike



I have two OLE DB Source for an Excel file with two different sheet.
Sheet A and Sheet B.
Sheet A cell I6 contained the date, and I would like combine the two sources and add a column in Sheet B to set the value as Sheet A's Date value. Is it possible to do that? Any ideas would be helpful, thank you!

对于具有两个不同工作表的Excel文件,我有两个OLE DB源。工作表A和工作表B。工作表A单元格I6包含日期,我想组合这两个源并在工作表B中添加一列,以将该值设置为工作表A的日期值。有可能做到吗?任何想法都会很有帮助,谢谢!


I tried to create the same column in each sources in order to UNION ALL Sheet A and Sheet B, but it did not return the way I wanted.

我试图在每个源中创建相同的列,以便联合所有工作表A和工作表B,但它没有以我想要的方式返回。


Sheet A (consider this is their membership sign up date)

表A(认为这是他们的会员注册日期)














Date
2012-09-01


Sheet B

图B
























Name phone number
Adam 123345678
David 123345678
Mary 123345678


Ideal Result

理想结果




























Name phone number Date
Adam 123345678 2012-09-01
David 123345678 2012-09-01
Mary 123345678 2012-09-01

更多回答

1. Use this method to read the single vslue into a variable youtube.com/watch?v=UYDvldSnuE0 2. Now use a datacopy to copy the file, but add a calculated column that is your variable that you set in step 1. But really SSIS is not the right tool for this.

1.使用此方法将单个vslue读取到变量youtube.com/watch?v=UYDvldSnuE0 2。现在使用数据副本复制文件,但添加一个计算列,这是您在步骤1中设置的变量。但实际上SSIS不是用于此目的的正确工具。

An alternative method would be to have 2 sources in your data flow and the join the data from those sources on an arbitrary value (which you could create using a derived column transformation).

另一种方法是在数据流中有两个源,并根据任意值(可以使用派生列转换创建)连接来自这些源的数据。

优秀答案推荐

According to my testing in SSIS, you can done it by these steps:

根据我在SSIS中的测试,您可以通过以下步骤完成:



  1. OLE DB Source for Sheet A: Configure an OLE DB Source to read data from Sheet A. The key field here is the date.



  2. Excel Source for Sheet B: Configure another Excel Source to read data from Sheet B.



  3. Data Conversion for Sheet A: Because Excel data is usually imported as DT_WSTR, you may need to use a Data Conversion transformation to convert the date from Sheet A into a format that SSIS can handle (DT_DATE).



  4. Recordset Destination for Sheet A: Use a Recordset Destination to store the single row of data from Sheet A (the date) in an SSIS Variable. You can create a new Object variable for this purpose.



  5. Script Component as Transformation for Sheet B: For the Excel Source of Sheet B, add a Script Component acting as a transformation. In this Script Component, you can add a new output column (for the date), and set its value to the date from the variable (that stored the date from Sheet A). You would use the Row. = Variables. syntax in the Input0_ProcessInputRow method.



  6. OLE DB Destination: Finally, you can write the data (now including the date from Sheet A) to your desired destination using an OLE DB Destination.





You have only one date, It is better to store it in a date variable first:

您只有一个日期,最好先将其存储在日期变量中:



  1. Create a new variable, and assign the date: 01.09.2012


Create Variable


2.Add a derived Column Transformation in DFT, and set the new column called 'Date' equal to the variable defined in step1:

2.在DFT中添加一个派生列转换,并将名为‘Date’的新列设置为等于步骤1中定义的变量:


Pipelines


DerivedColumn



  1. Finally Send it to OLEDB destination:


Destionation


更多回答

Thank you very much for your answer, I wonder if you have any sample of the script? As I've never learn C# before.

非常感谢你的回答,我想知道你是否有剧本的样本?因为我以前从来没有学过C#。

Or by any chance there is a way to do it with SQL query in SSIS / SQL Server instead of C#?

或者有没有可能在SSIS/SQL Server中使用SQL查询而不是C#来实现这一点?

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com