Saving and Retrieving Binary Files in MSSQL 2005

Dear all,
Can any one provide me a sample source regarding saving and retrieving
binary files in MSSQL 2005. I have try it but I found that it only insert
the first element of that byte array that I read from file to table.
I was using Microsoft Data Access Application Block version 2 SQLhelper
to done the execution portion.
ThanksHi
The text, ntext and image data types have successors: varbinary(max),
varchar(max) and nvarchar(max).
Those can be addressed like normal char and binary columns, no need to chunk
the data in.
Application Blocks 2 don't support he new datatypes as it is still a .NET
1.1 application.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Goh" <goh@.noemail.noemail> wrote in message
news:utOmXEv%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Dear all,
> Can any one provide me a sample source regarding saving and retrieving
> binary files in MSSQL 2005. I have try it but I found that it only insert
> the first element of that byte array that I read from file to table.
> I was using Microsoft Data Access Application Block version 2 SQLhelper
> to done the execution portion.
> Thanks
>
>|||Hello,
Did you try this code?
byte[] mmfbBlob;
SqlParameter Parameter1 = new SqlParameter("@.mmfbBlobThumb",
SqlDbType.Image);
Parameter1.Value = mmfbBlob;
Since the issue is related ADO.net, my suggestion is that you go there
since you could obtain most qualified answers there:
microsoft.public.dotnet.framework.adonet
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Goh" <goh@.noemail.noemail>
>Subject: Saving and Retrieving Binary Files in MSSQL 2005
>Date: Wed, 7 Dec 2005 13:42:56 +0800
>Keywords: Saving and Retrieving Binary Files in MSSQL 2005
>Lines: 13
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <utOmXEv#FHA.2036@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: tm.net.my 60.49.6.190
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:566803
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Dear all,
> Can any one provide me a sample source regarding saving and retrieving
>binary files in MSSQL 2005. I have try it but I found that it only insert
>the first element of that byte array that I read from file to table.
> I was using Microsoft Data Access Application Block version 2 SQLhelper
>to done the execution portion.
>Thanks
>
>|||http://spaces.msn.com/members/staceyw/Blog/cns!1pnsZpX0fPvDxLKC6rAAhLsQ!404.
entry
William Stacey [MVP]
"Goh" <goh@.noemail.noemail> wrote in message
news:utOmXEv%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Dear all,
> Can any one provide me a sample source regarding saving and retrieving
> binary files in MSSQL 2005. I have try it but I found that it only insert
> the first element of that byte array that I read from file to table.
> I was using Microsoft Data Access Application Block version 2 SQLhelper
> to done the execution portion.
> Thanks
>
>

Sampling data set via Integration Services data flow for data mining models without saving train

Hi, all here,

Thank you very much for your kind attention.

I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Does the Data Mining Model Training destination offer any solution?

"The Data Mining Model Training destination trains data mining models by passing the data that the destination receives through the data mining model algorithms. Multiple data mining models can be trained by one destination if the models are built on the same data mining structure."

