I needed to figure out how to get SSIS to consume a file that had lines of data with attributes of both "apples" and "oranges". No matter where I looked online, it was as if SSIS could not take in a file that had different types of data on each line and then throw that data into a database. The situation looked bleak, until I found a little item in my toolbox inside a data flow called "Conditional Split".
Conditional Split is almost like a little tool that makes your flat file like XML in that you can send data from the source to many tables. When you consume an XML file in SSIS, you could have many tables that could flow from that source. The same is true when you use Conditional Split; you tell SSIS when a line is a different row type and BAMM, you are on your way to multiple tables of data getting inserted into your database.
For me, the trick to all of this wasn't in how I accomplished this, but rather how to know what types of tools you need to use in SSIS to do what you need to do.
I have my Data Flow task.
Inside that I have my flat file Source.
That component points to a "Conditional Split" component.
Inside of that component I simply created multiple conditions that meant that if a line started like X, then output that line to a separate flow into a table. In my case, I said substring(Field_name,1,5)=="Batch" and called that Output Name "Batch_Details" table. I created other lines to, like substring(Field_name,1,3)=="Act" with an Output name of "Account Details". You can create as many types of output lines as you might need, although I don't know what the technical limit is, I only needed 10.
That component then pointed to a derived columns component. In my case, I had a fixed length file so I created a bunch of columns that were created by substring commands.
That component pointed to a OLE DB Destination.
That was it. It turned out to be pretty simple; if I could only figure out how SSIS could perform the actions. I remain disappointed that there weren't many posts, FAQ's or much of anything out on the net to help reveal how to perform these actions.
Keith
Recently I posted a script that allows you to download a CSV file to SSIS through HTTP Authentication, today, I'm posting a slightly different script that uses some XML references in order to download an XML file. You can probably use the other way that I wrote for the CSV file, but since I had both, I thought I'd blog them here for posterity sake.
- Create a script task on the control flow tab.
- Populate your URL in a prior step to a variable. I used a SQL Task to format a complicated file name into the URL.
- Make sure your script task passes the URL in the ReadOnlyVariables to the script.
- Use this script below. Correct the place for your UNC path for your output file, user id, password.
- Consume your data in a data flow task.
I hope this helps people; it took me a while to figure it out.
Keith
I used this VB.NET Script below:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Collections.Generic
Imports System.Text
Imports System.Xml
Public Class ScriptMain
Public Sub Main()
Try
Dim restURL As New StringBuilder()
Dim restRequest As HttpWebRequest
Dim restResponse As HttpWebResponse
Dim xDoc As New XmlDocument()
restURL.AppendFormat(Dts.Variables("URL").Value.ToString())
restRequest = DirectCast(WebRequest.Create(restURL.ToString()), HttpWebRequest)
' the key line. This adds the base64-encoded authentication information to the request header
restRequest.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("USER_ID_GOES_HERE:PASSWORD_GOES_HERE")))
restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)
xDoc.Load(restResponse.GetResponseStream())
' If you wanted to, you could change this next line to interact with a variable, just be sure to pass it to the script.
xDoc.Save("UNC_PATH_AND_FILE_NAME_GO_HERE")
xDoc.Save(Console.Out)
Dts.TaskResult = Dts.Results.Success
Catch webEx As WebException
Dim [error] As New StringBuilder()
'catch protocol errors
If webEx.Status = WebExceptionStatus.ProtocolError Then
[error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)
[error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)
' post the error message we got back. This is the old error catch code that might work better with SSIS.
Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End If
End Try
End Sub
End Class
I recently needed to perform a download of a CSV file with HTTP Authentication in SSIS to a CSV file. Its possible, but I needed a script to do it. I couldn't use an HTTP connection because nothing in SSIS would consume it. Nothing, including the File System, WMI and execute process tasks would work in the control flow and none of the data flow sources would consume the file. I was forced to use a script since the company I was downloading from had no FTP and no web service for this file.
- Create a script task on the control flow tab.
- Populate your URL in a prior step to a variable. I used a SQL Task to format a complicated file name into the URL.
- Make sure your script task passes the URL in the ReadOnlyVariables to the script.
- Use this script below. Correct the place for your UNC path for your output file, user id, password.
- Consume your data in a data flow task.
I hope this helps people; it took me a while to figure it out.
Keith
p.s. Thanks to my co-worker Kirk Palmer for assistance with the VB.NET script.
I used this VB.NET Script below:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Collections.Generic
Imports System.Text
Public Class ScriptMain
Public Sub Main()
Try
Dim restURL As New StringBuilder()
Dim restRequest As HttpWebRequest
Dim restResponse As HttpWebResponse
Dim tDoc As StreamReader
' modify the date, report name, merchant ID, and processing environment (production/test) as needed
restURL.AppendFormat(Dts.Variables("URL").Value.ToString())
restRequest = DirectCast(WebRequest.Create(restURL.ToString()), HttpWebRequest)
' the key line. This adds the base64-encoded authentication information to the request header
restRequest.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("USER ID:PASSWORD")))
restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)
tDoc = New StreamReader(restResponse.GetResponseStream())
Dim f As File
f.WriteAllText("UNC PATH FOR OUTPUT FILE", tDoc.ReadToEnd)
Dts.TaskResult = Dts.Results.Success
Catch webEx As WebException
Dim [error] As New StringBuilder()
'catch protocol errors
If webEx.Status = WebExceptionStatus.ProtocolError Then
[error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)
[error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)
' post the error message we got back. This is the old error catch code that might work better with SSIS.
Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End If
End Try
End Sub
End Class