SQL Lion

In my previous posts << SSIS vs Text File Importing –I >> and << SSIS vs Text File Importing –II >> , we discussed about the Row Delimiter issue of SSIS while dealing with text files and the workaround for the same. In this post I will show you how to achieve the same through SSIS package.

Step1:  Open BIDS (Business Intelligence Development Studio), and create a solution for Integration Services.

Step2: Drag a foreach Loop Container task from the toolbox, to the package and rename it to some meaningful words.

Step3: Double click the Foreach Loop container, and Click on “Collection” at the left pane.

Step4: Select “Foreach File Enumerator” from the Enumerator list and configure the values as shown below.

Step 5: Click on the “Variable Mappings” and create a string variable to store intermediate text file path for each iteration of the loop. Assign the default value to one of the text files to avoid any validation error.

Step 6: Click OK and drag one “script task” inside the “foreach loop container”.

Step 7: Double click the “Script Task” and select “Microsoft Visual Basic 2008” under “Script Language” text box.

Step 8: Click on the ellipse button beside “ReadOnlyVariables” and check the variable named “User::TextFilePath”.

Step 9: Click on the “Edit Script …” button and paste the below code inside the Main function.

Script Task Code in VB .Net Expand

Public Sub Main()
         Dim obj
         Dim objwkbookxls
         obj = CreateObject("Excel.Application")
         obj.Application.DisplayAlerts = False
         objwkbookxls = obj.Application.Workbooks.Open(Dts.Variables("User::TextFilePath").Value.ToString)
         objwkbookxls.Save()
         objwkbookxls.Close()
         obj.Quit()
Dts.TaskResult = ScriptResults.Success
End Sub

The above code is written in VB which will open the text file in Excel Application and will save it. This in turn reformats the missing tabs in the text file.

Step 10: Save the file and close it. Press OK.

Step 11: Drag a data flow task inside the “Foreach Loop Container” and double click it.

Step 12: Create source and destination connection manager for data transformation.

Step 13: Drag Flat file source for Text file and OLEDB destination for SQL table and configure it for a transformation to occur.

Step 13: Right click on the text file source connection manager, and click on “Properties”. On the “Properties” pane click on the ellipse button beside “Expressions”.

Step 14: On the “Property Expressions Editor”, select “Connection String” under the “Property” drop down box and click on ellipse button under “Expression”.

Step 15: On the “Expression Builder” dialog box, drag the variable “User::TextFilePath” inside “Expression:” text box and click on “Evaluate Expression” button. Click OK.

Step 16: The overall package should look like below:

Now run the package. After it got completed successfully, check the data in the destination table.

Use the below query:

 
SELECT [CarCompany]
      ,[CarBodyType]
      ,[CarName]
      ,[EngineType]
      ,[EMI (INR)]
      ,[LoanType]
      ,[Interest Rate]
  FROM [dbo].[Car_Info]

Output:

We got exactly 9 rows and the data is properly aligned.

Hence the problem got resolved by adding a simple script task code that reformats the text file before loading it into database.

Note: Download the Full SSIS solution from here for FREE.