Calling CLR Hosted Stored Procedure from Reporting Services

In short, you can’t. And this is with SQL Server 2005 SP2.

Problem:

1. I created a CLR hosted assembly stored procedure called HostedSProc using a VS2005 database project.

2. I deployed the project and it created a stored procedure called ‘dbo.HostedSProc’ on the server. Well and good.

3. I tested calling the sproc from SQL Management Studio and it worked fine.

4. I then created a VS2005 Report Project and set the data source of my report to call a stored procedure ‘dbo.HostedSProc’.

5. When I tried to run it, it returned ‘stored procedure ‘dbo.HostedSProc’ could not be found.

Solution:

1. Create a ‘normal’ stored procedure that ‘wraps’ the call to the hosted stored procedure.

2. I created a stored procedure called ‘HostedSProcWrapper’ and in the stored procedure I call the ‘HostedSProc’ procedure. See below.

3. The wrapper should have the same signature as the internally called procedure, so you can assign parameters from report designer.

4. I tried it and it works.

5. What I especially like is that even though you’ve called a ‘wrapper’ stored proc, the Report Designer can still detect the fields returned by the internal stored proc, to be used in the reports.

CREATE PROCEDURE [dbo].[HostedSProcWrapper]

-- Add the parameters (or more) of the stored proc we’ll call here

@docId varchar(100)

AS

BEGIN

SET NOCOUNT ON;

-- Call the stored proc here, passing in the required params.

exec dbo.HostedSProc @docId

END

[+/-] Read More...

[+/-] Summary only...

Script Task Precompiling bahaviour , custom references and side by side execution

If you have a script task that references a custom assembly, you would know by now that the custom assembly needs be in the GAC. However the GAC copy is only used during runtime.

For development, you'll also need to have the custom reference in the Microsoft.Net framework directory, ex C:\Windows\Microsoft.Net\Framework\v2.0.50727\.

Now what if you have two versions of the SSIS package, and two versions of the custom assembly, and these need to be developed and executed side by side. How do we handle this? See below for some notes:

Development
1. Make sure the correct version of the custom assembly you want your SSIS Script Task/Component to work with is in the .net framework directory. What I mean is, if you're working on v1.0 of your SSIS package, and the script tasks are supposed to reference v1.0 of your custom assembly, make sure v1.0 of the custom assembly is in the .net framework directory.

If are working with v1.0 SSIS package, you have v1.1 of the custom assembly in .net framework directory, and you open the script task, it will automatically pick up the framework directory version and if you have turned on precompiling, it will compile against the v1.1, and during runtime, will look for that version in GAC. The same works vice versa.

2. When you're done with v1.0 SSIS package, and start work on v1.1, copy the v1.1 of the custom assembly to the .net framework directory first, before opening your package, and especially before opening your script tasks.

Set a post build event on your custom assembly's project to automatically copy the built assembly to GAC AND copy to your .net framework directory. This ensures you're always developing your SSIS package against the correct custom assembly.
Example post build event:

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /if "$(TargetPath)"
copy /Y "$(TargetPath)" "C:\Windows\Microsoft.Net\Framework\v2.0.50727\$(TargetFileName)"

Runtime
1. If precompiling is turned off, the custom assembly has to be placed in the .net framework directory the SSIS Script task will compile at runtime, against the version in .net framework directory.

2. If you have v1.0 and v1.1 SSIS packages deployed on a machine, and v1.1 of custom assembly is in the .net framework dir, and their script tasks are not precompiled, BOTH SSIS packages will compile against v1.1 of your custom assembly. If your v1.1 custom assembly is not backward compatible with v1.0, your v1.0 SSIS package could break at runtime.

Turning on precompiling eliminates the need to have the custom assembly in the framework directory, and multiple versions of SSIS packages can run simultaneously.

[+/-] Read More...

[+/-] Summary only...

Getting constraint columns from a SQL Command

