Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.

Passing object properties to variables

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

One way you could accomplish this is by using a Script task.

You can access the properties of all the connections in your Connection Manager through the Dts ScriptObjectModel .

For example, the following code grabs the ConnectionString of my "currentFolder" connection object, and stores it in the "myConnectionString" variable.

PublicSub Main()

Dim value AsString

value = Dts.Connections("currentFolder").ConnectionString

Dts.Variables("myConnectionString").Value = value

'MsgBox(Dts.Variables("myConnectionString").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

Be sure to include your variable (ie. User::myConnectionString) in the ReadWriteVariables property of your script task.

Is this what you meant?

|||Yes, this is exactly what I meant. Thanks!

passing null reportparameter values

Hi,
Iâ'm using the report viewer object to view a report which works fine until I
need to pass null values. When I use the following code I get â'The
'reportId' parameter is missing a valueâ':
ReportViewer1.ShowParameterPrompts = false;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("reportId");
parameters[1] = new ReportParameter("userid", "123");
ReportViewer1.ServerReport.SetParameters(parameters);
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.Refresh();
The reportId is setup for null values, however it still gives me an error:
<ReportParameter Name="reportId">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<Prompt>reportId</Prompt>
</ReportParameter>
I know other people have had this problem but using â'new
ReportParameter("reportId")â' doesnâ't work for me.
Thanks in advance!I'm guessing here, but do you need to explicitly set the value of the
null parameter. DBNull isn't the same as .Net C# null.
Regards, Rhys
On Sep 21, 11:50 pm, Nathan <nathan.et...@.online.nospam> wrote:
> Hi,
> I'm using the report viewer object to view a report which works fine until I
> need to pass null values. When I use the following code I get "The
> 'reportId' parameter is missing a value":
> ReportViewer1.ShowParameterPrompts = false;
> ReportParameter[] parameters = new ReportParameter[2];
> parameters[0] = new ReportParameter("reportId");
> parameters[1] = new ReportParameter("userid", "123");
> ReportViewer1.ServerReport.SetParameters(parameters);
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.Refresh();
> The reportId is setup for null values, however it still gives me an error:
> <ReportParameter Name="reportId">
> <DataType>Integer</DataType>
> <Nullable>true</Nullable>
> <Prompt>reportId</Prompt>
> </ReportParameter>
> I know other people have had this problem but using "new
> ReportParameter("reportId")" doesn't work for me.
> Thanks in advance!

Passing DataTable to Reporting Services 2000

It used to work with ReportDocument Object of CR.This works with the new Visual Studio Report Controls. Take a look at http://gotreportviewer.com.