Then I want to fill this table with 15,000,000 records, so that I have
a table with the numbers 0 to 14,999,999.
How can I do this as fast as possible. A standard INSERT would take a
long time.
(It can be a temp table or a table variable. I just need a list with
numbered 0 to 15,000,000)
Thank you.
Gidonhttp://www.bizdatasolutions.com/tsql/tblnumbers.asp
--
David Portas
SQL Server MVP
--|||Thanks a lot.
Hello,
In SQL Server 2000, is it possible to take a table with one field (column), and pivot the table so that the characters in the row data become the field (column) names of another table ( or in a View)? The number of records could vary.
If so, how would I do this?
Sample table;
Create Table dbo.MonthlyData
(
Categories varchar(30) NOT NULL
)
Sample data;
Sales Volume 2005-02
TotRefVol 2005-02
Sales Ratio 2005-02
Sales Volume 2005-03
TotRefVol 2005-03
Sales Ratio 2005-03
Sales Volume 2005-04
TotRefVol 2005-04
Sales Ratio 2005-04
First; Sorry, the subject should have been 'Create table fields from row data'. To answer your question, each row of sample data is contained within a single column called 'Categories'.
|||Assuming you had some other value to use with the columns you reference you could do something like:
Create Table dbo.MonthlyData
(
CategoryID int IDENTITY(1,1)
,Categories varchar(30) NOT NULL
,Value varchar(50)
)
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-02', 'Small')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-02', 'Medium')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-02', 'Large')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-03', 'Extra Large')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-03', 'Small')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-03', 'Medium')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-04', 'Large')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-04', 'Extra Large')
INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-04', 'Small')
DECLARE @.string nvarchar(1000)
SELECT @.string = ISNULL(@.string + ', ', '') + QUOTENAME([Value], '''') + QUOTENAME(Categories)
FROM dbo.MonthlyData
SET @.string = 'SELECT ' + @.string
EXEC sp_executesql @.string
This will give you Categories as your column header with associated Value column.
|||Thank you for your help, I will try this!
cdun2
|||Thanks again. I had a couple of questions;
-Procedure sp_executesql expects parameter '@.statement' of type 'ntext/nchar/nvarchar'; nvarchar has a 'size' limit of 4000, and ntext cannot be the datatype of local variable @.string. char will handle up to 8000 characters. How can I work around these limitations?
-Could a table be created from the results of sp_executesql @.string so that the column names become fields in the table?
cdun2
|||Yes, you could alter the syntax
@.sql = 'select ' + @.sql
to
@.sql = 'select ' + @.sql + ' into myTable'
this would keep the columns dynamic. If the number of columns will be static you could create the table and do the following:
@.sql = 'insert myTable (valuelist) select ' + @.sql
|||I realized I didn't answer the first part of your question. If the string you are trying to pass is greater than the 4000 limit of nvarchar you can replace sp_executesql with exec() and make @.string a varchar(8000). It is better practice to use sp_executesql with dynamic sql but in this case it is your only real option.I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals
i.e.
col1 col2 col3
aaa 111 2005
bbb 222 2006
aaa 333 2006
ccc 444 2005
ccc 555 2006
bbb 666 2006
bbb 777 2006
ddd 888 2007
2005
aaa subtotal: 1
ccc subtotal: 1
total: 2
2006
aaa subtotal: 1
bbb subtotal: 3
ccc subtotal: 1
total: 5
2007
ddd subtotal: 1
total: 1
grandtotal:
aaa 2
bbb 3
ccc 2
ddd 1
totalnumber 8
I found the solution.
Thanks
There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:
INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent
If this does answer your question, please provide the following:
o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I have the following script that was generated using SMO:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_InsertCaseNote]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())
return @.@.IDENTITY
END
GO
But when I try to run it (in SQL Management Studio) I get the following error:
Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18
The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.
Any ideas on how to debug this problem?
Thank you.
Kevin
Please post the table DDL.|||
It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union
You can check to see if it is a computed column like this:
create table test
(
notComputed datetime,
computed as getdate()
)
go
select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go
Returns:
name is_computed
- --
notComputed 0
computed 1
If you want to see the definition (and other good stuff) use sys.computed_columns:
select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'
name definition
--
computed (getdate())
Arnie Rowland wrote:
Please post the table DDL.
Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.
Kevin
|||
It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union
You can check to see if it is a computed column like this:
create table test
(
notComputed datetime,
computed as getdate()
)
goselect name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
goReturns:
name is_computed
- --
notComputed 0
computed 1If you want to see the definition (and other good stuff) use sys.computed_columns:
select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'
name definition
--
computed (getdate())
Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.
Kevin
|||Right click the table in SSMS, click "Script table to..."
The error is not really all that strange, it is not letting your procedure do something that won't work.
|||Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:
Alter table CaseNotes add DateCreated as (getdate())
This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.
What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:
Alter table CaseNotes add DateCreated datetime default getdate()
-Tom
|||
Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:
Alter table CaseNotes add DateCreated as (getdate())
This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.
What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:
Alter table CaseNotes add DateCreated datetime default getdate()
-Tom
The table looks like:
/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]
The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.
Reproduced here for reference.
USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())
return @.@.IDENTITY
END
Thank you for your suggestions.
Kevin
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.
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.
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.
