data driven subscription not available

Hi,
I am trying to create a data driven subscription. I have stored the
credentials and custom data source in the report. My account has site
administrator and content manager permissions, yet the "New Data Driven
Subscription" button is not available. I have multiple reports like this.
Any ideas?
MarcusAre you on Enterprise Edition of Reporting Services, it's an EE only
feature.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> Hi,
> I am trying to create a data driven subscription. I have stored the
> credentials and custom data source in the report. My account has site
> administrator and content manager permissions, yet the "New Data Driven
> Subscription" button is not available. I have multiple reports like this.
> Any ideas?
> Marcus|||That explains it... Thanks!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||That might explain my problem...where does it state that it's an EE only
option? The developer edition supports it as well (obviously I suppose) but I
hadn't realised it wouldn't work in production!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||If you go to the SQL Server site then navigate to the reporting services page
and then find the feature compairison it is listed there.
For us the only thing we need EE for is the data driven subscriptions and I
can't get the boss to come off the hip with 10x the money for that one
feature.
I am going to try and write some code to run reports from the reporting
server using .net and sql statements.
"Paul Hasell" wrote:
> That might explain my problem...where does it state that it's an EE only
> option? The developer edition supports it as well (obviously I suppose) but I
> hadn't realised it wouldn't work in production!!
> "Jasper Smith" wrote:
> > Are you on Enterprise Edition of Reporting Services, it's an EE only
> > feature.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> > news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > > Hi,
> > >
> > > I am trying to create a data driven subscription. I have stored the
> > > credentials and custom data source in the report. My account has site
> > > administrator and content manager permissions, yet the "New Data Driven
> > > Subscription" button is not available. I have multiple reports like this.
> > >
> > > Any ideas?
> > >
> > > Marcus
> >
> >
> >

Data Driven Subscription - Open Connections

Hi,

I'm trying to run a data driven subscription on a report in the rs2005,

and using a data source to oracle,

I found out that when the subscription runs it opens a connection to oracle for each report and it means that i have more than fifteen open connections every time which causes a performance problems

did anyone encounter this kind of problem

pls help

Data driven subscriptions runs the report for each row you return in your delivery query.

You can minimze the impact on the underlying RDBMS by changing the execution mode of the report to cache or snapshot. The first will ensure the minimal number of query executions occur on your underlying RDBMS. The second option will ensure only one query execution will occur (whenever the snapshot is updated). This has some benefits, but may require you to update your report to filter content for each user.

Hope that helps,

-Lukasz

|||

Is this report going against transaction tables, is that why there is a performance problem? If for some reason you can't do what Lukasz suggests maybe you can snapshot or cache the data yourself (whether in Oracle or an external mart somewhere)?

This way you massage the data once up front into the form your reports need, take the load off your transaction tables, even avoid any potential licensing issues on the Oracle side if that has been a concern with these connections.

There are probably a lot of ways to skin this particular cat <s>.

>L<

Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:

Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Data Conversion Error

Hi,

I am transferring data from a Flat file source to SQL Database. Initially I got validation error saying

"Validation error. Import Employee Information: [DataFlowTask] [6384]: The column "[ColumnName] " can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported." ( i have 6 date fields)

The destination in SQL has the field in "datetime" format.

So i used a "Data Conversion" task to transform the fields, thereby resolving the validation errors. But when i Execute the dtsx I get the following error :

[Data Conversion [322]] Error: Data conversion failed while converting column "[ColumnName]" (79) to column "[Modified_ColumnName]" (346). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [322]] Error: The "output column "[Modified_ColumnName]" (346)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "[Modified_ColumnName]" (346)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Data Conversion" (322) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

["InputFileName" Input [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "InputFileName Input" (1) returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

Can anybody help me with this?

The "The value could not be converted because of a potential loss of data" error generally occurs when you're trying to convert a wider data type value into a data type too narrow to hold it. Can you look at the metadata in your data flow to see what the exact data types are for the source ("[ColumnName]") and target ("[Modified_ColumnName]") columns?|||Can you post some of your sample data in the [ColumnName] column?|||

Hi,

I am transferring data from .dat file to tables in SQL DB. The output from .dat file is in [DT_STR] datatype.

[ColumnName1] [ColumnName2]

04/27/1987 0

05/20/1958 0

03/12/1982 1

05/04/1957 2

Error 23 Validation error. [TaskName]: [TableName] [9]: The column "[ColumnName1]" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

The table in the DB has "datetime" as its datatype

Error 26 Validation error. [TaskName]: [TableName] [9]: The column "[ColumnName2]" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

The table in the DB has "int" as its datatype

So i used the "data conversion" task in SSIS and resolved the validation error.

But when i executed the dtsx i got the error mentioned in my previous post

|||I'd add a derived column transformation between the source and the destination.

In that derived column, you'd cast the values appropriately.

Expressions below:
NewOutputColumnName1: (DT_DBTIMESTAMP)[ColumnName1]
NewOutputColumnName2: (DT_I4)[ColumnName2]

Data Conversion - NULL datetime causes failure

Hi,

I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an error output. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug? It seems to me it is. I can't believe this would be the desired behavior?!

Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?

Thanks

Do you have the retain nulls option checked in the Flat File Source?

If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

Code Snippet

([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

|||Thanks much - the checkbox in the flatfile source did the trick. Never noticed that before, but it worked great. Thanks again.

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed th
e
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly adviseTiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

Data Conversion

Hello,

What will the Dataconversion do if I try to convert a source field to a datetime but the data is not a valid date?

Can I just skip the one field?

Thanks,

Michael

Hi Michael,

Most likely, you will get a conversion error. If you open the UI for the data conversion, and hit the "Configure Error Output" button, you can set the error handling behaviour on a column-by-column basis. If you just want to skip that field, you could set the error behaviour for that column to "Ignore Failure". That column will be set to NULL.

Let me know if you have further questions about this.

Thanks
Mark

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly advise
Tiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly adviseTiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

Daft DTS Data Driven task question

Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:

UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)

The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.

Anyone know the answer - I'll bet it's simple :)

Cheers,

MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.

Very strange.

Customizing replication

Hi,
Is it possible to replicate some field from multiple tables (as a result of select query with joins) at source to a single table at target ?
For example i have two tables at source 'source_table1' and 'source_table2' and one table at target namely 'target_table'. Now i want Field1 from 'source_table1' and field1 from 'source_table2' to be replicated into 'target_table'.
pictorial depiction of behavious i need is as under:
Tables at Source :
Table 1 Table at Target:
\ _________ Table
/
Table 2
1) Is is possible using SQL Server replication ? If not is there any workaround ?
2) Is is a good practice to replicate GBs of data from source to target over internet with security being an issue ?
Thanks in advance,
Hatim Ali.
1) it sure is, you create a custom sync object. The problem is that the log
reader can generate sql statements or insert procs based on updates of a
single object.
So one your publication or article will have to key off one table, and
probably replicate the second table as well so your custom proc can read
this data and merge it to the target table.
The simple way of doing this is using an indexed view.
2) To be secure you should use a VPN. Replicating Gbs of data can be
difficult, but it can be done. Security is a matrix of risk and liability.
Risk is small using FTP, anonymous authentication, and only allowing a range
of IP addresses to download your snapshot, but the liability can be
signficant.
I have had a client who couldn't care less if you snag their data because it
has a very high time value (they were a news agency). Then I have financial
clients and if I mention the word FTP I loose all credibility - not that I
have much to begin with mind you
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
news:99A07D96-B98C-41CC-947D-05F35FB8E018@.microsoft.com...
> Hi,
> Is it possible to replicate some field from multiple tables (as a result
of select query with joins) at source to a single table at target ?
> For example i have two tables at source 'source_table1' and
'source_table2' and one table at target namely 'target_table'. Now i want
Field1 from 'source_table1' and field1 from 'source_table2' to be replicated
into 'target_table'.
> pictorial depiction of behavious i need is as under:
> Tables at Source :
> Table 1 Table at Target:
> \ _________ Table
> /
> Table 2
> 1) Is is possible using SQL Server replication ? If not is there any
workaround ?
> 2) Is is a good practice to replicate GBs of data from source to target
over internet with security being an issue ?
> Thanks in advance,
> Hatim Ali.
>
>