Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

manually manipulate parameter info

If i want to go directly against the ReportServer database to manipulate parameter info for a particular report definition, which table is that data stored in?

Probably not something you can do like that, you may need web service.
The Catalog table contains information that may help you (text & binary blobs though)

Manipulating dates in an expression

Does anyone know a way to subtract a date from a date
parameter (e.g. = Parameters!Date.Value - 1, which of
course does not work)
thanksYou might want to read the MSDN documentation for the DateTime class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
E.g. subtracting one day would be =Parameters!Date.Value.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks|||Here is how I subtracted one date from another to get a number of days
between them:
=Fields!DESIRED_SHIP_DATE.Value.ToOADate() -
Fields!ORDER_DATE.Value.ToOADate()
This information below is not a direct solution for your problem, but the
information may help you with other things data/time related.
Here is how I get the current hour and manipate it:
The DateAndTime.Hour(Now) return a 24 hour clock output for the hour, so
this code turns hour 13 to 1 (like 1pm).
Dim cHour As Integer = DateAndTime.Hour(Now)
If cHour > 12 Then
StartHourBox.Text = cHour - 12
Else
StartHourBox.Text = cHour
End If
Look at all the different options available to you with "DateAndTime",
including Day, Month, and year.
Later,
Ed Hammond
--
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks