Programatically Create And Configure A FlatFileConnectionManager

Jul 18, 2006

I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.

Here is some of my code:

Package p = New package();

ConnectionManager sourceCM = p.Connections.Add("FLATFILE");

sourceCM.Name = "some-name";

ConnectionManagerFlatFileClass cmffc = sourceCM.InnerObject()

 

I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:

ConnectionManagerFlatFileClass cmffc = (ConnectionManagerFlatFileClass)sourceCM.InnerObject()

Now it will compile. But when I run it I get the error:

"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"

 

Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?

-Jamie

 

P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!

View 3 Replies


ADVERTISEMENT

Programatically Create SQL Adapter

Nov 12, 2007

In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?

View 3 Replies View Related

Programatically Create A Script

Jun 16, 2005

I need some help with the following issue. I need vb.net code that will create a T-SQL script.  For example given the orders table in northwind I would get the following with indexes, RI, ect. :CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL , [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD  CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED  (  [OrderID] )  ON [PRIMARY] GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD  CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO
 CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
 CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
 CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
 CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
 CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO
 CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]GO
 CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]GO
 CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY  (  [CustomerID] ) REFERENCES [dbo].[Customers] (  [CustomerID] ), CONSTRAINT [FK_Orders_Employees] FOREIGN KEY  (  [EmployeeID] ) REFERENCES [dbo].[Employees] (  [EmployeeID] ), CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY  (  [ShipVia] ) REFERENCES [dbo].[Shippers] (  [ShipperID] )GO
 

View 1 Replies View Related

Create Flat File Source Programatically

Jun 1, 2006



I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.

my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.

any help is appreciated. thanks.



View 9 Replies View Related

Create Konesans RowNumber Component Programatically

Jun 5, 2007

hi all,



i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.

in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.

has anybody try to do this ?

is there any way to get the row number other then this way?

any ideas?



thank you!!! for all your help until now!!

View 1 Replies View Related

How Do I Create A Integration Services Project Programatically???

Oct 25, 2006

hi everyone,

My vb .net app creates programatically a package. I mean, you can provide name, description, number of OLEDB/ADO connections, variables and so on, where it's stored, from a WinForm.

Up to the moment, process only creates boxes separately wihtout links.

My concern is after this successful cycle I only can open that DTSX and see its components but executing button is disabled (only possible with DTEXECUI.exe) from BIDS.

Only when I create a project (DTSPROJ) and then I put that same .DTSX inside I am be able to execute it.

Well, up to here all of this is very good. Things were created this way for some reason.