Data Mining Model Training Destination
(http://msdn2.microsoft.com/en-us/library/6bc8cbe2-46af-4f7b-93d6-86779313c9d7.aspx)

|||

Hi, Darren,

Thank you very much for your very helpful guidance.

So it means that If I want to feed data into my mining models for different mining structures, I will have to create different data flows for that goal?

Thank you very much in advance for your further guidance and advices.

With best regards,

Yours sincerely,

|||That is the way I read the documentation. Why not give it a try?|||

Hiya, thank you very much for your guidance. Have got it done.

With best regards,

Yours sincerely,

Sampling data set via Integration Services data flow for data mining models without saving t

Hi, all here,

Thank you very much for your kind attention.

I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Does the Data Mining Model Training destination offer any solution?

"The Data Mining Model Training destination trains data mining models by passing the data that the destination receives through the data mining model algorithms. Multiple data mining models can be trained by one destination if the models are built on the same data mining structure."

Data Mining Model Training Destination
(http://msdn2.microsoft.com/en-us/library/6bc8cbe2-46af-4f7b-93d6-86779313c9d7.aspx)

|||

Hi, Darren,

Thank you very much for your very helpful guidance.

So it means that If I want to feed data into my mining models for different mining structures, I will have to create different data flows for that goal?

Thank you very much in advance for your further guidance and advices.

With best regards,

Yours sincerely,

|||That is the way I read the documentation. Why not give it a try?|||

Hiya, thank you very much for your guidance. Have got it done.

With best regards,

Yours sincerely,

Samples for Calendar Style Reports

newbee! question.
Want to see sample reports that display
the data in calendar format like outlook does.
Example ... all appointments for the next 2 weeks arranged
as a calendar.
Thank you for your help in advance.It depends on which Outlook view. Month? Week? Day? You probably could
get the day view or business week view working using tables as well as a
dataset that included all days and hours, and outer join your appointments
so you could display the entire range of data and leave the non-appointments
blank.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:EAEA54D3-82E2-44B4-8AB0-4FEC73A79266@.microsoft.com...
> newbee! question.
> Want to see sample reports that display
> the data in calendar format like outlook does.
> Example ... all appointments for the next 2 weeks arranged
> as a calendar.
> Thank you for your help in advance.
>

Sample Word docs and workbooks for DW Design

At my old job, I found a set of Word templates and Excel workbooks that helped in data warehouse specification, design and build.

Unfortunately I lost the link in my move to a new job & wondered if anyone else had it off the top of their head... ?

I believe they were all linked to a MSFT white paper but can't seem to find it

Microsoft Office templates are here:

http://office.microsoft.com/en-us/templates/FX100595491033.aspx

More here:

http://www.microsoft.com/canada/smallbiz/managingbizfundamentals/resources.mspx

More info on DW process here:

http://msdn2.microsoft.com/en-us/library/ms345127.aspx

And this one is an oldie but a goodie:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx

The latest BI info is here:

http://www.microsoft.com/sql/solutions/bi/default.mspx

Sample Trouble

I am trying to get the "PrinterDeliverySample" Extension sample to work. I
assume that the result of compiling then deploying (following all of the
deployment steps) will be another option titled "Printer Delivery Sample" in
the subscription page.
I compiled the solution and copied the .dll to the appropriate directories
then made the updates to the respective files:
RSReportServer.config
---
<Delivery>
<Extension Name="Printer Delivery Sample"
Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample">
<Configuration>
<Printers>
<Printer>\\MED-FP-001\MED-PR-LIC01</Printer>
<Printer>\\MED-FP-001\MED-PR-DLP01</Printer>
</Printers>
</Configuration>
</Extension>
---
RSWebApplication.config
--
...
<Extensions>
<DeliveryUI>
<Extension Name="Printer Delivery Sample"
Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
--
rsmgrpolicy
--
...
version="1"
PermissionSetName="CodeGenDirPermissionSet">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="PrinterDeliveryCodeGroup"
Description="Code group for my delivery extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll"
/>
</CodeGroup>
--
rssrvrpolicy.config
--
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="PrinterDeliveryCodeGroup"
Description="Code group for my delivery extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll"
/>
</CodeGroup>
---Can anyone help here?
"DaveM" wrote:
> I am trying to get the "PrinterDeliverySample" Extension sample to work. I
> assume that the result of compiling then deploying (following all of the
> deployment steps) will be another option titled "Printer Delivery Sample" in
> the subscription page.
> I compiled the solution and copied the .dll to the appropriate directories
> then made the updates to the respective files:
> RSReportServer.config
> ---
> <Delivery>
> <Extension Name="Printer Delivery Sample"
> Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample">
> <Configuration>
> <Printers>
> <Printer>\\MED-FP-001\MED-PR-LIC01</Printer>
> <Printer>\\MED-FP-001\MED-PR-DLP01</Printer>
> </Printers>
> </Configuration>
> </Extension>
> ---
>
> RSWebApplication.config
> --
> ...
> <Extensions>
> <DeliveryUI>
> <Extension Name="Printer Delivery Sample"
> Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
> --
> rsmgrpolicy
> --
> ...
> version="1"
> PermissionSetName="CodeGenDirPermissionSet">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="$CodeGen$/*"
> />
> </CodeGroup>
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="PrinterDeliveryCodeGroup"
> Description="Code group for my delivery extension">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin\Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll"
> />
> </CodeGroup>
> --
> rssrvrpolicy.config
> --
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="$CodeGen$/*"
> />
> </CodeGroup>
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="PrinterDeliveryCodeGroup"
> Description="Code group for my delivery extension">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin\Microsoft.Samples.ReportingServices.PrinterDeliverySample.dll"
> />
> </CodeGroup>
> ---
>
>

Sample Security Extension and WinForms Application

I'm doing a proof of concept on Security Extensions using the Sample Security
Extension. I've implemented the Sample Security Extension without any
problems. It's worked successfully with our WinForms application, which
manages Reporting Services using the Web Services.
However, the WinForms application uses a browser control and URL Access to
display reports to the user (We do this so the user can have the drill down
capabilities in the reports). Everytime a request is made using URL Access,
the login page is displayed. Is there a way to persist the cookie so the
login page isn't displayed when we use URL Access?Please see the thread below. In a nutshell, you need to find a way to pass
the authentication cookie to the WebBrowser control.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=Oxge94CqEHA.868%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3Dcookie%2Bwebbrowser%2Bgroup:*.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3DOxge94CqEHA.868%2540TK2MSFTNGP10.phx.gbl%26rnum%3D1
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"marcca" <marcca@.discussions.microsoft.com> wrote in message
news:785ECC07-D422-4F5F-8AF4-D41EBA0CB41E@.microsoft.com...
> I'm doing a proof of concept on Security Extensions using the Sample
Security
> Extension. I've implemented the Sample Security Extension without any
> problems. It's worked successfully with our WinForms application, which
> manages Reporting Services using the Web Services.
> However, the WinForms application uses a browser control and URL Access to
> display reports to the user (We do this so the user can have the drill
down
> capabilities in the reports). Everytime a request is made using URL
Access,
> the login page is displayed. Is there a way to persist the cookie so the
> login page isn't displayed when we use URL Access?

Sample RSExecutionLog Error

I have been using the reportserver function of sql 2005 for some time now and I decided to try the rsexecutionlog sample. I went through the installation and everything seemed to go fine until I executed the job. The job failed so I manually ran the dts package with configuration file and found that the error is from the parameters column query and reads like this:

"[Derived Column [979]] Error: The "component "Derived Column" (979)"

failed because truncation occurred, and the truncation row disposition

on "output column "ParametersStr" (999)" specifies failure on

truncation. A truncation error occurred on the specified object of the

specified component. "

"Task Update Parameters failed"

I read the query and it reads as such:

SELECT ExecutionLogID, Parameters

FROM ExecutionLogs WITH (NOLOCK)

WHERE Parameters IS NOT NULL AND

Datalength(Parameters) > 0

I checked both the source and destination columns and they have the same character type settings of ntext and same lengths. So I don't understand why it thinks there is a truncation occurring. Please help.

I ran into the same problem. The "Update Parameters" data flow in the SSIS package has a script step that reads the parameters and parses them into name/value pairs. The script task variable structure can't handle the entire amount of data being passed so it is throwing an error. I got around this by changing the "derived column" step just before the "script component" step in the "update parameters" data flow. In there, the variable ParametersStr is defined as "(DT_WSTR,4000)Parameters". I changed it to "(DT_WSTR,2000)Parameters". I also set the error output to ignore truncation errors. This may not be the best way to resolve this, but it worked for me and it only took a second.|||

This worked for me, thanks for sharing. I just saved the old dstx in case this were to cause any problems in the future.

Sample RSExecutionLog Error

I have been using the reportserver function of sql 2005 for some time now and I decided to try the rsexecutionlog sample. I went through the installation and everything seemed to go fine until I executed the job. The job failed so I manually ran the dts package with configuration file and found that the error is from the parameters column query and reads like this:

"[Derived Column [979]] Error: The "component "Derived Column" (979)"

failed because truncation occurred, and the truncation row disposition

on "output column "ParametersStr" (999)" specifies failure on

truncation. A truncation error occurred on the specified object of the

specified component. "

"Task Update Parameters failed"

I read the query and it reads as such:

SELECT ExecutionLogID, Parameters

FROM ExecutionLogs WITH (NOLOCK)

WHERE Parameters IS NOT NULL AND

Datalength(Parameters) > 0

I checked both the source and destination columns and they have the same character type settings of ntext and same lengths. So I don't understand why it thinks there is a truncation occurring. Please help.

I ran into the same problem. The "Update Parameters" data flow in the SSIS package has a script step that reads the parameters and parses them into name/value pairs. The script task variable structure can't handle the entire amount of data being passed so it is throwing an error. I got around this by changing the "derived column" step just before the "script component" step in the "update parameters" data flow. In there, the variable ParametersStr is defined as "(DT_WSTR,4000)Parameters". I changed it to "(DT_WSTR,2000)Parameters". I also set the error output to ignore truncation errors. This may not be the best way to resolve this, but it worked for me and it only took a second.|||

This worked for me, thanks for sharing. I just saved the old dstx in case this were to cause any problems in the future.

Sample RSExecutionLog Error

I have been using the reportserver function of sql 2005 for some time now and I decided to try the rsexecutionlog sample. I went through the installation and everything seemed to go fine until I executed the job. The job failed so I manually ran the dts package with configuration file and found that the error is from the parameters column query and reads like this:

"[Derived Column [979]] Error: The "component "Derived Column" (979)"

failed because truncation occurred, and the truncation row disposition

on "output column "ParametersStr" (999)" specifies failure on

truncation. A truncation error occurred on the specified object of the

specified component. "

"Task Update Parameters failed"

I read the query and it reads as such:

SELECT ExecutionLogID, Parameters

FROM ExecutionLogs WITH (NOLOCK)

WHERE Parameters IS NOT NULL AND

Datalength(Parameters) > 0

I checked both the source and destination columns and they have the same character type settings of ntext and same lengths. So I don't understand why it thinks there is a truncation occurring. Please help.

I ran into the same problem. The "Update Parameters" data flow in the SSIS package has a script step that reads the parameters and parses them into name/value pairs. The script task variable structure can't handle the entire amount of data being passed so it is throwing an error. I got around this by changing the "derived column" step just before the "script component" step in the "update parameters" data flow. In there, the variable ParametersStr is defined as "(DT_WSTR,4000)Parameters". I changed it to "(DT_WSTR,2000)Parameters". I also set the error output to ignore truncation errors. This may not be the best way to resolve this, but it worked for me and it only took a second.|||

This worked for me, thanks for sharing. I just saved the old dstx in case this were to cause any problems in the future.

Sample rows by criteria

This might be simple, but no matter how I try, I cannot get it working.

I want to select a sample, say 3 rows, of a table: SELECT TOP 3 * FROM table - that was easy.

Now say this table has a criteria column, an integer, with around 50 unique values. The whole table has 3 mill rows, so each criteria is seen between "a lot" and maybe only once for some others - but that should not be the issue here.

I'd like to get 3 sample rows FOR EACH value in the criteria column - or only one or two if only so many matches exist.

How could this be done? The table does have a unique primary key, int, if this is needed for the operation. Any ideas?

To select random samples take advantage of (1) ROW_NUMBER and (2) NEWID() function; for example:

declare @.example table
( rid integer,
criteria tinyint,
sampleValue smallint,
filler char(200)
)

insert into @.example
select iter,
1 + 49.9999 * dbo.rand(),
1200 * dbo.rand(),
'Record # ' + convert(varchar(6), iter)
from small_iterator (nolock)

select criteria,
rid,
seq,
sampleValue
from ( select criteria,
rid,
row_number ()
over ( partition by criteria
order by newid()
)
as seq,
sampleValue
from @.example
) x
where seq <= 3
order by criteria, seq

-- criteria rid seq sampleValue
-- -- -- -- --
-- 1 2242 1 197
-- 1 10438 2 204
-- 1 9782 3 881
-- ...
-- 50 15673 2 455
-- 50 5825 3 482

The SMALL_ITERATOR and DBO.RAND() functions used to create the sample data can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

Sample reports error

How come i get this:
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Query execution failed for data set 'SalesOrder'.
(rsErrorExecutingCommand) Get Online Help
a.. Must declare the variable '@.SalesOrderNumber'.
----
--
when trying to run sales order detail ?
Don't have visual studio on my machine but that shoudln't be necessary to
run reports in report manager ?Have you added the right DataSource in report manager?
The samples comes without the Data Source, so you have to add that manually.
Kaisa M. Lindahl
"Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
news:uBBGi0X1EHA.3708@.TK2MSFTNGP14.phx.gbl...
> How come i get this:
> a.. An error has occurred during report processing.
(rsProcessingAborted)
> Get Online Help
> a.. Query execution failed for data set 'SalesOrder'.
> (rsErrorExecutingCommand) Get Online Help
> a.. Must declare the variable '@.SalesOrderNumber'.
> ----
--
> --
>
> when trying to run sales order detail ?
> Don't have visual studio on my machine but that shoudln't be necessary to
> run reports in report manager ?
>

Sample Report Deployment

Hi all,

I tried to deploy sample report to http://myserver/reportserver and I got the following error, can someone provide some tips?

TITLE: Microsoft Report Designer

A connection could not be made to the report server http://lasc06/reportserver.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00" target="_blank">Get Online Help</a></li><ul>
<li>Object reference not set to an instance of an object.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.Designer)

BUTTONS:

OK

Have you configured your Report Server?
Make sure that everything is up and running by configuring the server:
Start --> All programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration|||This sounds like a credential issue. Go to the Data Source folder. Click on the data souce. Connect Using: the default is "Credentials supplied by the user running the report". Try other options like "Credentials stored securely in the report server" to see if that'll work.|||

Hi,

first thing for me would be to install the latest service pack first (You are running on the RTM version whereas the version 9.0.00.2047 is the most current.

Is the report server available through the navigation in Internet Explorer on the appropiate site http://localhost/reportserver ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Sample Report Deployment

Hi all,

I tried to deploy sample report to http://myserver/reportserver and I got the following error, can someone provide some tips?

TITLE: Microsoft Report Designer

A connection could not be made to the report server http://lasc06/reportserver.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00" target="_blank">Get Online Help</a></li><ul>
<li>Object reference not set to an instance of an object.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.Designer)

BUTTONS:

OK

Have you configured your Report Server?
Make sure that everything is up and running by configuring the server:
Start --> All programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration|||This sounds like a credential issue. Go to the Data Source folder. Click on the data souce. Connect Using: the default is "Credentials supplied by the user running the report". Try other options like "Credentials stored securely in the report server" to see if that'll work.|||

Hi,

first thing for me would be to install the latest service pack first (You are running on the RTM version whereas the version 9.0.00.2047 is the most current.

Is the report server available through the navigation in Internet Explorer on the appropiate site http://localhost/reportserver ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Sample Report Delivery Problem

I have done everything in the Readme.htm to setup the delivery extension.
When I go to New Subscrition for a report to try to test I get 'Object
Reference not set to and instance of on object', that displays on the page.
Thanks in advance.
here is a bit of the log file.
w3wp!library!114c!10/8/2004-13:40:36:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, Could not create Extension of type: DeliveryUIname: Printer
Delivery Sample;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
w3wp!extensionfactory!114c!10/8/2004-13:40:47:: e ERROR: Exception caught
instantiating report server extension:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files.
w3wp!ui!114c!10/8/2004-13:40:47:: e ERROR: Object reference not set to an
instance of an object.
w3wp!ui!114c!10/8/2004-13:40:47:: e ERROR: HTTP status code --> 500
--Details--
System.NullReferenceException: Object reference not set to an instance of an
object.
at
Microsoft.ReportingServices.UI.SubscriptionPropertiesPage.AddDropDownOfDeliveryProviders()
at
Microsoft.ReportingServices.UI.SubscriptionPropertiesPage.FinalizeControls()Could not create Extension of type: DeliveryUIname: Printer
Delivery Sample
Make sure that the assembly name is correct and located in the bin directory
of the reportmanager.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"KENWOOD" <KENWOOD@.discussions.microsoft.com> wrote in message
news:F51164DD-2F1A-4D04-8823-4ABFBBDD83C9@.microsoft.com...
> I have done everything in the Readme.htm to setup the delivery extension.
> When I go to New Subscrition for a report to try to test I get 'Object
> Reference not set to and instance of on object', that displays on the
page.
> Thanks in advance.
> here is a bit of the log file.
> w3wp!library!114c!10/8/2004-13:40:36:: e ERROR: Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
ception:
> The Report Server has encountered a configuration error; more details in
the
> log files, Could not create Extension of type: DeliveryUIname: Printer
> Delivery Sample;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
ception:
> The Report Server has encountered a configuration error; more details in
the
> log files
> w3wp!extensionfactory!114c!10/8/2004-13:40:47:: e ERROR: Exception caught
> instantiating report server extension:
>
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorEx
ception:
> The Report Server has encountered a configuration error; more details in
the
> log files.
> w3wp!ui!114c!10/8/2004-13:40:47:: e ERROR: Object reference not set to an
> instance of an object.
> w3wp!ui!114c!10/8/2004-13:40:47:: e ERROR: HTTP status code --> 500
> --Details--
> System.NullReferenceException: Object reference not set to an instance of
an
> object.
> at
>
Microsoft.ReportingServices.UI.SubscriptionPropertiesPage.AddDropDownOfDeliv
eryProviders()
> at
>
Microsoft.ReportingServices.UI.SubscriptionPropertiesPage.FinalizeControls()

Sample problem : Function Replace() in Derived Column

Hello All.

Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22

I used ==> replace(strDate, "/", "") ==> 20060322

But If strTest = "123.10" ====>> 123.10

How can i do to replace ( " )double qoute ?
by function replace()

what is a statement for replace (") in Derived Column ?

please tell me for this event.

any suggesstion appreciated
Thank you very much.

Chonnathan

Not sure without testing, but try escaping it.

replace(strTest,"\"","")|||Note: Please try not to delete your posts. I had a reply submitted to your deleted post.

Never-the-less, this does work:

In my test, column test01 was populated with the text string,

TES"T. After running through my derived column with the below

expression, the result was TEST (without the double quote).

REPLACE(test01,"\"","")

What version of SSIS are you using? (SP1? SP2? initial release?)

Sample Printer Delivery Extension Question

I'm trying to use the printer delivery extension sample that comes with SQL Server Reporting Services. The problem that I am having is that when I am going through to set up the subscription using a printer the printers that I try to select come back with an error that states that the printers are not installed. I have double checked the config file and they are indeed added to the configuration as specified and I have double checked to make sure that the printers are added to the server. In fact one printer is the default for the report server that we are using. Can anyone please assist me or point me in the right direction?
TIA,
MelanieHi Melanie,
Just take note that the code that checks if the printer is installed is case
sensitive. write a simple program using the code the example uses to
compare the installed printer, and check for sure what it is looking for...
"Melanie" <Melanie@.discussions.microsoft.com> wrote in message
news:BD4829E0-2BAE-4710-8617-BDD3DD08D21A@.microsoft.com...
> I'm trying to use the printer delivery extension sample that comes with
SQL Server Reporting Services. The problem that I am having is that when I
am going through to set up the subscription using a printer the printers
that I try to select come back with an error that states that the printers
are not installed. I have double checked the config file and they are indeed
added to the configuration as specified and I have double checked to make
sure that the printers are added to the server. In fact one printer is the
default for the report server that we are using. Can anyone please assist me
or point me in the right direction?
> TIA,
> Melanie|||I have this same problem. What I had to do is log onto the Report Server and launch the Report Manager desktop from there. I could then set up subscriptions to the printers, and they do print out correctly.
"Melanie" wrote:
> I'm trying to use the printer delivery extension sample that comes with SQL Server Reporting Services. The problem that I am having is that when I am going through to set up the subscription using a printer the printers that I try to select come back with an error that states that the printers are not installed. I have double checked the config file and they are indeed added to the configuration as specified and I have double checked to make sure that the printers are added to the server. In fact one printer is the default for the report server that we are using. Can anyone please assist me or point me in the right direction?
> TIA,
> Melanie

Sample Printer Delivery Extension

I am trying to test the Sample Printer Delivery EXtension. I have built the
assembly and moved it into the RM and RS bin folders.
My RSReportServer.config file addition looks like this:
<Extension Name="Printer Delivery Sample"
Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"
Visible="True">
<Configuration>
<Printers>
<Printer>\\BUSINESS01\KILLINGTON</Printer>
</Printers>
</Configuration>
</Extension>
My RSWebApplication.config file entry looks like this:
<Extension Name="Printer Delivery Sample"
Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryUIProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
I have restarted the ReportServer service. When I go to report and create a
new subscription, I still see only the Email and FileShare delivery options.
What is missing?
Steve BurtHi,
did you have any luck resolving this? Having the same problem. Seem to
have the correct config settings and security settings but no printer option
displays.
AJ
"Steve Burt" wrote:
> I am trying to test the Sample Printer Delivery EXtension. I have built the
> assembly and moved it into the RM and RS bin folders.
> My RSReportServer.config file addition looks like this:
> <Extension Name="Printer Delivery Sample"
> Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"
> Visible="True">
> <Configuration>
> <Printers>
> <Printer>\\BUSINESS01\KILLINGTON</Printer>
> </Printers>
> </Configuration>
> </Extension>
> My RSWebApplication.config file entry looks like this:
> <Extension Name="Printer Delivery Sample"
> Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryUIProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
> I have restarted the ReportServer service. When I go to report and create a
> new subscription, I still see only the Email and FileShare delivery options.
> What is missing?
> Steve Burt|||I did get the printer to display by rechecking the various config settings.
I now get an exception when I try to create a subscription to the report. It
says that the printer is not available. I thought this might mean the
service account under which the ReportServer is running didn't have the
specific printer configured (this is a network printer, not directly
attached). I checked this and the printer is configured for the service
account.
So no joy thus far.
"AJ" wrote:
> Hi,
> did you have any luck resolving this? Having the same problem. Seem to
> have the correct config settings and security settings but no printer option
> displays.
> AJ
>
> "Steve Burt" wrote:
> > I am trying to test the Sample Printer Delivery EXtension. I have built the
> > assembly and moved it into the RM and RS bin folders.
> >
> > My RSReportServer.config file addition looks like this:
> > <Extension Name="Printer Delivery Sample"
> > Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"
> > Visible="True">
> > <Configuration>
> > <Printers>
> > <Printer>\\BUSINESS01\KILLINGTON</Printer>
> > </Printers>
> > </Configuration>
> > </Extension>
> >
> > My RSWebApplication.config file entry looks like this:
> > <Extension Name="Printer Delivery Sample"
> > Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryUIProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
> >
> > I have restarted the ReportServer service. When I go to report and create a
> > new subscription, I still see only the Email and FileShare delivery options.
> >
> > What is missing?
> > Steve Burt|||Hi Steve,
I think you have to change the service account to an account that's part of
the domain, so that it is possible to see and interact with the other
printers. I also have the same problem. If I was you, I'd try to maken a
"reporting" user and let this user be the reporting service account (be
careful with permissions!)
Hope it helps. Otherwise, there are several other topics concerning this
subject somwhere in the newgroup.
Kind regards, Koen
"Steve Burt" wrote:
> I did get the printer to display by rechecking the various config settings.
> I now get an exception when I try to create a subscription to the report. It
> says that the printer is not available. I thought this might mean the
> service account under which the ReportServer is running didn't have the
> specific printer configured (this is a network printer, not directly
> attached). I checked this and the printer is configured for the service
> account.
> So no joy thus far.
> "AJ" wrote:
> > Hi,
> > did you have any luck resolving this? Having the same problem. Seem to
> > have the correct config settings and security settings but no printer option
> > displays.
> > AJ
> >
> >
> > "Steve Burt" wrote:
> >
> > > I am trying to test the Sample Printer Delivery EXtension. I have built the
> > > assembly and moved it into the RM and RS bin folders.
> > >
> > > My RSReportServer.config file addition looks like this:
> > > <Extension Name="Printer Delivery Sample"
> > > Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"
> > > Visible="True">
> > > <Configuration>
> > > <Printers>
> > > <Printer>\\BUSINESS01\KILLINGTON</Printer>
> > > </Printers>
> > > </Configuration>
> > > </Extension>
> > >
> > > My RSWebApplication.config file entry looks like this:
> > > <Extension Name="Printer Delivery Sample"
> > > Type="Microsoft.Samples.ReportingServices.PrinterDeliverySample.PrinterDeliveryUIProvider,Microsoft.Samples.ReportingServices.PrinterDeliverySample"/>
> > >
> > > I have restarted the ReportServer service. When I go to report and create a
> > > new subscription, I still see only the Email and FileShare delivery options.
> > >
> > > What is missing?
> > > Steve Burt

Sample output

hi, i want to know how to do that:

table: costtypes

fields : typecode

values: amount, vat, duty,

table bills:

fields: billid, costtype, amount

value

bi1 amount 1000

bi1 vat 10

bi2 amount 200

i need to create a view that will display amount, vat, duty as fields. note that these are values in the table.

so that i will have

billid amount vat duty

bi1 1000 10 0

bi2 200 0 0

thanks

-- Create #Bills table and values

Create Table #CostTypes (TypeCode varchar(10))

Go

Insert Into #CostTypes

Select 'Amount'

Union

Select 'Vat'

Union

Select 'Duty'

Go

-- Create #Bills table and values

Create Table #Bills (BillId int, CostType varchar(10), value int)

Go

Insert Into #Bills

Select 1, 'Amount', 1000

Union

Select 1, 'Vat', 10

Union

Select 2, 'Amount', 200

Go

-- Create view (you could use this code in a table function)

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select TypeCode From #CostTypes

Set @.stmt = 'Select Distinct BillId'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From #Bills'

Exec(@.stmt)

I hope it's useful

Giulio

|||Thats too good . can you explain whats happenin in the code though . only the fetch etc part|||

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @.stmt = 'Select Distinct BillId'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From Bill'

Exec(@.stmt)

|||

billid amount vat duty

699190BD-1410-47A8-AFAD-05EC38FEDBF8 0.00 50.00 0.00
B9B71063-D2AD-4735-8CC0-106335990A93 1.00 0.00 0.00
48A82C3C-CF64-4202-B6FA-5DD5A3D32D3C 0.00 0.00 210.00

|||

Sorry, I forgot table function definition requires you know returned table structure.

You could create a scalar function to get SQL Select statement and use this to get data...

Bye,

Giulio

|||

Declare @.fieldname varchar(10) -- variable used to get field value from AmountTypes table and use it as field name in your view

Declare @.stmt varchar(4000) -- variable that contains sql select statement to get data

Declare Fields Cursor For Select Amounttype From Amounttypes -- cursor to scan AmountTypes records and get view fields list

Set @.stmt = 'Select Distinct BillId' -- initialize @.stmt.

Open Fields -- Open Fields cursor

Fetch Next From Fields Into @.fieldname -- get first record

While @.@.Fetch_Status = 0 Begin -- while reading AmountTypes...

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname -- ...add field to select fields list

Fetch Next From Fields Into @.fieldname -- read next record

End

Close Fields -- close Fields cursor

Deallocate Fields -- remove Fields cursor

Set @.stmt = @.stmt + ' From Bill' -- add FROM clause to select statement

Exec(@.stmt) -- execute select

At the end of code below, your select statement should be something like

Select Distinct BillId, amountfc * Case When AmountType = 'Amount' Then 1 Else 0 End As Amount, amountfc * Case When AmountType = 'Vat' Then 1 Else 0 End As Vat, amountfc * Case When AmountType = 'Duty' Then 1 Else 0 End As Duty From Bill

Simply I read AmountTypes to get fields list. Since you have AmountType in Bill as value, I multiply amountfc by 1 if AmountType is equal to current cursor's AmountType or by 0 else.

Ok?

|||

ca101 AD2574DB Estimated 0.00 0.00 2100.00
ca101 AD2574DB Estimated 3000.00 0.00 0.00
ca101 7BB1C98B Cheque 0.00 1.00 0.00
ca101 18ACADF5 Cheque 0.00 0.00 500.00
ca101 18ACADF5 Cheque 0.00 1100.00 0.00
nmh500 AD2574DB Estimated 0.00 0.00 10.00
nmh500 AD2574DB Estimated 2000.00 0.00 0.00

okay if u see the rows., those in same colour should have been on the same row, so

ca101 AD2574DB Estimated 3000.00 0.00 2100.00

code i used follows;

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @.stmt = 'Select pono, myid, billtype'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From multiplebillsviewall'

Exec(@.stmt)

|||

sql never got so interesting in my life.

i know mere basics u know thanks millions.

|||

Sorry, I forgot it...

Set @.stmt = @.stmt + ', Sum(Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname

and

Set @.stmt = @.stmt + ' From #Bills Group By BillId '

it should be ok...

|||

ca101 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 3000.00 0.00 2100.00
ca101 7BB1C98B-923B-471D-AC86-9F331671F789 Cheque 0.00 1.00 0.00
ca101 18ACADF5-E7D0-4B97-B79D-E266F07C804D Cheque 0.00 1100.00 500.00
nmh500 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 2000.00 0.00 10.00

lovely bra.

thanks.

by the way how do i create a view out of this now?

i will simply use the view in .net and display .

|||

okay no problemo i created a stored procedure.

views cannot contain declare, set etc..

but if the possibility that i can create a view out of this exists . let me know.

shot bra SQL ROCKS

|||

I don't think this possibility exists...

|||so whats the next proposal .. i only need to be able to bring the data to my application in .net and display it in a datagridview.|||

you could simply execute a select statement got by calling a scalar function that returns it...

Create Function dbo.xGetAmountSelectStmt()
Returns Varchar(4000)
As Begin
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select AmountType From AmountTypes

Set @.stmt = 'Select BillId'

Open Fields
Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', Sum(Value * Case When AmountType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End

Close Fields
Deallocate Fields

Set @.stmt = @.stmt + ' From Bills Group By BillId '
Return(@.stmt)
End

When you have to get data, get below function return value and use it as select statement...

If you need to inject fields, join, where etc you could may simply modify last Set @.stmt as

Set @.stmt = @.stmt + '/*Select*/ From Bills /*Joins*/ /*Where*/ Group By BillId /*Group*/ /*Order*/'

and replace these optional pseudo-tags as your needs

/*Select*/ -> Other fields to get using joins or as static values beginning with ","

/*Joins*/ -> Table joins

/*Where*/ -> Where clause

/*Group*/ -> Other fields used in Group By beginning with ","

/*Order*/ -> Order By clause

Since they are delimited by /*..*/ if you don't need to use tags below, you may leave they as are

I think this could be better (more readable, simple and clean) than execute all every time... And more, if you have to change anything in function code, you only have to modify server-side function...

Sample on how to import Flat File with vertical structure

Hello,

I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

Anybody got an idea on where to find this kind of samples.

Thx.

? Harry, I think the inconsistency in which columns are present or not in each row is going to cause you problems in SSIS. If I understand your structure correctly you can't use the Flat File Connection manager for such data. I think you'll need to write some custom code. Without knowing the structure of your data I don't think I can be more specific. Andrew Watt [MVP] <Harry_Leboeuf@.discussions.microsoft.com> wrote in message news:22ebb616-102a-46db-b372-9c21df0d6f3f@.discussions.microsoft.com... Hello, I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block. Anybody got an idea on where to find this kind of samples. Thx.|||

Harry_Leboeuf wrote:

Hello,

I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

Anybody got an idea on where to find this kind of samples.

Thx.

I personally would import each row as simply a single column of type DT_STR/DT_WSTR and then parse out the required columns within the data-flow - probably using a script component.

You could make things slightly easier for yourself by importing the data as two columns: the first contains the indicator and the second contians the rest of it.

Alternatively you could combine those two steps into one and write a source component that imported the data and parsed it on the way. I wouldn't do that though cos there's no point in creating work for yourself - let SSIS import the data and you are then left with the relatively easy job of parsing out the columns.

-Jamie