I Need To Dynamically Create A Script Component
Oct 8, 2007Hi,
I need to dynamically create a script component (by code).
Is it possible?
Thanks ahead,
Eran
ADVERTISEMENT
A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs
Aug 13, 2007Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
Create SP Dynamically
Sep 2, 2005Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
SQL 2012 :: How To Capture Data Flow Component Name Dynamically While Package SSIS Package Is Executing
Jun 3, 2014I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies View RelatedCreate Tables Dynamically
Sep 12, 2007Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
Thanks & Regrads
Jai Shankar
Create Table Dynamically
Apr 14, 2008Hi,
I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last
Create table N2(ID,First,Last)
regards,
Mark
How To Dynamically Create Tables
Oct 7, 2006I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:
public void CreateTypedLiterals(Type type)
{
String sql = String.Format(
"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",
// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()
);
...
As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:
[SqlProcedure]
public void CreateTypedLiterals(Type type)
{
DataTable TypedLiterals =
new DataTable(
String.Format("Literals_{0}", type.ToString()));
TypedLiterals.Columns.Add(
"ID", typeof(int), "DISTINCT NOT NULL");
TypedLiterals.Columns.Add("Value", type);
...
But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:
using(Microsoft.SqlServer.Server)
{
CurrentDatabase.Tables.Add(TypedLiterals);
}
But this is afaik not possible. Has anybody an idea how to solve this issure?
Create Subtotals Dynamically
Apr 3, 2007
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
Create Charts Dynamically
May 3, 2008Hi there!
I have a certain scenario and would like to know if it would be recommendable to try and realize it through RS.
I have a matrix of sales data (countries, regions, categories, total sales, Year-To-Date, changes, etc.) that need to be shown on charts. The charts themselves have a single format (bars). The out put needed is a relatively large number of charts (3000+) that would allow for a great number of comparisons (country to country, region to region, etc.). All these charts share a single layout. What would be varying greatly are the values and the labels attached to the elements on the chart (labels of the bars, title of the chart, legend, footnotes, etc.)
My plan is the follwoing:
- create a .net module that would prepare the data for the chart. This would be done with two tables
- parent table with general information
- child table with the the values for the bars and the labels
- Create a parametrized report in RS that accepts the Id of a report from the parent table
- Call this report for each and every report that can be found in the parent table
I would really appreciate any information of the feasabilitiy of this plan. I have worked a lot with Crystal before, but I don't have it anymore and will be using RS from now on. I haven't really started working with RS as of yet, but would need to know if this is "doable" with RS!
Thank you very much in advance for your replies!
Greetings
NB
T-SQL (SS2K8) :: Create Table Dynamically?
Sep 5, 2014I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
Create Table And Column Dynamically
Jun 2, 2008Hi,
There is a table exists in a database, I have to write a stored procedure to create the same table in different database, with the same column name and field. This should be done in runtime. Is it possible. The table will be passed as a parameter to the stored procedure.
Create And Then USE A Dynamically-named Database?
Nov 2, 2006I have a need to create a database, and then populate it. However, thecode below doesn't work as I hoped it might (it creates the table inthe "master" database, which is Not A Good Thing). I know already(thanks Tony!) that if you use Dynamic SQL for the USE command, thenthe subsequent operations need to be Dynamic SQL as well, which is apity since there are over 11,000 lines of it and I don't really fancydebugging it!Does anyone have a cunning plan? In a nutshell, I would like to beable to:1. Create a new database with a derived name - as in the case below aname based on the year, though it might be month as well.2. Create and populate tables in this new database.These operations would ideally be running from a scheduled job.Any thoughts?TIAEdward====================================USE MASTERDECLARE @DBName VARCHAR(123)SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)if not exists(select dbid from master.dbo.sysdatabases where name =@DBName)exec('CREATE DATABASE ' + @DBName)elseraiserror('Database already exists.',3, 1)EXEC ('USE ' + @DBName)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([TestID] [int] NOT NULL ,[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO
View 3 Replies View RelatedHow To Dynamically Create Columns For A Table
Apr 29, 2008Hi,
how to dynamically create columns for a table
Trying To Dynamically Create Text Files
Oct 27, 2006I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.
How do go through the recordset to create a file for each customer?
Create File Location Dynamically
Apr 10, 2008hi,
Can you guys please give me the steps of how to create a destination file dynamically. What i mean is for example i want to get everything from a table and ssis should create a file distination according to today's date and save it in a specific folder.
thanks
Create Query Dynamically According To Parameter
Dec 6, 2007I have a parameter in my report with 2 options.
1."Less than Jan 1 2007"
2."Greater than Jan 1 2007"
According to the option selected, I should have the query look like:
1."Less than Jan 1 2007"
select * from table_name
where open_date < Jan 1 2007
or
2."Greater than Jan 1 2007"
select * from table_name
where open_date > Jan 1 2007
Please let me know how can I acheive this.I'm Using ORACLE database
Transact SQL :: Dynamically Create A View
Jun 25, 2015Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this
Create Table [dbo].[BuildingInformation] (
ID INT IDENTITY NOT NULL,
BuildingName varchar(50),
RAName varchar(50)
[code]....
Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change. If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table
Create Report.rdlc In Vb.net Dynamically
Oct 23, 2007I' m using Microsoft Reporting. Is there a way to create Report .rdlc file Dynamically on the fly? I' m using VB.net.
Any help can be appreciated.
Thanks
Mythili
The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.
Oct 26, 2007Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
How To Dynamically Create SQL Inside A Stored Procedure?
Feb 28, 2005I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
Dynamically Generating CREATE INDEX Statements
Feb 15, 2001Does anyone have any SQL that will look at a DB and dynamically generate CREATE INDEX statements? I
know I can use EM but I want to make this a scripted process and you can only generate CREATE INDEX statement if
you script out the tables too.
Any Ideas??
Thanks
Dynamically Create Connection Managers @ Run Time
Jan 28, 2006Is there a way to dynamically create a connection manager @ run time? I would like to do this from a data set of connection strings so I can link them into a union all component.
View 1 Replies View RelatedIs It Possible To Dynamically Create Columns In A Table In SSRS
Nov 19, 2007
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
Dynamically Create Text File As Destination
Feb 16, 2007I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.
Thanks,
Fred
Create Destination Table Dynamically In A Program
Feb 17, 2007HI,
I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.
Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.
Has anybody done that?
Any help in this regard is greatly appreciated.
Pavan
Is It Possible To Dynamically Create Multiple Variables In A Package?
May 21, 2008I'd like to be able to call different packages from a control flow. These packages will have different requirements for parameters therefore I'd like to create them dynamically.
Is this possible? Can I do it using a script task?
Thanks in advance.
Ben
The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.
Jan 23, 2007Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
T-SQL (SS2K8) :: Create Function For Dynamically Update Every Year?
May 27, 2015UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
Dynamically Create An SSIS Bulk Insert Package
Sep 26, 2007
I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!
Cheers~
Create A Table And Then Using It As OLE DB Destination Component
Apr 12, 2007I want to do the following in a package:
Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.
Is this possible in SSIS?
The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.
I've looked at:
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.
Thanks
SQL Server 2008 :: Dynamically Create The Table With Current Date At The End?
Feb 12, 2015I am running a script by the end of the day. What I need is the rows in my temp table get saved in a permanent table.
The name of the table should end with the current date at the end.
Declare @tab varchar(100)
set @tab = 'MPOG_Research..ACRC_427_' + CONVERT(CHAR(10), GETDATE(), 112 )
IF object_id(@tab ) IS NOT NULL
DROP TABLE '@tab';
Select * INTO @tab from #acrc427;
SQL Server 2012 :: Create Table Syntax Dynamically On Run Time
Apr 19, 2015I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL default getdate()
)
SQL 2012 :: Dynamically Create Connection To A Database Within SSIS Package
Aug 6, 2015I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
Is this possible, if so how?