Sorry i don't have the solution. Does anyone know what is a good and clean way to extract the columns specified in the WHERE clause in a SQL Command?
eg, SQL COmmand :

SELECT * FROM HELLOWORLD WHERE FNAME='JOHN' AND LNAME='DOE'

I want to be able to extract the texts 'FNAME' and 'LNAME'. Working with vb.net.

Ideas?

[+/-] Read More...

[+/-] Summary only...

SSIS Script task Precompiling fails, even though code has no errors

Have you ever written a beautiful piece of VB.Net code in your Script Task, but it just refuses to precompile? It happened to me.

It happened after I installed a patch for .Net framework 2.0, and then those silly script tasks just won't precompile no matter what.

Turns out the framework patch updated something to do with VSA (Visual Studio for Applications) engine, the same thing SSIS uses for Script Tasks. This would only happen on installations of SQL Server 2005, SQL Server 2005 SP1.

Updating to SP2 fixes this.

See here for more details:
http://support.microsoft.com/kb/932557
http://support.microsoft.com/kb/931846

Hope this eases some pain.

[+/-] Read More...

[+/-] Summary only...

SSIS FTP Task Password (and other) Problems

Originally posted on SQL Server Central, but posting it here as well for my future reference. :)

I've had some problems with the SSIS FTP Task where it does not seem to make the connections correctly. I keep getting the error "Password is invalid" and all sorts of errors.

I don't really like to use the script task component because of it just creates another area of potential coding error in the project.

But I had to take control of the FTP task functionality myself in order to make it do what I wanted it to do.

I realised that when the FTP Task does a listing, the unix box returns not only the file name but the timestamp as well.
Eg,
Problem 1
directory on the unix box
/files
File1.trg
File2.trg

When listing from FTP Task: remote directory = /files
"10:00 File1.trg"
"12:00 File2.trg"

Now, when the ftp task tries to get these files, its not found, or throws some error.

