Mapping UDF Parameters to Variables

As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.

So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.

The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.

Dick,

This doesn't help you, but I think there IS a bug with the OLEDB data source and passing variables. I have exactly the same issue with a simple query to a Microsoft FoxPro data source (which I've brought to this forum before and got no solution).

I went with the package variable solution - it's not as elegant but it works

Do you know where we should post bug reports for SQL 2005?

Rich

|||

Thanks Rich,

It's some consolation the hear from someone else about the problem. Foxpro is a "foriegn" application (albiet a Microsoft one). It's the fact that the problem occurs within SQL Server 2005 that is a bit of a surprise. Add to this the fact that Reporting Services seems to handle UDF parameters correctly (indicating that there is no reason why it shouldn't work) and it is a bit frustrating.

As you say contructing the entire SQL Command Text as a Package Variable is a work around but this is not really what was intended, Mapping Package Variables to parameter markers is more within the spirit of SSIS and much nicer. It seems to work with Stored Procedures for example.

My company has links with MS so I will try to find out on Monday how to officially report this.

Best regards,

Dick Campbell

Map One generic Input column to multiple Destination column

I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".

I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.

But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.

So how do I go about doing this the smart way?

thanks in advance!

Hi,

You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.

Jean-Pierre Riehl

http://blog.djeepy1.net

http://www.bewise.fr

|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.

|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.

Manipulating dates in SQL

I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?

Start Date... This is the first day of the month, one year ago... in VB I worked it out as...

dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

End Date... This is the last day of the previous month... in VB I worked this one out as...

dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006

Any help would be appreciated.

I managed to work out a solution... I get my Start date by using the following...

(select dateadd(yyyy,-1,(select stuff(stuff((convert(varchar,
convert(varchar,datepart(year,getdate()))+
convert(varchar,datepart(Month,getdate()))+
convert(varchar,(convert(int,datepart(day,getdate())))- (convert(int,datepart(day,getdate()))-1))))
,5 ,0, '-'),7,0, '-'))))

|||

Hey Jon,

This should do what you want it to.

select dateadd(mm, datediff(mm, 0, dateadd(yy, -1, getdate())), 0) as StartDate,
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, getdate()), 0)) as EndDate

Hope this helps.

Jarret

|||

That's great... thanks Jarret.

A much better solution than mine... especially since I noticed mine only works if the month is a single digit.