My question now is how do I from my application in order to create dtsproj and dtsproj.user (I don't know how many files are needed) and be able to create a package and its "father project". My idea is that the user can choose if create a standalone DTSX or a full project.

Let me know if you need further details.

TIA,

View 3 Replies View Related

Programatically Create A DSN For SQL-Server With Non-standard Port-number

Apr 16, 2007

Hi all,



I have to create a DSN for SQL-Server. Therefor I use the function "SQLConfigDataSource" from "ODBCCP32.DLL". But the problem is that the SQL-Server does not use the Standard-Portnumber 1433. I've tried to make the DSN with "SERVERNAME xxxx" where xxxx is the Portnumber, sometimes it works, but often it does not. When it does not work, then the Checkbox in the ODBC-Configuration-Tool is checked, so the connection is searching for the standard-port. How can I tell the system that it shall NOT use dynamic configuration?



Thanks for help,

Franz

View 6 Replies View Related

Create And Configure Databases Via C#

Feb 20, 2008



I am using Visual Studio 2008 and have created a .mdf database within my project. I want to be able to do a few things and I am not quite sure where to start. The program I am writing manages rental properties. Thus, each rental property that the user owns needs its own sql express database. For this to work:

1. I need to be able to dynamically create a database for each property that the user owns.
Is it best to do this as described here http://msdn2.microsoft.com/en-us/library/bb399420.aspx ? Or is it best to have a template database and then make a duplicate of the database using the File.Copy() method.

If it is best to do it programatically as shown in the link above, how do you create relationships between tables?

2. I need to be able to dynamically add users to the database via c# code. How does one do this? Is it possible do through LINQ?


Hopefully someone can point me in the right direction. Thanks

Nick

View 6 Replies View Related

Programatically Change DBs

Sep 19, 2005

Carlos writes "I am trying to create a sp that would jump from one DB to another using the USE command. I built a string and then I tried to run EXECUTE(@SQLString) with no lock. This is the sample code I am using:

declare @DBName varchar(200)
declare @SQlString varchar(300)
set @DBName='model'
set @SQlString = 'use '+@DBName
execute(@SQlString)

Is there any way I can accomplish that?

Thanks

Carlos"

View 2 Replies View Related

Best Way To Tell If A SP Is A Query Programatically

Sep 16, 2005

Using ADO.NET Or regular queries on SQL Server, is there a good way ofdetermining if a Stored Proc will be returning a result set or if it isa non-query that does all I/O through parameters?Thanks.

View 1 Replies View Related

Subreports Programatically

Dec 27, 2007



Hi

I have a main report and sub report. The subreport has some fields like SYS, LOC, MSG etc.

But the report should display the values filtered basis on system. i.e. I must have a sub report

for SYS1 , SYS2 , SYS3 and so on. I can create a sub report for each system. But I want to create only subreport and

call them from the main report. How do I do this?

Thanks

Sai

View 5 Replies View Related

How To Programatically Connect To An SQL Database?

Nov 18, 2006

Can anyone please direct me to an easy to understand Description, Article or Video tutorial that shows an example on how to programatically connect to an SQL database and retrieve data from a collumn using C# and VISUAL WEB DEVELOPER 2005 EXPRESS EDITION. Also, what namespace should be imported to do this? (From what I have gathered out of the help, it seem that System.Web.UI.WebControls is the one)
I am trying to create a "news letter" web page. I have a basic form and when I hit "send" then the code in the background should access the database with all the recipient info, and retrieve all the E-mail addresses, Names, etc. and include it in each e-mail.
( I don't know if it is just me, but the help included with VISUAL WEB DEVELOPER 2005 EXPRESS EDITION is extremely difficult to understand - from a beginner point of view).
Thanks!
Regards
Jan

View 1 Replies View Related

SELECT Columns Programatically

Jul 31, 2007

Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks. 

View 7 Replies View Related

Programatically Get Values From Sqldatasource

Oct 1, 2007

Hi All,
I am having problems getting values out of an sqldatasource.
I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.
the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.
The insert is fine, i just don't know how to get the values out of the first 'select source'
Any pointers or suggestions most appreciated.
Cheers

View 2 Replies View Related

Generating Tables Programatically

Jun 7, 2008

hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip(    id int IDENTITY NOT NULL,    ip varchar(15) NOT NULL,    hostname varchar(128) NOT NULL,    primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens  its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code  thankssimo  stored as a new query     

View 1 Replies View Related

Change SqlDataSource Programatically

Jun 10, 2008

I have a list box populated by a SqlDataSource with a basic statement of Select name where location = @location.       Location is bound to a dropdownlist control.  I want to have a checkbox for the Location dropdownlist and a checkbox for another dropdownlist (for the alphabet) that will select only those people with a last name of the letter selected.  Changing the checkbox would change the sql statement. 
OK so there are two questions here:
1.  How can I write a sql statement that would select only people whos last name begins with a certain letter.  Would it use some sort of convert function to a string with only 1 letter?
2.  How can I programatically change the sqlDataSource's 'select' statement.  I need to switch between sorting by location and sorting by alphabetical last name.  All of my previous experience has had me configuring the datasource in the asp.net code. 
 
Thanks in Advance. 

View 7 Replies View Related

Creating A Table Programatically!

Nov 25, 2003

Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.

How can I solve this problem.

Any help is appreciated!
Krish Chandra

View 1 Replies View Related

Programatically Verifying SQL Syntax

Nov 24, 2004

OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"

and transforms it into something that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"

My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?

Any help would be appreciated! Cheers.

View 2 Replies View Related

SQLDatasource - Managing Programatically

Nov 25, 2005

HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode   Case "Category"      sqldatasource1.SelectCommand = "productsbycategory"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure      ******'Need to add querystring parameter   Case "Wishlist"      sqldatasource1.SelectCommand = "productsbywishlist"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure      ******'Need to add Session variable parameter   Case Else      sqldatasource1.SelectCommand = "allProducts"      sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks

View 2 Replies View Related

Checking Services Programatically?

Jul 20, 2001

Hi ,
Could anybody point me to an NT/DOS script ( e.g a batch file ) that could check if the MSSQL Server and SQL Server Agent Services are running ?

I am trying to write the following script to check for the status. Each of the following commands gives a 1 as output if the service is running and 0 if it is not. The problem I am facing is that I am not able to check the output after the command is executed. I am neither able to assign it to a variable or include the whole command in an IF statement.

net start | find /c /i "MSSQLServer"
net start | find /c /i "SQLServerAgent"

Is there any other way to do this programmatically ? Any help is greatly appreciated.

Thank You,
Srini

View 4 Replies View Related

Stop/start SQL Programatically

Apr 11, 2002

What is the best way to programatically stop/start SQL Server? I want to stop it to do backups and then restart it.

I was hoping to do it within SQL Agent but it depends on SQLServer so you can't do a restart.

Any help greatly appreciated.

View 1 Replies View Related

Programatically Restore SQL Database

May 15, 2008

Hi Guys,

I am trying to figure out a way to restore a database programatically. Here is how I do it now. Right click on the database, select Restore. Browse out to my computer and find the .BAK file. Select the database and go into 'Restore Options' I have to physically change the .MDF and .LOG paths since I am getting this backup is not coming from my computer. I select 'Overwrite the existing database' then click restore.

My problem is that I have to change the paths to the MDF and LOG files. How could I automate this? Thank you!!

View 7 Replies View Related

How Do I Programatically Acces A Database With A '-' In The Name?

Jul 23, 2005

use this-is-a-stupid-namereturns an errorFor that matterselect *from this-is-a-stupid-name.dbo.anytablereturns an errorI've tried single quoted, double quotes, escaping. In the SQL editor I canselect the name from the dropdown and I'm OK - but I'm writing a perlscript to connect to the database.I didn't create the database and don't know how it was created.--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

How To Determine Programatically What Output Of A Sp Will Be?

Oct 5, 2005

Using Sql Srv 7I know I can use the system sp's sp_stored_procedures and sp_sproc_columnsto determine all the sps in a db, and what input parms there are for aparticular sp... but... if the sp returns a result set, is there a way tofind out the stru of that in a similar manner???

View 1 Replies View Related

RS Programatically Insert A Pagebreak

Mar 5, 2008



Anybody know how to or if we can programatically insert a pagebreak. in a table. Yes, I know about the ones at group footer and header these arn't doing what I need.

vbwrangler@yahoo.com

View 1 Replies View Related

Programatically Deploying Reports

Jul 10, 2007

Hi All,

I want to programatically deploy the report in VS2005 environment,ie.from an asp.net page if i click deploy report Button ,the report should be deployed into the report server automatically.So that i can view the report in report server.
the input to the asp page would be the.rdl file...
If anyone could help then it would be good.



thankyou.

View 5 Replies View Related

Lookup Component - Programatically

Dec 5, 2006

Need to ask three questions regarding this component I am creating programatically:

1) How do you select "Modify the SQL statement" programatically.

2) How do you change the actual sql.

3) How do you add a parameter, also programatically.



I have manged to select "Enable memory restriction" by using

instance.SetComponentProperty("CacheType", 2);

but can't add a paramater and modify sql statement. Have used

instance.SetComponentProperty("SqlCommand", statement); and

instance.SetComponentProperty("SqlCommandParam", statement);

with no luck. Any ideas, have searched the web but found nothing.

Thanks.

View 1 Replies View Related

Deploying RDL File Programatically

Apr 23, 2007

Hi,

I am able to create a rdl file programatically.I want to deploy the RDL file programatically in Report Server.Any help is appreciated.



Thanks,

Prabu

View 6 Replies View Related

What's The Best Way To Programatically Modify SQL Results For Display?

Oct 5, 2006

Lets say you have to retrieve some SQL.Then you have to go through those records and delete some items, change some values, and even add columns and rows...based on logic that depends on the data you got.Thanks.

View 2 Replies View Related

Programatically Viewing The Data In A Particular Column

Oct 10, 2006

This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column...  One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization.  I need to be able to read this value so I can assign it to the Page.'First Do a Database ConnectionIf Not Page.IsPostBack Then'Start by determining the connection string valueDim connString As String = _ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim myAdapter As New SqlDataAdapter(myCommand)myAdapter.Fill(myDataSet, "SEO")'Create a DataViewDim myDataView As New DataViewmyDataView.Table = myDataSet.Tables("SEO")'Ok...this is where I'm lost.  Now how do I look up the value for the column "Title" in this Dataset'Close the connectionmyConnection.Close()'End database connectionsEnd Using 

View 6 Replies View Related

SQLDATASOURCE ~ ACCESSING DATA PROGRAMATICALLY

May 9, 2007

Can anyone point me in the direction of code that will allow me to acce3ss the data held in an SQLDataSource.
 
The Control is link Selection Staement has been set at run, what I want to do is loop thru any data that is rertrieved, setting oter controls on the page depending on values
 Thanks
 Steve (I have not got much hair left, please help soon)

View 3 Replies View Related

Creating An MS SQL Data Table Programatically

Aug 27, 2005

I use Visual Studio and VB.

If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.

It would suit me, for a current job, to be able to create the table itself programatically.

I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.

David Morley

View 1 Replies View Related

Problem In Creating A Database Programatically....

Jun 27, 2001

I am crateing the database on the fly using db directory in VC++. I am able to create database. I am also able to create login. These can be easily done by logging as master using the username and password sa and "" resp. Now I want to add a user to the database that i created. but it is being added to the master database. I know that I have to change my login from sa ,"" to the username and password that i gave during login. but i dont know how to change the login. Please help. If you have any code for that then that will be of great help.
Hoping to get a positive response as soon as possible..
Thanx
Nitin Gulati

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved