Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

Conversion error when calling stored procedure

Hi all,
I want to execute a stored procedure from a report. The stored procedure
takes nvarchar(50) parameters. When I call the procedure from a report with
string paramerers, I get the error 'Implicit conversion from data type
sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
reporting services parameters when calling Exec to run the stored procedure.
Help! Anyone experience this kind of problem before? Any suggestions are
welcome!hey.
Not sure why it happens as I get those all the time to. I have found that
if i create a make the first dataset something simple, like the query to
populate a parameter drop down list, then add a second data source I can then
call the execute statement for the proc. Boqus I know but it works. Buggy
software is my quess, I think it may have been the service pack as I did not
do this a few months back.
hth
"Bas" wrote:
> Hi all,
> I want to execute a stored procedure from a report. The stored procedure
> takes nvarchar(50) parameters. When I call the procedure from a report with
> string paramerers, I get the error 'Implicit conversion from data type
> sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
> reporting services parameters when calling Exec to run the stored procedure.
> Help! Anyone experience this kind of problem before? Any suggestions are
> welcome!
>

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Control Report Parameters Display

Is there a way to control the way report parameters are displayed in terms of number of columns etc. The reason we need that is we have like 15 parameters in one of the report and then it just looks ugly.

Thanks.

Not if you are viewing the Reports via Report Manager. If you are using the Viewer Controls that ship with VS 2005 you could but it would mean writing your own parameter area.

Control parameter help......

I'm writing a page to do some reporting off of one of our databases, and I'm using 3 control parameters for my sql query that feeds my datagrid. 2 of the ControlParameters are from dropdownlists, and one is from a RadioButtonList. The ControlParameters that reference the dropdownlists are both working well, but the one for the RadioButtonList is not.

The error I am getting is:Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near'@.Booga'. (I changed my ControlParameter name from DateStr to Booga to try to avoid any conflicts with reserved words.)

Any ideas? Here is a snippet of my code:

<

formid="form1"runat="server"><tablewidth="100%"><tr><tdstyle="text-align: center"><strong>DataCenter</strong></td><tdstyle="text-align: center"><strong>Time Scope</strong></td><tdstyle="text-align: center"><strong>Call Status</strong></td></tr><tr><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList1"DataSourceID="SqlDataSource2"AutoPostBack="true"DataTextField="LocationName"runat="server"/></td><tdstyle="height: 47px; text-align: center"><asp:RadioButtonListID="RadioButtonList1"runat="server"AutoPostBack="true"Font-Size="Smaller"><asp:ListItemSelected="True"Value=" 1 = 1 ">All</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 1) >= (CAST(GETDATE() AS smalldatetime)))">Last Day</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 7) >= (CAST(GETDATE() AS smalldatetime)))">Last Week</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 30) >= (CAST(GETDATE() AS smalldatetime)))">Last 30 Days</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 120) >= (CAST(GETDATE() AS smalldatetime)))">Last 120 Days</asp:ListItem></asp:RadioButtonList></td><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList2"AutoPostBack="true"runat="server"><asp:ListItemSelected="True"Value="Open">Open</asp:ListItem><asp:ListItemValue="Closed">Closed</asp:ListItem></asp:DropDownList></td></tr></table><br/><pstyle="text-align: center"><strong>Displaying All Open Tickets</strong><br/></p><asp:SqlDataSourceID="SqlDataSource2"runat="server"SelectCommand="SELECT DISTINCT [locationname] FROM [profile]"ConnectionString="<%$ ConnectionStrings:Heat %>"/><tablewidth="100%"><trwidth="100%"><tdvalign="top"width="100%"><asp:GridViewID="GridView1"AllowSorting="True"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="CallID"AutoGenerateColumns="False"Font-Size="Smaller"Width="100%"><Columns><asp:CommandField/><asp:BoundFieldDataField="CallID"HeaderText="Call ID"ReadOnly="True"SortExpression="CallID"/><asp:BoundFieldDataField="CustID"HeaderText="Customer ID"ReadOnly="True"SortExpression="CustID"/><asp:BoundFieldDataField="CallType"HeaderText="Call Type"ReadOnly="True"SortExpression="CallType"/><asp:BoundFieldDataField="Priority"HeaderText="Priority"ReadOnly="True"SortExpression="Priority"/><asp:BoundFieldDataField="Cause"HeaderText="Cause"ReadOnly="True"SortExpression="Cause"/><asp:BoundFieldDataField="CallDesc"HeaderText="Call Description"ReadOnly="True"SortExpression="CallDesc"><ItemStyleWidth=40%/></asp:BoundField><asp:BoundFieldDataField="RecvdBy"HeaderText="Received By"ReadOnly="True"SortExpression="RecvdBy"/><asp:BoundFieldDataField="RecvdDate"HeaderText="Call Date"ReadOnly="True"SortExpression="RecvdDate"><ItemStyleWrap="False"/></asp:BoundField><asp:BoundFieldDataField="RecvdTime"HeaderText="Call Time"ReadOnly="True"SortExpression="RecvdTime"><ItemStyleWrap="False"/></asp:BoundField></Columns></asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "ConnectionString="<%$ ConnectionStrings:Heat %>"><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="LocationName"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="DropDownList2"Name="CallStatus"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource></td></tr></table><br/><br/><br/>

<br />

</form>

Your SQL looks incomplete:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "

What is @.Boonga supposed to be related to?

|||

That's where the ControlParameter problem is:

<asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/>

That should be making the SQL command end with one of the date comparisons that are controlled by the radiobuttonlist control.

Control Date Range in Rpt Svc

lHi,
I have two date parameters (Start Date and End Date) in one report and it
alows users input startdate and enddate. But I don't want users execute
reports more than 5 days from the start date. How do I limit it before the
reprot gets executed? Thanks.
ChuckProbably the easiest way to control this would be in the report parameters.
Instead of allowing them to enter a start date and end date allow them to
enter one of the dates, then set the second parameter as the number of days
to include in the report and set a drop down for those values, 1-5.
For instance, if you want them to be able to enter the end date and create a
report for the previous five days you wuold set your parameters up like this.
Parameter Name: @.EndDate
Type: Date/Time
Parameter Name: @.StartDate
Type: Date/Time
In the Report Parameters dialog for StartDate set the available values as:
Label Value
1 day DateAdd(day, -1, @.EndDate)
2 days DateAdd(day, -2, @.EndDate)
ect, ect...
This should allow them to select any ending date and from 1 to 5 days
previous for the start date.|||Hi JHoward,
Thank you for getting back to me. This is one solution. However, report
users does not like the format because they have to add days into start date
to figure out the End Date. Is there a way that if it is more than 5 days,
it will bring up an alert message and will NOT execute the report even though
a user click 'View Report'? Thanks.
Chuck
"JHoward" wrote:
> Probably the easiest way to control this would be in the report parameters.
> Instead of allowing them to enter a start date and end date allow them to
> enter one of the dates, then set the second parameter as the number of days
> to include in the report and set a drop down for those values, 1-5.
> For instance, if you want them to be able to enter the end date and create a
> report for the previous five days you wuold set your parameters up like this.
> Parameter Name: @.EndDate
> Type: Date/Time
>
> Parameter Name: @.StartDate
> Type: Date/Time
> In the Report Parameters dialog for StartDate set the available values as:
> Label Value
> 1 day DateAdd(day, -1, @.EndDate)
> 2 days DateAdd(day, -2, @.EndDate)
> ect, ect...
> This should allow them to select any ending date and from 1 to 5 days
> previous for the start date.
>
>
>

Continued frustration with parameters : jump to report

I have one report (R1) which includes a list of ports ("=Fields!Port_Name.Value")

Another report (R2) supplies details for individual reports and has a parameter named PortDWIDPortName which gets port names from the same dimension as those in R1. So in the field properties of the Port field in R1, I selected Navigation > Jump To Report and entered PortDWIDPortName = Fields!Port_Name.Value (also tried changing .Value to .Key or .UniqueName) but although it jumps to report R2 it does not enter a value for the parameter (ie leaves it blank or displays <Select Value> depending on whether multivalue or not).

Does anyone know the right syntax to pass the parameter ? If not, does anyone know to view what Jump To Parameter is returning so I can figure how to doctor it!

Thanks

I can't believe it I think I have the answer : it is ="[Port DWID].[Port Name].&[" & Fields!Port_Name.Value & "]"

Well that was kind of intuitive ... not!!

Richard

Consuming Stored Procedure Output Param

This is my SProc:

CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @.numberRows */
@.selectDate datetime
,@.selectCountry int
,@.numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_smc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDate and RequestDateTime < dateadd(dd,1, @.selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @.selectCountry
ORDER BY configname

/* @.@.ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @.numberRows = @.@.RowCount

GO

And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue("@.selectDate", dateEntered)

myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))

Dim myParamAsNew SqlParameter("@.numberRows", SqlDbType.Int)

myParam.Direction = ParameterDirection.Output

myCommand.Parameters.Add(myParam)

myCommand.Connection = con

con.Open()

Dim readerAs SqlDataReader = myCommand.ExecuteReader()Dim rowCountAsInteger = reader.RecordsAffected

numberParts.Text = rowCount.ToString

con.Close()

EndSub

What should I fix?

label1.Text = myCommand.Parameters("@.numberRows").Value

|||

If I remember, I had this same problem, and found that you can't use the DataReader if you want to get the output parameter. I think you have to use DataSet.

|||

Read the following for an explanation of why it is happening and how to get around it.

http://p2p.wrox.com/archive/aspx/2001-12/24.asp

|||

How do I do the DataSet approach?

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection("Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx")

Dim dbDataSet =New DataSet()Dim dbAdapterAsNew SqlDataAdapter

dbAdapter.Fill(dbDataSet)

|||

You can do the following

Dim dbDataSet =New DataSet()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataSet,"tablename")

dbDataSet.Tables("tablename").rows.count

In case you have only one table, you can use a datatable instead of a dataset

Dim dbDataTable =New DataTable()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataTable)

dbDataTable.rows.count

consuming parameters

Hi
I open a report using an url. In the end of the url i add &TEST=123. In
Reporting Services I have added the @.TEST parameter. I would like this
parameter to get the value of 123. How do I consume the parameter in the url?
Do I use the Report Parameters dialog in Reporting Services?
Please Help
JuliaJulia:
I'll take a stab at answering this for you... I hope this will help give you
a push in the right direction.
ok, you are sorta on the right track... but what you are going to need to do
is look into the reporting services documentation and specifically at passing
values to the report service. For instance, if you go to your report manager
and click on a report, you will see at the top that there are indeed
parameters being passed to the report service like your query string example
that you have. However, there is a specific format that you need to follow,
that is where the documentation on the reporting service will help.
See, there essentially 2 parts to the parameters that get passed in... the
first being the options for how the report will display (like showing
different options in the toolbar, showing the parameter prompts, ect) and the
second being values that you are supplying to parameters in your report.
The documentation on this is a little confusing at first, but stick with
it... you will be able to get it working.
I "hope" that this helps.
"Julia" wrote:
> Hi
> I open a report using an url. In the end of the url i add &TEST=123. In
> Reporting Services I have added the @.TEST parameter. I would like this
> parameter to get the value of 123. How do I consume the parameter in the url?
> Do I use the Report Parameters dialog in Reporting Services?
> Please Help
> Julia|||One other point. Julia. You are putting the cart before the horse. Before
making any attempt to call a report via a URL you should first get the
report working. You need to create a report with query parameters. RS
automatically creates the report parameters for you when you do this. It is
important to realize the difference between query and report parameters.
Until you have a working report don't go anywhere near URL integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5493C3E7-4070-4018-960B-776EF9EDC4DD@.microsoft.com...
> Julia:
> I'll take a stab at answering this for you... I hope this will help give
> you
> a push in the right direction.
> ok, you are sorta on the right track... but what you are going to need to
> do
> is look into the reporting services documentation and specifically at
> passing
> values to the report service. For instance, if you go to your report
> manager
> and click on a report, you will see at the top that there are indeed
> parameters being passed to the report service like your query string
> example
> that you have. However, there is a specific format that you need to
> follow,
> that is where the documentation on the reporting service will help.
> See, there essentially 2 parts to the parameters that get passed in... the
> first being the options for how the report will display (like showing
> different options in the toolbar, showing the parameter prompts, ect) and
> the
> second being values that you are supplying to parameters in your report.
> The documentation on this is a little confusing at first, but stick with
> it... you will be able to get it working.
> I "hope" that this helps.
> "Julia" wrote:
>> Hi
>> I open a report using an url. In the end of the url i add &TEST=123. In
>> Reporting Services I have added the @.TEST parameter. I would like this
>> parameter to get the value of 123. How do I consume the parameter in the
>> url?
>> Do I use the Report Parameters dialog in Reporting Services?
>> Please Help
>> Julia|||VHi
And thanks for the answers!
Ok, I have a working report that I can open from Report Manager. The report
shows data for one order that I have in my databse. In the databse I have
many orders so I would like to send the orderId to the select string (select
a, b, c from Order where OrderId = @.TEST). This is working from Report
Manager but then the user needs to add the orderId manually and press View
Report.
The user will work with an asp.net application and select an order from a
list and then press a button (or link) to view the order. I need to send the
orderId to the report. I have tried to add ?TEST=123 in the end of the URL
but that doesn't work. I would really need an example that I could run in my
development environment to see how this should work.
Thanks again
Julia
"Bruce L-C [MVP]" wrote:
> One other point. Julia. You are putting the cart before the horse. Before
> making any attempt to call a report via a URL you should first get the
> report working. You need to create a report with query parameters. RS
> automatically creates the report parameters for you when you do this. It is
> important to realize the difference between query and report parameters.
> Until you have a working report don't go anywhere near URL integration.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5493C3E7-4070-4018-960B-776EF9EDC4DD@.microsoft.com...
> > Julia:
> >
> > I'll take a stab at answering this for you... I hope this will help give
> > you
> > a push in the right direction.
> >
> > ok, you are sorta on the right track... but what you are going to need to
> > do
> > is look into the reporting services documentation and specifically at
> > passing
> > values to the report service. For instance, if you go to your report
> > manager
> > and click on a report, you will see at the top that there are indeed
> > parameters being passed to the report service like your query string
> > example
> > that you have. However, there is a specific format that you need to
> > follow,
> > that is where the documentation on the reporting service will help.
> >
> > See, there essentially 2 parts to the parameters that get passed in... the
> > first being the options for how the report will display (like showing
> > different options in the toolbar, showing the parameter prompts, ect) and
> > the
> > second being values that you are supplying to parameters in your report.
> >
> > The documentation on this is a little confusing at first, but stick with
> > it... you will be able to get it working.
> >
> > I "hope" that this helps.
> >
> > "Julia" wrote:
> >
> >> Hi
> >>
> >> I open a report using an url. In the end of the url i add &TEST=123. In
> >> Reporting Services I have added the @.TEST parameter. I would like this
> >> parameter to get the value of 123. How do I consume the parameter in the
> >> url?
> >> Do I use the Report Parameters dialog in Reporting Services?
> >>
> >> Please Help
> >> Julia
>
>