Problem 2
Passwords. Using a batch file to script the FTP is not a good solution, because at the end of the day, your password is clearly visible in plaintext in the script. Not a good solution. Using the package configuration file (*.dtsConfig) is useless as it too stores passwords in plaintext (till now, ssis hasn't implemented encrypted configuration files). Don't even think of using enterprise library with SSIS, you'll run into major headaches.

Solution
I've since given up on using the FTP task for my FTP, but we can still use SSIS's FTP plumbing and with some extra coding, make a properly platform independent and secure FTP function.

I've removed the error checking and variable assignment to make things easier to read.
Steps:
1. Create a FTP Connection Manager in your SSIS Designer 'RightClick in Connections - New Connection... - FTP'.
2. Create a variable to store the password.
Setup the package to use configuration file to store the password variable (and other information you need).
3. Fill in the FTP information as needed. 3.
4. Write a simple .net application that does string encryption. I won't put code for this here. You can google how to do this easily.
5. Use this tool to navigate the xpath of the dtsConfig file to password variable's value and encrypt it. This way, the password is not plaintext on the dtsconfig file.
6. Create a script task to take the password variable's (as readwrite) value and 'decrypt' it, remember to use whatever method was used to encrypt it in your separate .net app. Assign the decrypted password back to itself.
6. Create a script task to do the FTP functionality.
Public Sub Main()
'TODO: assign variables here...
dim password as string = dts.variables("vPassword").value.tostring 'the decrypted password

'Get instance of the connection manager.
Dim cm As ConnectionManager = Dts.Connections("FTPConnMgr")
'Set the password property to the decrypted password
cm.Properties("ServerPassword").SetValue(cm, password)

'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connect to the ftp server
ftp.Connect()

ftp.SetWorkingDirectory(remoteDir) 'set the remote directory

Dim files(0) As String
files(0) = fileToGet 'eg. File1.trg

'Get the file
ftp.ReceiveFiles(files, localDir, True, True)

' Close the ftp connection
ftp.Close()


Dts.Events.FireInformation(0, context, "File " + fileToGet + " retrieved successfully.", Nothing, Nothing, True)
Dts.TaskResult = Dts.Results.Success

End Sub

So there you go. An ssis package that decrypts a encrypted password on a dtsconfig file, decrypts it at runtime, and doesn't use batch files that expose the password.

If you'd like to know how to do a batch get of all files in a directory, i can show in another post. Whenever you need to change the password, just use that separate .net app to modify the dtsconfig file. Nothing needs to be done on the package.

[+/-] Read More...

[+/-] Summary only...

Dynamically modifying an SSIS 2005 package

There may be a time when you need to create a package with a data flow that needs to be modified at run-time. This means that at design time, you don't create any column mappings, or maybe a few standard columns like a 'CreateDate' or 'UpdateDate' columns, but the actual data columns are not known at design time.

This requirement creates some problems like:
1. SQL Server doesn't have the necessary columns to store the data.
2. To create columns in SQL, we need the data type and length (if applies) for each column.
3. SSIS 2005 user scripts can't be used to modify the SSIS in which it is contained.
and a couple of more issues that i can't remember anymore... haha...

Well one approach will be to write an application/service that will:
1. Update SQL table with new/modified tables
2. Load the package and create sql table mappings.
3. Execute the package, if required.

We'll take a look at each of these steps in more detail.
1. Update SQL table with new/modified tables
1.1 To update SQL table, we need a the column definitions ie column name, datatype, length. This can be obtained from the source table and written to a csv file or read directly from the source database's INFORMATION_SCHEMA.COLUMNS view. Here's a sample code:
select COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Client'
Note: if you're integrating data between different databases eg. UniVerse -> SQL, Oracle -> SQL, SQL -> Oracle, etc..., you may need a function that will 'map' the different datatype between the database types.

1.2 Once you have the column definitions, you can make the connection to the SQL database (I'm assuming its sql, you can do it with other databases as well) and create your database table's columns.
Note: You need to import the following namespace in your application to be able to do the database manipulation:
Microsoft.SqlServer.Management.Smo()
Microsoft.SqlServer.Management.Common()
Code example to acquire a connection to the database in your program:

Dim db As Microsoft.SqlServer.Management.Smo.Database
'Get connection settings
Dim DBServerName, DBUser, DBPassword, DBName As String
DBServerName = "MyServer"
DBUser = "sa"
DBPassword = "sa_password"
DBName = "MyDatabase"
Dim SrvConn As New Microsoft.SqlServer.Management.Common.ServerConnection(DBServerName)
SrvConn.LoginSecure = False
SrvConn.Login = DBUser
SrvConn.Password = DBPassword
Dim srv As New Server(SrvConn)
'Open Required Database
db = srv.Databases(MyDatabase)

To get to the table you want to edit, use the db.Tables(tableName as string) function

1.3 Write code to add the columns
Dim tb As Table = db.Tables(tableName)
Dim newDataType As SqlDataType
newDataType = DataType.VarChar(intDataTypeLength)
Dim newColumn As New Column(tableName, columnName, newDataType)
tb.Columns.Add(newColumn)
Your SQL table is now ready for mapping.

2. Now the more difficult part.
Import the following namespaces:
Microsoft.SqlServer.Dts.Runtime.Wrapper
Microsoft.SqlServer.Dts.Runtime
Microsoft.SqlServer.Dts.Pipeline.Wrapper

2.1 Load the package (i'm assuming its on filesystem)
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package
pkg = app.LoadPackage("C:\PackageToEdit.dtsx", Nothing)

2.2 Now that you have loaded the package, we now need to 'navigate' to the component that is the dataflow component for the table you're going to modify.
To modify a dataflow, we have to start updating the names from the 'top' of the flow and end at the 'bottom' of the flow. This is what's called as the 'pipeline' by Microsoft.
Here's a basic sequence of modification we'll make.
a. Update the source connection manager with column names
b. Update the data flow source component
c. Update the data flow destination component
We'll go into detail now...

2.2.a Load the source connection manager.
You should know the name of your connection manager during design time. Its always good to use a naming convention for your components so they can be parameter driven if necessary.
What you need at this stage:
i) a variable / array containing the column names to be updated
ii) the source connection manager name as used in the SSIS Designer
iii) the package variable (we already loaded earlier)
Steps:
i. Get the connection manager object from the list of package's connection managers. I'm using an example of a flat file connection manager.
Dim SrcConn As ConnectionManager = Nothing
'Get specific connection
SrcConn = pkg.Connections.Item(ConnectionName)
'Get the underlying connection object
Dim FlatConnMgr As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerFlatFile90
FlatConnMgr = CType(SrcConn.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerFlatFile90)
ii. Now we create a new column and add to the connection manager's columns collection
Dim NewCol As RTW.IDTSConnectionManagerFlatFileColumn90 = nothing
FlatConnMgr.Columns.Add(NewCol)
'Assign the new column's properties
NewCol.ColumnType = "Delimited"
NewCol.ColumnDelimiter = "~"
NewCol.ColumnWidth = 1
NewCol.DataType = Wrapper.DataType.DT_STR
NewCol.MaximumWidth = 1
NewCol.DataScale = 0
NewCol.DataPrecision = 0
name = CType(NewCol, RTW.IDTSName90)
name.Name =
name.Description =
NOTE : Data type incompatibility. Looks easy? Here's a problem.

