I want to create a table with the output of select statement. i.e. create
table tmpk as select *...
this gives an error
Incorrect syntax near the keyword 'as'.
when i am running query alone this gives the result fine.try
SELECT
field1, field 2 etc...
INTO tmpk
FROM
wherever
"Karn Tanwar" <karntanwer@.hotmail.com> wrote in message
news:OtJceVhVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>|||On Wed, 11 May 2005 16:21:36 +0530, Karn Tanwar wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
You have to use EXEC, like this:
DECLARE @.var VARCHAR(8000)
SELECT @.var = 'CREATE TABLE ' + something
FROM somewhere
WHERE somecol = @.somevar
EXEC (@.var)
This is a crude example, but you get the point.
/Andrs Taylor|||Hi Karan
Try this way
SELECT * INTO tmpk FROM ...
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Unfortunately in the SQL Server enviornment you cannot select into a table
like you can in other legacy enviornments like Foxpro but you can declare a
local temp table with the same field makeup as the select statement outputs
and then use and insert to get the data into a table. You might have to mes
s
with it for a little while before you get it right, but it is possible to ge
t
somewhat the same results.
Declare @.v_sql NVARCHAR(4000)
SELECT @.v_sql =
INSERT #test
select hard_id,
Type_id,
description,
speed,
amount,
cache,
brand
from TU_Hardware
EXEC sp_executesql @.v_sql
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Correction.. you can do a select into statement but you cant do it the way
you were trying it.
If you go to masters table on any db and type the following:
select * from abc
It will give you an error because no table exist then type
select top 10 *
into abc
from sysobjects
You will have a new table in master db. You can do this with any table in
the from part of the statement.
Hope this helps..
"Karn Tanwar" wrote:
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>
Showing posts with label output. Show all posts
Showing posts with label output. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Create Procedure Output ntext
Hello,
I need to produce with T-SQL a user defined function or stored
procedure that make one SLQ-Statement and prepare as string from the
result set.
The request muss be able to return a very long unicode string. The
return value nvarchar is being truncated so I'm trying to create a
stored procedure, that returns a ntext string.
I can't manage it (I am no T-SQL specialist). Maybe someone can help
me?
Thanks for your help.
--
Here is my sp:
alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
@.return ntext output)
AS
BEGIN
DECLARE @.onelevel nvarchar(4000)
DECLARE @.pos varchar(1000)
DECLARE @.leveldone varchar(100)
DECLARE @.levelplaned varchar(100)
DECLARE @.planeddate nvarchar(4000)
DECLARE @.elementid varchar(10)
DECLARE @.levelid varchar(10)
DECLARE @.levelstatus varchar(10)
DECLARE @.levelupd nvarchar(4000)
DECLARE @.levelauthor varchar(10)
DECLARE @.prevelementid varchar(10)
BEGIN
declare level_cursor CURSOR FOR
SELECT
B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
convert(varchar,A.Upd,126) as Upd,A.Author
FROM T_TABLE1 as B left outer join T_TABLE2 as A on
(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
where B.ParentID=@.parentid
ORDER BY B.ElementID,B.ElementPos
END
set @.onelevel=''
OPEN level_cursor
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
@.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.prevelementid=@.elementid
if (@.pos IS NULL)
set @.onelevel=''
else
set @.onelevel=@.pos
if (@.elementid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.elementid
if (@.levelid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelid
if (@.leveldone IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.leveldone
if (@.levelplaned IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelplaned
if (@.planeddate IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.planeddate
if (@.levelstatus IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelstatus
if (@.levelupd IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelupd
if (@.levelauthor IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelauthor
-- Part Output
print @.onelevel
if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor
if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor
END
CLOSE level_cursor
DEALLOCATE level_cursor
RETURN
END
--
Call of the function with:
exec dbo.F_FUNCTION 550,1632, 0, ''
Here the beginning of the query analyser output:
1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.
--I think you cannot return that using an output parameter - you'll have to
return it as a field in a SELECT.
On 24 Oct 2005 08:17:01 -0700, rey@.infoman.de wrote:
>Hello,
>I need to produce with T-SQL a user defined function or stored
>procedure that make one SLQ-Statement and prepare as string from the
>result set.
>The request muss be able to return a very long unicode string. The
>return value nvarchar is being truncated so I'm trying to create a
>stored procedure, that returns a ntext string.
>I can't manage it (I am no T-SQL specialist). Maybe someone can help
>me?
>Thanks for your help.
>--
>Here is my sp:
>alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
>@.return ntext output)
>AS
>BEGIN
> DECLARE @.onelevel nvarchar(4000)
> DECLARE @.pos varchar(1000)
> DECLARE @.leveldone varchar(100)
> DECLARE @.levelplaned varchar(100)
> DECLARE @.planeddate nvarchar(4000)
> DECLARE @.elementid varchar(10)
> DECLARE @.levelid varchar(10)
> DECLARE @.levelstatus varchar(10)
> DECLARE @.levelupd nvarchar(4000)
> DECLARE @.levelauthor varchar(10)
> DECLARE @.prevelementid varchar(10)
> BEGIN
> declare level_cursor CURSOR FOR
> SELECT
>B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
>convert(varchar,A.Upd,126) as Upd,A.Author
> FROM T_TABLE1 as B left outer join T_TABLE2 as A on
>(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
> where B.ParentID=@.parentid
> ORDER BY B.ElementID,B.ElementPos
> END
> set @.onelevel=''
> OPEN level_cursor
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
> @.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>set @.prevelementid=@.elementid
> if (@.pos IS NULL)
> set @.onelevel=''
> else
> set @.onelevel=@.pos
> if (@.elementid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.elementid
> if (@.levelid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelid
> if (@.leveldone IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.leveldone
> if (@.levelplaned IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelplaned
> if (@.planeddate IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.planeddate
> if (@.levelstatus IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelstatus
> if (@.levelupd IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelupd
> if (@.levelauthor IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelauthor
> -- Part Output
> print @.onelevel
> if (@.return is NULL)
> exec(@.return+@.onelevel)
> else
> exec(@.return+'*;*'+@.onelevel)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> END
> CLOSE level_cursor
> DEALLOCATE level_cursor
> RETURN
>END
>--
>Call of the function with:
>exec dbo.F_FUNCTION 550,1632, 0, ''
>Here the beginning of the query analyser output:
>1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '*'.
>--|||Am 24 Oct 2005 08:17:01 -0700 schrieb rey@.infoman.de:
...
> --
> Call of the function with:
> exec dbo.F_FUNCTION 550,1632, 0, ''
> Here the beginning of the query analyser output:
> 1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '*'.
> --
ntext is a valid datatype for the OUTPUT variable of a stored procedure.
This is not the problem. Your error appears here:
...
-- Part Output
print @.onelevel
if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
...
because if @.return is Null then you do a
exec('1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277')
and what should this be?
EXEC starts another stored proc, from there you get your error message. And
so it is an error in line 1.
bye
Helmut|||Hello Helmut,
thanks for your answer.
With exec(@.return+@.onelevel) I try to fill my @.return variable with
the content of @.onelevel. I can't do it with set @.return=@.onelevel
because @.return is of type ntext.
How can I do it else?
thanks and bye,
Agns.|||Not sure if you can do this with ntext, but try this
select @.return = @.return + '*;*' + @.onelevel|||(rey@.infoman.de) writes:
> thanks for your answer.
> With exec(@.return+@.onelevel) I try to fill my @.return variable with
> the content of @.onelevel. I can't do it with set @.return=@.onelevel
> because @.return is of type ntext.
> How can I do it else?
You can't. Since you are in a dead end, I suggest that you explain
your underlying business problem that you are trying to solve. What
does the calling side of this look like?
I can offer one workaround: move to SQL 2005, which offers the
new datatype nvarchar(MAX), which in difference to ntext is a first
class citizen.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 25 Oct 2005 00:35:55 -0700, rey@.infoman.de wrote:
>Hello Helmut,
>thanks for your answer.
>With exec(@.return+@.onelevel) I try to fill my @.return variable with
>the content of @.onelevel. I can't do it with set @.return=@.onelevel
>because @.return is of type ntext.
>How can I do it else?
>thanks and bye,
>Agns.
What's wrong with returning it via SELECT rather than via a parameter?
Sunday, March 11, 2012
Create output columns based on input in custom component
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||You could try something like this-
IDTSInput90 input = ComponentMetaData.InputCollection[inputID];
This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?
From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.
A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.
|||Thanks,
I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.
Here's a forum entry on it:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17
And Jamie T's explanation with more links:
http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx
I'll see if I can get the custom source component working today.
|||I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html
(The site has a page for downloading the examples).
The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).
Here's some of the key code:
Code Snippet
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);
if (cm.CreationName != "FLATFILE")
{
throw new Exception("The Connection Manager is not a FILE Connection Manager");
}
else
{
_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);
if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)
{
throw new Exception("The type of FILE connection manager must be an Existing File");
}
else
{
_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();
if (_filename == null || _filename.Length == 0)
{
throw new Exception("Nothing returned when grabbing the filename");
}
}
}
}
}
The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"
Code Snippet
private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)
{
if (_filename != null || _filename.Length > 0)
{
TextReader tr = File.OpenText(_filename);
string columns = tr.ReadLine();
tr.Close();
_columnNames = columns.Split(",".ToCharArray());
foreach (string columnName in _columnNames)
{
IDTSOutputColumn90 outName = output.OutputColumnCollection.New();
outName.Name = columnName.Trim();
outName.Description = columnName.Trim();
outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);
//Create an external metadata column to go alongside with it
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);
}
}
}
Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.
Code Snippet
private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)
{
IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);
IDTSExternalMetadataColumn90 mdc;
bool rtnVal = true;
int cCount = 0;
foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
{
if (col.ExternalMetadataColumnID == 0)
{
rtnVal = false;
}
else
{
//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];
mdc = output.ExternalMetadataColumnCollection[cCount];
if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision
|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)
{
rtnVal = false;
}
cCount++;
}
}
return rtnVal;
}
This was the other change I needed to make to the example, the collection index doesn't match the column ID.
I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.
Create output columns based on input in custom component
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||You could try something like this-
IDTSInput90 input = ComponentMetaData.InputCollection[inputID];
This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?
From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.
A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.
|||Thanks,
I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.
Here's a forum entry on it:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17
And Jamie T's explanation with more links:
http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx
I'll see if I can get the custom source component working today.
|||I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html
(The site has a page for downloading the examples).
The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).
Here's some of the key code:
Code Snippet
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);
if (cm.CreationName != "FLATFILE")
{
throw new Exception("The Connection Manager is not a FILE Connection Manager");
}
else
{
_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);
if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)
{
throw new Exception("The type of FILE connection manager must be an Existing File");
}
else
{
_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();
if (_filename == null || _filename.Length == 0)
{
throw new Exception("Nothing returned when grabbing the filename");
}
}
}
}
}
The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"
Code Snippet
private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)
{
if (_filename != null || _filename.Length > 0)
{
TextReader tr = File.OpenText(_filename);
string columns = tr.ReadLine();
tr.Close();
_columnNames = columns.Split(",".ToCharArray());
foreach (string columnName in _columnNames)
{
IDTSOutputColumn90 outName = output.OutputColumnCollection.New();
outName.Name = columnName.Trim();
outName.Description = columnName.Trim();
outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);
//Create an external metadata column to go alongside with it
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);
}
}
}
Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.
Code Snippet
private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)
{
IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);
IDTSExternalMetadataColumn90 mdc;
bool rtnVal = true;
int cCount = 0;
foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
{
if (col.ExternalMetadataColumnID == 0)
{
rtnVal = false;
}
else
{
//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];
mdc = output.ExternalMetadataColumnCollection[cCount];
if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision
|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)
{
rtnVal = false;
}
cCount++;
}
}
return rtnVal;
}
This was the other change I needed to make to the example, the collection index doesn't match the column ID.
I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.
Create output columns based on input in custom component
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||You could try something like this-
IDTSInput90 input = ComponentMetaData.InputCollection[inputID];
This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?
From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.
A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.
|||Thanks,
I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.
Here's a forum entry on it:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17
And Jamie T's explanation with more links:
http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx
I'll see if I can get the custom source component working today.
|||I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html
(The site has a page for downloading the examples).
The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).
Here's some of the key code:
Code Snippet
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);
if (cm.CreationName != "FLATFILE")
{
throw new Exception("The Connection Manager is not a FILE Connection Manager");
}
else
{
_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);
if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)
{
throw new Exception("The type of FILE connection manager must be an Existing File");
}
else
{
_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();
if (_filename == null || _filename.Length == 0)
{
throw new Exception("Nothing returned when grabbing the filename");
}
}
}
}
}
The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"
Code Snippet
private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)
{
if (_filename != null || _filename.Length > 0)
{
TextReader tr = File.OpenText(_filename);
string columns = tr.ReadLine();
tr.Close();
_columnNames = columns.Split(",".ToCharArray());
foreach (string columnName in _columnNames)
{
IDTSOutputColumn90 outName = output.OutputColumnCollection.New();
outName.Name = columnName.Trim();
outName.Description = columnName.Trim();
outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);
//Create an external metadata column to go alongside with it
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);
}
}
}
Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.
Code Snippet
private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)
{
IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);
IDTSExternalMetadataColumn90 mdc;
bool rtnVal = true;
int cCount = 0;
foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
{
if (col.ExternalMetadataColumnID == 0)
{
rtnVal = false;
}
else
{
//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];
mdc = output.ExternalMetadataColumnCollection[cCount];
if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision
|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)
{
rtnVal = false;
}
cCount++;
}
}
return rtnVal;
}
This was the other change I needed to make to the example, the collection index doesn't match the column ID.
I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.
Friday, February 24, 2012
Create Excel Output
I successfully use the code below to output a report to a web page or to a
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>
Subscribe to:
Posts (Atom)