Create DTS PACKAGE Programmatic
Nov 24, 2006Hi guys..!!
i am working on Dynamic creation of DTS-packages in C#.NET 2005(sql server 200)
but i not must create package in Sql Server.
but i cant...
can i do ?
any ideas ??
thx...
ADVERTISEMENT
Programmatic Insert Into SQL Database
Nov 1, 2007I have a page with over 20 Textbox and DDL controls and an upload in various Divs and Panels (Ajax enabled) that are used for gathering user data. Some of the fields are mandatory and some optional.
In the Code behind (VB- I am a complete novice) On the submit button click event, I iterate through the controls in the page and build an array with information from the controls that have data in them, (filtering out the non-filled textboxes, and DDLs).
All this works well, and I get an array called 'myInfo' with the columns with the control ID, and control values 'rvalue' (as string), with the number of rows equal to the filled textboxes and DDLs.
I then step through the array and build a string with 'name=values' of all the rows in the myinfo array and email this as a message:
ThisMessage = "" NoOfControls = myInfo.GetLength(0) For i = 0 To NoOfControls - 1 ThisMessage = ThisMessage & myInfo(i).ID.ToString & "=" & myInfo(i).rvalue.ToString & "; " Next
SendMail(email address, ThisMessage)
I also want to add this information to a database, appended by the IP address and datetime.now.
Dim evdoDataSource As New SqlDataSource() evdoDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnectionStringCK").ToString evdoDataSource.InsertCommandType = SqlDataSourceCommandType.Text
Dim InsertMessage As String = """INSERT INTO evdoData (" NoOfControls = myInfo.GetLength(0)
Dim k As Integer For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & myInfo(k).ID.ToString & ", " Next
InsertMessage = InsertMessage & "IPNo, DateEntered) VALUES ("
For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & "@" & myInfo(k).ID.ToString & ", " Next InsertMessage = InsertMessage & ", @IPNo, @DateEntered" & ")""" evdoDataSource.InsertCommand = InsertMessage
I then similarly iterate through and do the insertparameters.
Now here is the rub- (My all too often DUH moment!)
When I look at the insertmessage in debug (and to be sure- I also show the insert string on a temporary debug label on the page), The insertmessage looks fine:
"INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirstName, @Age, @Email, @Phone, @Country, @City, , @IPNo, @DateEntered)"
However when the above code(evdoDataSource.InsertCommand = InsertMessage) is run, I get an error - the message with the error is:
The identifier that starts with 'INSERT INTO ModelData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirsteName, @Age, @Email, @Phone,' is too long. Maximum length is 128
EH? When I actually copy the InsertMessage from the debug window and paste it manually after the command "evdoDataSource.InsertCommand = " It works, and I get the data inserted into the table..
-It would seem that I am probably missing something obvious in my complete "noviceness" . HELP! (oh and thanks a bunch in anticipation)
_____________________________________________________________________________________________________________________
Its Easy --------------------------When you know How. Meanwhile Aaaaaaaaah .
Programmatic Inspection Of A Dump?
May 21, 2004I need to set up a job to allow users to restore their databases, on SQL Server 2000 SP3. The idea is that a user inserts a record into a table, identifying the dump they want to load. (They can only restore their own account.) A job picks up this record, restores the database, and notifies the user as appropriate.
My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.
Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
Programmatic Use Of SQLDataSource Not Returning Dataview
Sep 4, 2007I am creating an ad-hoc reporting module for my website. The user may select a list of columns and specify the WHERE for the report. I build the select statement and the where statement and pass the info to a stored procedure. The stored procedure accepts 3 select parameters and a 3 wherestmt parameters. I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource. Binding it to a gridview shows me all my values. Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further. I am getting an null back when I call the select method. Any ideas why? or a workaround?
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
<SelectParameters>
<asp:Parameter Name="strSelect" Type="String" />
<asp:Parameter Name="strSelect2" Type="String" />
<asp:Parameter Name="strSelect3" Type="String" />
<asp:Parameter Name="strWhere" Type="String" />
<asp:Parameter Name="strWhere2" Type="String" />
<asp:Parameter Name="strWhere3" Type="String" />
<asp:Parameter Name="bitDebug" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Getting an error that the object is null? why is the above code not returning any data?
'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader)
If rptview.Read Then
lblMsg.Text = "Got Rows"
End If
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard
'does not work. Need to be set programmatically.
e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 "
e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2"
e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5"
e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null"
e.Command.Parameters("@strWhere2").Value = "'"
e.Command.Parameters("@strWhere").Value = ""
e.Command.Parameters("@bitdebug").Value = False
End Sub
thanks for any help.
Programmatic Access To Sensitive Data
Jan 18, 2007Hello,
I'm developing custom SSIS task (control flow component) which offers usert to choose ADO.NET connection.
I want to use this connection in my code and access SQL server.
There is no any problem unless connection is with IntegratedSecurity = true.
But, when user chooses to set username and password, I can not access password because it is not present in the connection string as sensitive information.
Package.DTSProtectionLevel is set to EncryptAllWithUserKey
I need to programmaticaly access to password, but I don't know which class to use from DTS (SSIS) object model.
Any help?
Thanks, Borko
DTSTransform.DataConvert Programmatic Access?
Jan 27, 2006I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.
The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.
I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.
I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?
Here is what is left of my code:
IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}
Use A DTS Package To Create An Excel, Which Has In The Filename The Date The Package Is Ran
Dec 19, 2006I am trying to create a DTS Package which will run a SQL query and export the results to an Excel file. I would like to the name of the excel to be "dynamic". What I would like is for the name to be ChronicDownSiteReport - mmddyy.xls. The mmddyy is the date which the package is executed.
How can I do this?
Also, I want this package to be excuted at 1am every Sunday Morning. I have attempted to schedule this to run, but when I come to work on Monday, the excel file is not present and the email, which is sent telling me that the file was created is not in my mailbox.
Package Is Not Able To Create Process To Execute Child Package.
Jul 3, 2007 Hi,
In my application, i have two package, parent package and child package. the parent package is executing child package using a Execute Package Task.
"Execute Out Of Process" property of Execute Package Task is set to TRUE. means the child package will be run in separate process not in the process of Parent package.
this was working fine, but at a particular client location. its failing the error is "not able to load child package".
for me it seems some setting on server restricting to create separate process for child package execution.
when "Execute Out Of Process" property of Execute Package Task is set to FALSE. its working fine.
can anyone help what could cause its failure with property set to TRUE.
Appreciate any help.
Simulataneous Programmatic And Declarative Access To SQLDataSource Data
Apr 22, 2006Hi,
Is there a way to programmatically access the results of an SQLDataSource control select at the time it is binding to a DetailView? There are some fields in the data results I do not want to render to the page, but I still need for other stuff.
I know I can programmatically invoke a select method, but since it is selecting anyway for binding to the DetailView it would be better to get all the data in one swoop.
Thanks in advance for any assistance you could provide.
Best Regards,
Brett
SQL Server 2014 :: Programmatic Creation Or Modification Of SSIS
May 21, 2015I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
How To Create The Dts Package In Asp.net 1.1
May 24, 2008how to create the dts package in asp.net 1.1 pls send me this mail
View 2 Replies View RelatedHow To Create Package
May 10, 2007 i need to transfer the data from allbase (old database) to sql server 2005 .
moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).
and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.
and please help me to create custom component if needed.
thanks in advance
Peer-to-Peer Replication (Programmatic Control And Setup)
Feb 7, 2007I have reviewed the BOL documentation on how to configure Peer-to-Peer replication via T-SQL and how to use the Replication Wizard to implement replication.
What I would like to find out is how do I configure the peer-to-peer replication process to use an existing column on a table that contains a GUID instead of creating an extra column with a uniqueidentifier GUID value. When you use the Wizard each table article has this extra column added to it.
I don't seem to be able to find it in the books-on-line. Can some one point me to the correct article or BOL page.
Thank you.
...cordell...
Create A SP That Will Launch A DTS Package
Oct 10, 2006Hi all. How can I create a Stored Procedure that will launch a DTS package that I have already built dynamically ?
I have a string already built and I need to put it in a SP because my application is php (linux) and the database is SQL Server on a Windows machine.
I will use php to execute the stored proc, which is the only way to access it.
My string looks like this :
C:Progra~1Micros~380ToolsBinnISQL.EXE -S [MyServer] -U [Username] -P [Password] -Q "ISQL_Batch 'D:DDFIImporteIMPICAFI.bat [user] [Schema] [Pwd] '" -n -d [database]
(Words in [] are only to show that I will put other values)
Thanks
CFGilles
Trying To Create A SSIS Package
Jul 3, 2007Hello All,
I am learning SSIS and I am working through the Wrox book on 2005 SSIS. When I try to Import the files from the AdventureWorks2000 database I cannot see the Human Resources files. I was able to go through the wizard once and see the files and select them and proceed, but now I cannot see them. Please keep in mind I am a newby to this. What should I check for or what am I doing wrong?
Thanks,
Kurt
Can't Create New SSIS Package In VS 05
Jun 1, 2007Is there anyone out there that can help me - please! I'm about to put my head through a wall.
I used to be able to create SSIS packages in VS 05. It's been a while since I've done it, but when I went in the other day, it would let me - all I can do is get errors. It tells me an "error prevented the view from loading" when it opens in control flow (blank packages and ones I created before all this started) and I get this error :
"Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)"
If I go over to the Data Flow tab and there are no errors until I click "Click here to add a new Data Flow task", then I get this :
"Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)"
So... here's what I've tried.
Uninstalled and reinstalled VS 05 and SP1 (this was before I realized the problem was with SQL).
Uninstalled and reinstalled Office 2003 Web Tools.
Made sure all the MSXMLs were registered.
Uninstalled and reinstalled all SQL Servers and Tools/Components.
Made sure all SQL Services were running under Local Account
Made sure Integrated Services service was running.
Applied all SQL patches.
I cannot get this to work and I need to be able to create packages. The only other step I can think of is wiping my computer and I don't want to have to do that.
Please please can anyone help me?? I've been scouring the internet and working on this for 1 day and 1/2.
How To Create A DTS Package In 2005
Jan 23, 2006Can anyone give me a quick tutorial on how to create a DTS package in 2005 using Business intelligense studio.
I want to import a file from a ftp location and load it into the database as a table.
I know how to do it in 2000, but can use some help in 2005.
thanks
How To Create A DTS Package In 2005
Aug 15, 2006I am 100% new to SQL. I have used Access for ever. I need to know how to take a Access 2003 database and covert it over to SQL 2005. I have no idea how to complete this. I was told to do a DTS package but I do not see any options for this.
Any help would be great!
Thank you
Trying To Create A DTS Package To Import Dbf Tables && Need Help
Mar 9, 2004Hi There,
I am trying to create a DTS package that will import dbf tables. My problem is that the data was created back in the pre-dos 5 days, so to save room they took the dates and convert them to 2 characters.
Now I would like to import these tables and conver the date back. I do have a Function that I can run in FoxPro to "Unpack" these dates.
CODE
Function UnPackDate( cDate )
*!*PARAMETERS: f_datestr - Character (manditory). This should be a two
**!* byte string created with PACKDATE().
*!*
*!* RETURNS: A date from 1/1/1970 and 11/29/2126
If EMPTY(cDate)
Return {//}
Endif
Local nDate
nDate = (ASC( LEFT(cDate,1) ) * 256) + ASC( RIGHT(cDate,1))
Return ( CTOD("01/01/1970") + nDate - 8225 )
*================================================= ======================
Does anyone know how to take this function and make it work while doing an import?
Thanks
Sue
Create MSI Installer For SSIS Package
Sep 29, 2006Can anyone sugest me steps to create the MSI Installer for SSIS packages
Using Transact SQL To Create And Add An SSIS Package
Aug 22, 2007Can someone point me to any whitepaper on how to use a Transact SQL script to create an SSIS package. I will need to be able to run the script at various customer sites.
Package Error: Cannot Create Thread
Nov 17, 2006I have a child package that has been run successfully multiple times in the last month +. Each time with roughly the same amount of data, give or take a few thousand rows.
Suddenly, this child package is now giving me the following errors from the log file:
Error: 2006-11-17 12:04:19.98
Code: 0xC0047031
Source: DFLT Primary DTS.Pipeline
Description: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.
End Error
Error: 2006-11-17 12:04:20.03
Code: 0xC004700E
Source: DFLT Primary DTS.Pipeline
Description: The Data Flow task engine failed at startup because it cannot create one or more required threads.
End Error
I tried taking the child out of parent and running it by itself. I still get the same error. There are three other child packages that run on the exact same data and they have no problems. The control flow for the package first runs an SQL command. Then it has a data flow. The data flow grabs records from the source, adds two derived columns, looks up data and then stores to the destination. Relatively easy compared to other packages that are running just fine.
I've had our network people check the both the server running SSIS and the database server (two different machines) and there are no memory spike while the package is running.
Any ideas?
Create New Scheduled Job Which Runs A Dts Package
Nov 28, 2007Hello Experts,
I found some similar threads and guides but they didn€™t help me with my special problem.
I converted a dts package (built in SQL 2000) to SQL 2005. Right now it€™s a legacy package.
(I tried the Tool Microsoft SQL Server 2000 DTS Designer Components to open the package. It€™s going well)
I would like to build a scheduled job which runs this dts package.
In SQL 2000 you can right click on the package and create the job.
SQL created string like this:
Dtsrun ASDFHJKSF56A4DFSLAKDHFJKS65646ASDFHSF (very long sting, it€™s the ID of the dts package)
How can I make something like this in SQL 2005?
Where I can get the ID of a dts package from?
Best Regards,
Alex
p.s.
- I red the thread from Jamie Thomason and will directly mark as answer after I get a answer
- of cource I will delete my thread too if I overlooked a thread with the same issue
Create SSIS Package Programmatically In VB6
Nov 28, 2007Hi,
I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library.
We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.
Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#.
Any help with white paper or sample code would be appreciated.
Thanks in advance
How Create Msi For Install SSIS Package
Sep 6, 2007how i will can create a install for SSIS package
View 3 Replies View RelatedNeed To Create Package For Refreshing The Cubes
Apr 4, 2008
We have scenario where we need to create package for refreshing the cubes. Let me explain it breifly.
We are doing ETL process in different ETL tool (not SSIS) and once the process is done, we are inserting in a table where we have a column like date, Completed C as the status. Once we get this information i.e 'Completed C' status and date, we need to refresh the cube like as follows,
1. Previous day cube need to be refreshed daily once
2. Previous week cube need to be refreshed weekly once.
3. Previous month cube need to be refreshed monthly once.
4. Historical cube need to be refreshed daily once.
We have decided all these above operations needs to be done in SSIS. In this case what are the things to be done while creating a package.
1. What are the control flow items to create it in SSIS?
2. Is there any way to have pooling like every 10 minutes to check whether the table has 'Completed C' status and STOP EXECUTE this package?
3. Is there any way to check the date for Previous day cube, Previous week cube, Previous Month cube and Historical cube etc?
Thanks in advance,
Anand Rajagopal
How To Create A Script To Execute A Package?
May 10, 2007
hello,
I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?
thanks in advance
Create Database Within SSIS Package
Aug 22, 2006I want to create a package that imports data from a Visual Foxpro database to SQL Server 2005 Express database. I used the wizard in BI Development Studio (similar to the DTS in SQL Server 2000) to create a package and noticed that the SQL statements created in the Preparation SQL Task only has code for creating tables. I want to make the package such that it first creates the destination database before creating all the related tables in it! When I tried to edit the SQL code to include DROP DATABASE and/or CREATE DATABASE statements, these were rejected.
Is it possible to do this or do I have to first create the database outside the package and then call the package? I want to make this a seamless process for clients who do not have the know-how of SQL Server database administration.
HELP!!!!!!
Can't Create A SQL Server Package Configuration
Jun 29, 2006I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.
When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.
The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:
Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)
Could not complete wizard actions.
I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?
Many thanks
Matt
How To Create DTS Package Using MS SQL Server Management Studio
Oct 30, 2007
Hi all experts,
Im new to MS Server Management studio. I need to create a DTS package, but unable to do so. Im not sure whether I need the Microsoft SQL Server 2005 DTS Designer Components in order to create DTS package. Can anyone help? Thks in advance.
How To Create An SSIS Package Correctly By Programming?
Oct 12, 2005Recently I try to create a package completely by c# code,not the ETL tool.
View 9 Replies View RelatedIs 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
SSIS - How To Create A Script That Can Be Used From Various Dataflows Within The Same Package
Sep 4, 2007hi,
i have to manipulate/calculate an attribute at runtime in a number of dataset within a package.
since the manipulation will be the same for all these datasets and that it has to be done with a script, i am looking to find a way of creating a single instance of this script and call it as and when necessary.
could i use a variable of data type object and pass and retrieve parameters into and out of it?
lets assume the following scenary to illustrate my point.
i have to create a new attribute in two datasets ( ds1, ds2) which is based on the following attributes:
firstname
surname
and that i want to create this new attribute as:
surname, firstname
so i am creating a single string composed of surname plus a comma and firstname.
thus i will be creating a new attributes called custNames for two or more dataset and it tomorrow it is decided that i should be done in a different way then i only need to amend in on place.
i know it is not clear but i will be glad to anwser any queries to clarify this question.
thanks,
Nicolas