SSIS datatypes and SQL data types are NOT the same. You need to do some 'mapping' between the SSIS data type and SQL datatype. We already know what the column's SQL data type is, because we used it to create the SQL column, remember? So we just need to have a function that can do some 'mapping' for us. For your convenience, i'll include what i use, and you can modify if necessary.
Public Function MapSQLtoSSISDataType(ByVal SQLDT As SqlDataType) As Dts.Runtime.DataType
Select Case SQLDT.SqlDataType
Case SqlDataType.BigInt
Return Wrapper.DataType.DT_I8
Case SqlDataType.Binary
Return Wrapper.DataType.DT_BYTES
Case SqlDataType.Bit
Return Wrapper.DataType.DT_BOOL
Case SqlDataType.Char
Return Wrapper.DataType.DT_STR
Case SqlDataType.DateTime
Return Wrapper.DataType.DT_DBTIMESTAMP
Case SqlDataType.Decimal
Return Wrapper.DataType.DT_DECIMAL
Case SqlDataType.Float
Return Wrapper.DataType.DT_NUMERIC
Case SqlDataType.Image
Return Wrapper.DataType.DT_BYTES
Case SqlDataType.Int
Return Wrapper.DataType.DT_I4
Case SqlDataType.Money
Return Wrapper.DataType.DT_CY
Case SqlDataType.NChar
Return Wrapper.DataType.DT_WSTR
Case SqlDataType.None
Return Wrapper.DataType.DT_STR
Case SqlDataType.NText
Return Wrapper.DataType.DT_NTEXT
Case SqlDataType.NVarChar
Return Wrapper.DataType.DT_STR
Case SqlDataType.NVarCharMax
Return Wrapper.DataType.DT_STR
Case SqlDataType.Real
Return Wrapper.DataType.DT_DECIMAL
Case SqlDataType.SmallDateTime
Return Wrapper.DataType.DT_DBTIMESTAMP
Case SqlDataType.SmallInt
Return Wrapper.DataType.DT_I2
Case SqlDataType.SmallMoney
Return Wrapper.DataType.DT_CY
Case SqlDataType.SysName
Return Wrapper.DataType.DT_STR
Case SqlDataType.Text
Return Wrapper.DataType.DT_TEXT
Case SqlDataType.Timestamp
Return Wrapper.DataType.DT_DBTIME
Case SqlDataType.UniqueIdentifier
Return Wrapper.DataType.DT_GUID
Case SqlDataType.VarChar
Return Wrapper.DataType.DT_STR
Case SqlDataType.VarCharMax
Return Wrapper.DataType.DT_STR
Case SqlDataType.Xml
Return Wrapper.DataType.DT_STR
Case Else
Return Wrapper.DataType.DT_STR
End Select
End Function
You can use this function to return the appropriate SSIS data type from the SQL data type you have on the database. Not perfect but you can use it as a base.

