Join two files into one?

Question

Got two flat files.   One is a single row and a single column: just contains a date like below.

2009-10-31

The other has multiple rows and columns like so:

123 JOE
456 MARY
989 BLAIN
432 RITA

This is what I want to end up with (and send to my SQL server for inserting).  I don't want to use a SQL server table for staging or any transact SQL to massage the data.   I just want to do this in SSIS.  

2009-10-31 123 JOE
2009-10-31 456 MARY
2009-10-31 989 BLAIN
2009-10-31 432 RITA
 
I tried doing this with a merge and merge join but that doesn't appear to be geared toward what I've got.   Any help is appreciated.

Zach

Answer 1

I'd handle this by using a script task to read the first file and store the date in a variable. Then, in a second flow that reads the second file, I'd use a derived column transform to add the date variable as a column in the flow.

Edit: Or you could vote for this (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=16115) and hope we get a Cross Join option in the next version :)

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 2

Any chance someone can provide a script?   I've got zero on the script capabilities side of the equation...

Answer 3

There's one approach here (http://vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2007/01/10/247.aspx) that involves using a second data flow to do it.

Or you can use a Script Task that leverages the code here: http://msdn.microsoft.com/en-us/library/db5x7c0d.aspx. You just need to modify it to store the string in a variable. Add the variable to the ReadWriteVariables property on the script task, and copy the string value to the variable.

Dts.Variables["MyVariable"].Value = line;

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 4

John has given the right suggestion!

I would like to add few more things on his first approach to complete your requirement.
1. Follow the steps in http://vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2007/01/10/247.aspx
2. In the last step in the link above inside the script taks remove the code "MsgBox(Me.Variables.vdtFileDate.ToString)".
3. Drag a Flat File Source onto the Data Flow next to the Script component.
4. Double-click the Flat File Source to edit it. Click the New button to create a new Flat File Connection Manager and set the File name to the second flat file.
5. Place a derived column transformation and edit it.
6. In grid below provide the Derived column Name as "Date", Derived column as <add as a new column> and in the expression "User::vdtFileDate"
7. Drag a ole db destination and create a new connection manager with the server and database details of the destination table.
8. Map the output of the derived component to the destination and verify the column mappings.

Overall the flow would be like this.
||Flat File Source1|| -> ||Sript Component|| -> ||Flat File Source2|| ->||Derived Column Transformation|| -> ||ole db destination||

Hope this solves your requirement.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 5

Any chance someone can provide a script?   I've got zero on the script capabilities side of the equation...

 
1.Create a variable "datevalue".
2.Take a script task.
   Go to Script Task editor and make datevalue variable as ReadWriteVariables
   Then hit Design Script button and use folloing code: (Inside ScriptMain Class)

    Dim filepath As String
    Dim value As String
	Public Sub Main()
		'
		' Add your code here
        '
        filepath = "<path of the text file with one record>"
        value = System.IO.File.ReadAllText(filepath)
        Dts.Variables("datevalue").Value = value.ToString()
		Dts.TaskResult = Dts.Results.Success
	End Sub

3.Then add a DFT.
   Take Flat file source inside DFT and configure the flat file source for other text file by creating a connection manager.
   Then take a Derived Column component
   Inside Derived column create a new column "DATE" and in the expression write @[User::datevalue]

   Now take the records to other components as per your requirement

---

Nitesh Rai- Please mark the post as answered if it answers your question