Well... On Wednesday, at 3:30AM EDT, the Large Hadron Collider will be powered up.
I guess we will know shortly if it will unleash many tiny black holes that will consume the Earth.
Or better yet, a time machine :)
http://www.foxnews.com/story/0,2933,419404,00.html
peace
Look out for black holes...
Tuesday, September 9, 2008
Friday, September 5, 2008
Determining if a File Exists in SSIS Using Wildcards
In some instances I have had to deal with files that did not have a set name. In these cases, I have a handy script file to determine if the files are there.
(Another way to handle this is to use the ForEach task and use a wildcard in there, but I am going to blog about that next week)
First, I add a variable to my package. I usually call this variable FileExists and set it as a Boolean, with a default value of False.
I then add a script task to my control flow. In the Read/Write variable property, I add User::FileExists.
In the script editor, I use this script:
This searches c:\ for a file *.txt. The Length property of FileInfo is the number of files found. If this is greater than zero, I set the FileExists variable to True.
After closing the script editor, and clicking OK on the script task property window, I am ready to set up my precedence constraint.
I then add my next task to the control flow and drag the flow control from the script task to the new task. I right click on the constraint and select edit. I select Expression in the Evaluation Operation drop down box. For the expression I use: @[User::FileExists]==true. This way, the only way the next task is executed is if files exist. If the don’t exists, the package ends gracefully.
You could add another task to log the fact that there were no files, connect the script to that task, and set the expression to: @[User::FileExists]==false.
peace
(Another way to handle this is to use the ForEach task and use a wildcard in there, but I am going to blog about that next week)
First, I add a variable to my package. I usually call this variable FileExists and set it as a Boolean, with a default value of False.
I then add a script task to my control flow. In the Read/Write variable property, I add User::FileExists.
In the script editor, I use this script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim di As DirectoryInfo = New DirectoryInfo("c:\")
Dim fi As FileInfo() = di.GetFiles("*.txt")
If fi.Length > 0 Then
Dts.Variables("User::FileExists").Value = True
Else
Dts.Variables("User::FileExists").Value = False
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
This searches c:\ for a file *.txt. The Length property of FileInfo is the number of files found. If this is greater than zero, I set the FileExists variable to True.
After closing the script editor, and clicking OK on the script task property window, I am ready to set up my precedence constraint.
I then add my next task to the control flow and drag the flow control from the script task to the new task. I right click on the constraint and select edit. I select Expression in the Evaluation Operation drop down box. For the expression I use: @[User::FileExists]==true. This way, the only way the next task is executed is if files exist. If the don’t exists, the package ends gracefully.
You could add another task to log the fact that there were no files, connect the script to that task, and set the expression to: @[User::FileExists]==false.
peace
Tuesday, September 2, 2008
How to Use the SSIS Script Component as a Data Source
Recently I had the pleasure of working with a data source that returned all of the transactions with 1 stored procedure. However, this meant the stored procedure returned 14 result sets, and an output parameter.
The question was how to get the 15 result sets into SSIS. The answer: Use a Script Component.
Here is the path I took to handle this:
Making the Connection
All connections in SSIS are handled thru connection managers. We will have to have a connection manager to connect to the source database to execute the stored procedure.
In the Connection Manager tab of SSIS, right click and select New ADO.NET Connection…
Setup your connection properties, click on Test Connection. Once you have a good connection, you are ready for the next step.
Setup a Script Component
I added a script component to my data flow task. When asked if it was a Source, Transformation or a Destination, I selected Source.
I then added a total of 15 outputs. (14 for the result sets, 1 for the output parameter) To do this, I clicked on the Inputs and Outputs tab, and clicked on the Add Output button until I have 15 outputs.
Then came the fun part: adding, naming and typing all of the columns for all of the outputs. On the same Inputs and Outputs tab, I selected the first output, renamed it to the result set name. Then I opened up the output in the tree view, and expanded the Output Columns folder. I clicked on the Add Column button until I had as many columns as the first result set.
Once the columns where in the tree view, I selected the first one, changed the name, set the data type and size, and moved onto the next column, until they were complete.
Then I did the same for each output in the component.
The final step here is to configure the script component to use your newly created connection manager. To do this, click on the Connection tab and add a new connection. Set the name, and then in the middle column, choose your connection manager.
Scripting the Outputs
The next step is to tie together the stored procedure and the script component outputs. To do this, click on the script tab and click the Design Script button to open the scripting window.
I added 2 subroutines to handle opening the connection and executing the stored procedure:
The AcquireConnections subroutine is called by SSIS when it is ready to open the database connections. I override it to make sure the database connection is ready to use.
Likewise, the PreExecute is called when it’s time to get the data. (This should clear up some of the long running PreExecute issues out there.) I open our SQL Reader here and execute the source stored procedure.
Now comes the fun part. Linking the result sets to the output columns.
This is done in the CreateNewOutputRows subroutine:
This code goes thru each result set in the SQL Reader and assigns the value of the result set to the output column. I did not show all of the columns or all of the outputs since it’s the same concept for each.
Once that is done, I clean up after myself:
This closes the SQL Reader and releases the connection to the database.
Once this is done, close the script window and click on OK on the script component properties.
The script component will now have multiple outputs that you can select from when linking it to another data flow component.
Conclusion
I hope this will help you when you need to return multiple result sets from stored procedures into SSIS. If you are familiar with VB.NET coding, you should pick this up easily, and even if not, the example at least gives you the basic steps and something to copy from.
peace
The question was how to get the 15 result sets into SSIS. The answer: Use a Script Component.
Here is the path I took to handle this:
Making the Connection
All connections in SSIS are handled thru connection managers. We will have to have a connection manager to connect to the source database to execute the stored procedure.
In the Connection Manager tab of SSIS, right click and select New ADO.NET Connection…
Setup your connection properties, click on Test Connection. Once you have a good connection, you are ready for the next step.
Setup a Script Component
I added a script component to my data flow task. When asked if it was a Source, Transformation or a Destination, I selected Source.
I then added a total of 15 outputs. (14 for the result sets, 1 for the output parameter) To do this, I clicked on the Inputs and Outputs tab, and clicked on the Add Output button until I have 15 outputs.
Then came the fun part: adding, naming and typing all of the columns for all of the outputs. On the same Inputs and Outputs tab, I selected the first output, renamed it to the result set name. Then I opened up the output in the tree view, and expanded the Output Columns folder. I clicked on the Add Column button until I had as many columns as the first result set.
Once the columns where in the tree view, I selected the first one, changed the name, set the data type and size, and moved onto the next column, until they were complete.
Then I did the same for each output in the component.
The final step here is to configure the script component to use your newly created connection manager. To do this, click on the Connection tab and add a new connection. Set the name, and then in the middle column, choose your connection manager.
Scripting the Outputs
The next step is to tie together the stored procedure and the script component outputs. To do this, click on the script tab and click the Design Script button to open the scripting window.
I added 2 subroutines to handle opening the connection and executing the stored procedure:
Public Class ScriptMain
Inherits UserComponent
Private connMgr As IDTSConnectionManager90
Private Conn As SqlConnection
Private Cmd As SqlCommand
Private sqlReader As SqlDataReader
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.Connection ‘This is the connection to your connection manager.
Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
Dim cmd As New SqlCommand("Declare @SessionID int; Exec spgTransactions @SessionID OUTPUT; Select @SessionID", Conn)
sqlReader = cmd.ExecuteReader
End Sub
The AcquireConnections subroutine is called by SSIS when it is ready to open the database connections. I override it to make sure the database connection is ready to use.
Likewise, the PreExecute is called when it’s time to get the data. (This should clear up some of the long running PreExecute issues out there.) I open our SQL Reader here and execute the source stored procedure.
Now comes the fun part. Linking the result sets to the output columns.
This is done in the CreateNewOutputRows subroutine:
Public Overrides Sub CreateNewOutputRows()
'Invoice Header
Do While sqlReader.Read
With Me.InvoiceHeaderBuffer
.AddRow()
.InvoiceNumber = sqlReader.GetInt32(0)
.InvoiceDate = sqlReader.GetDate(1)
'etc, etc, etc for all columns.
End With
Loop
sqlReader.NextResult()
'Invoice Detail
Do While sqlReader.Read
With Me.InvoiceDetailBuffer
.AddRow()
...
'more outputs and more columns
' until we get to the last result set which will be the output parameter (SessionID)
sqlReader.NextResult()
'Session ID
'We know this result set has only 1 row
sqlReader.Read
With Me.SessionIDBuffer
.AddRow()
.SessionID = sqlReader.GetInt32(0)
End With
sqlReader.Read 'Clear the read queue
End Sub
This code goes thru each result set in the SQL Reader and assigns the value of the result set to the output column. I did not show all of the columns or all of the outputs since it’s the same concept for each.
Once that is done, I clean up after myself:
Public Overrides Sub PostExecute()
sqlReader.Close()
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(Conn)
End Sub
This closes the SQL Reader and releases the connection to the database.
Once this is done, close the script window and click on OK on the script component properties.
The script component will now have multiple outputs that you can select from when linking it to another data flow component.
Conclusion
I hope this will help you when you need to return multiple result sets from stored procedures into SSIS. If you are familiar with VB.NET coding, you should pick this up easily, and even if not, the example at least gives you the basic steps and something to copy from.
peace
Subscribe to:
Posts (Atom)