iii. Once you've done the above for all your new columns, your Source Connection Manager will now have the necessary columns to be accessed downstream in the data flow.

2.2.b Update the Data Flow Components
Information you need at this point:
i) The name of the data flow component to update
ii) All the names of the data flow components you want to update as used in the SSIS Designer.
iii) list of new columns to add.
Steps:
i) Get the data flow component object.
Dim DataFlowTaskHost As TaskHost
Dim DataFlowMainPipe As MainPipe
DataFlowTaskHost = CType(pkg.Executables(), TaskHost)
DataFlowMainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
We now have the main pipeline of the data flow (whatever you see in the 'Data Flow' in the SSIS designer)

ii) Now get the data SOURCE object (top of the data flow). I'm assuming this data source component is set up to use the data source connection manager we already updated.
Dim DFSource As IDTSComponentMetaData90
DFSource = DataFlowMainPipe.ComponentMetaDataCollection.Item(SourceComponentName)
'Create an Instance of the source component
Dim instDfSource As CManagedComponentWrapper = DFSource.Instantiate
Now we 're-assign' the connection manager for this component so that it retrieves the new values that was updated on the connection manager earlier.
DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections.Item().ID
DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections(SourceComponentConnectionMgrName))
instDfSource.AcquireConnections(Nothing)
instDfSource.ReinitializeMetaData()
instDfSource.ReleaseConnections()
Note: the reinitalizeMetaData step is very important as it updates the internal/external metadata to be seen by other components.

iii) Now we need to update the data DESTINATION object. In this example, i'm assuming its an OLEDB Insert object.
As for the source component, we need to get hold of the component's object
Dim cmDestination As IDTSComponentMetaData90
cmDestination = DataFlowMainPipe.ComponentMetaDataCollection.Item(DataFlow Insert Dest. Name)
Dim instDFDest As CManagedComponentWrapper = cmDestination.Instantiate
Reassign the connection manager
cmDestination.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections.Item(DestinationComponent ConnectionManager Name).ID
cmDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections(DestinationComponent ConnectionManager Name))
instDFDest.AcquireConnections(Nothing)
instDFDest.ReinitializeMetaData()
Get the input object of the component so that we can add the new columns.
Dim input As IDTSInput90 = cmDestination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
We need to map each column of the input object's to the external metadata columns of the 'upstream' component, which is our source. This will make the Insert component aware of the new columns.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
Dim vCol As IDTSInputColumn90 = instDFDest.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE)
instDFDest.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
Next
to finalise the changes we need to reinitialise the metadata.
instDFDest.ReinitializeMetaData()
instDFDest.ReleaseConnections()
The 'ReinitialiseMetaData' will actually check the columns against the SQL table (using its assigned connection manager) and report any errors here.
Done! you've got your components updated with the custom data.
If you'd like to see the changes, save your package using the app.SaveToXml function and you can open it in the editor.
This is a 'rough' guide and you'll need to add and fine tune it to your purposes.
Let me know if you found it helpful.

[+/-] Read More...

[+/-] Summary only...

What makes programming difficult?

... statements like this:

One difference between HTTP handlers and ISAPI extensions is that HTTP
handlers can be called directly by using their file name in the URL, similar to
ISAPI extensions

haha!

[+/-] Read More...

[+/-] Summary only...