I'm building packages programmatically. I need to add a ScriptTask to my package and include the script code. The script task itself is easy to add. But I can't figure out how to add the script code to the task.
I found one post in this forum saying the trick is to use the PutSourceCode method of the StriptTaskCodeProvider class, but I can't figure out how to do that.
Can anyone provide a code sample of how this is done?
Thanks.
Hi am trying without luck to load a package which contains a ScriptTask and read the source code of that task.
I can load the package and get the ScriptTask no problem.
However i am not sure how to get the source code.
I know i have to use the ScriptTaskCodeProvider and i assume the GetSourceCode() method.
This is what i have so far
ScriptTask scriptTask = taskHost.InnerObject as ScriptTask;
ScriptTaskCodeProvider codeProvider = new ScriptTaskCodeProvider();
codeProvider.LoadFromTask(scriptTask);
string sourceCode = codeProvider.GetSourceCode(scriptTask.VsaProjectName);
Any assistance greatly appreciated.
Cheers
Richard.
Hi,
I have the following flow in which i am looping through a folder containing textfiles
Foreach loop
[
ScriptTask -> DataFlowTask -> Execute SQL
]
In my ScriptTask, i am checking the file validity. Now if the file is found incorrect, I do not want the DataFlowTask and other to execute. Instead i want the file to be moved to Error and the loop to proceed with the next file. If the file validity is correct, DFT and ExecuteSQL execute. How do i do this?
Some explanation supported by examples would help.
Lalit
Hi guys,
I have a column value like MPROW0016
M is for monthly/yearly
PRO 3 character make account type of the sub
W is web
0016 - id
here i neet to split this COLUMN USING SCRIPT TASK IN SSIS
plz help me how to do this .
I have a ScriptTask, in the designer I've given it a name. Is there a way at runtime to pick up that name from within the code in the ScriptTask?
I have a feeling I'm overlooking something painfully simple here. Inside a Script Task, I'm trying to fill a DataTable with a recordset variable.
oledbAdapter.Fill(dataTable, Dts.Variables("rsSrcTblNames"))
...doesn't work, because Dts.Variables("rsSrcTblNames") is a System.Object, not an ADODB recordset. Sure, whatever. Most of the examples I see online use Me.Variables.rsSrcTblNames, as in:
oledbAdapter.Fill(dataTable, Me.Variables.rsSrcTblNames)
That also doesn't work, which makes less sense to me. The auto-correct highlights "Me.Variables", giving the message "'Variables' is not a member of ScriptTask_34dgf89sghg9a8h...". I guess this makes sense given that I'm in a Script Task, and apparently the syntax I'm trying to use is for Script Components (http://msdn2.microsoft.com/en-us/library/ms136031.aspx). But I'm at a loss how to proceed. Any suggestions would be appreciated.
(more detailed description of situation and code will be posted below)
Hi. I've tried several things to log some information inside of the ScriptTask. I've tried Console.WriteLine(), Debug.WriteLine(), and Dts.Log().
All compile and work, but my problem is I can't find the output to save my life.
I have logging turned on in my SSIS package, but the data doesn't show up in that file. I've check SQL Server's log. Event Log in OS. All no luck.
Please help.
I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.
Hi All,
In one of my ScriptTasks, I instantiate FileSystemWatcher class and set events for it. This has been done inside the Main() method. I can access all the varibles declared in the SSIS package inside the Main() method (by using Dts.Variables("").Value) but none can be accessed inside the event methods. One possible reason for this can be, events might be running in different threads.
Now my question is, How the varibles can be accessed inside the event methods.
Hope someone can give me a solution. Appricate all your solutions.
Thanks
I have created a task that uses a ForEachFile Loop. Within that loop I load 5 files. Once those 5 files have loaded successfully I want to run a stored procedure. I placed this stored procedure into a script task. The script task has 5 precedence constraints,. They are a success on the load of the files. When I run the task, the 5 files load, but the script task will not start. I have gone so far as to check each load individually to see if it would start the script task, which they do. It's only when I add more than 1 precedence constraint that the script task will not run. The precedence constraints are set to AND, so they all are required. They all succeed. What am I doing wrong?
View 3 Replies View RelatedI need a library for Microsoft.SqlServer.Dts.Tasks.ScriptTask. How do I go about getting this.
Basically I am trying to execute the following code to verify my SSIS ran correctly.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
namespace Package_API
{
class Program
{
static void Main(string[] args)
{
Package p = new Package();
p.InteractiveMode = true;
p.OfflineMode = true;
// Add a Script Task to the package.
TaskHost taskH = (TaskHost)p.Executables.Add("STOCK : ScriptTask");
// Run the package.
p.Execute();
// Review the results of the run.
if (taskH.ExecutionResult == DTSExecResult.Failure || taskH.ExecutionStatus == DTSExecStatus.Abend)
Console.WriteLine("Task failed or abended");
else
Console.WriteLine("Task ran successfully");
}
}
}
Hi,
I developed an SSIS package in which i declared a global variable"FileName" . In script task of SSIS I am assigning some value to that global variable"FileName".
Now, In script page of "ScriptTaskEditor" either I declare global variable"FileName" as ReadOnlyVariable or as ReadWriteVariable, In both conditions i am able to get the new assigned value from global variable"FileName".
So, my question is that, Here what is the difference between "ReadOnlyVariable " and "ReadWriteVariable", since i am
getting result from both and what declaration should I use ?
Code line is:
Dts.Variables("FileName").Value().ToString = "LatestFile"
I have been developing a large SSIS project and it uses scripts extensively.
During development, i encountered no problems.
However, when i tried executing them on a server (windows server 2003), i was given this error for all the scripts:
Here is a brief log:
PackageStart,servername,networklogin,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:26 PM,3/10/2006 5:24:26 PM,0,0x,Beginning of package execution.
OnError,servername,networklogin,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Variables' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 26 Columns 51-63
Line Text: Dim DaysToKeepErrorFile As Integer = CInt(Dts.Variables("vDaysToKeepErrorFiles").Value.ToString)
OnError,servername,networklogin,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 49 Columns 25-31
Line Text: Dts.Log(MaintenanceMsg, 0, ZeroByte)
OnError,servername,networklogin,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 55 Columns 17-23
Line Text: Dts.Log("Script Error:" + ex.Message, 0, ZeroByte)
OnError,servername,networklogin,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,5,0x,The script files failed to load.
PackageEnd,servername,networklogin,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,1,0x,End of package execution.
I've been using the same scripts for months in development and testing. Both dev and test server has SQL 2005 and SSIS installed.
What causes this?
I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.
View 15 Replies View RelatedI'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
What would the code look like?
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
Any ideas, thoughts, resources are all welcome.
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
View 6 Replies View Relatedhi all... how can i change the collation of a table that i dynamically created during runtime??i wanna change the default collation to Arabic
View 2 Replies View RelatedHi ,Can we make a backup from a SQL-server 2000 database programmatically, using asp.net and then restore this backup programmatically ?
View 4 Replies View RelatedHi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically?
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Hi,
I am using Visual Web Developer 2005 Express Edition.
I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example).
How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @resortid". How do I programatically set it up so that @resortid contains the value that is associated with "Articleid"?
My code is below.
Thank you for your advise!
RegardsJan/*******************************************************************************
* RETRIEVE INFORMATION FROM DATABASE
*******************************************************************************/
// specify the data source
string connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString;
SqlConnection myConn = new SqlConnection(connContStr);
// define the command query
String query = "SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @resortid)";
SqlCommand myCommand = new SqlCommand(query, myConn);
// open the connection and instantiate a datareader
myConn.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
// loop thru the reader
while (myReader.Read())
{
Label5.Text = myReader.GetInt32(0).ToString();
Label6.Text = myReader.GetInt32(1).ToString();
Label7.Text = myReader.GetInt32(2).ToString();
}
// close the reader and the connection
myReader.Close();
myConn.Close();
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.
heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter
PUser.ControlID = "lblUser"
PUser.Name = "LoginName"
PUser.PropertyName = "Text"
PUser.Type = TypeCode.String
PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource
SQLDS_Login.ID = "SQLDS_Login"
SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?"
SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)"
SQLDS_Login.SelectParameters.Add(PUser)
SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
GridView1.DataSource = SQLDS_Login
GridView1.DataBind()
End Sub
When i run, i get this error message:
The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.
I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.
Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
View 1 Replies View RelatedI am trying to customize my update statement and this MUST happen in codebehind, otherwise I will be overwriting data. The following is updating the data that is should be. The problem is that eventhough my UpdateCommand is clear in my .aspx and there are no parameters set... It is STILL running it's own update and overwriting the information it isn't supposed to. From what I can tell, it is using a default. What can I do to prevent this?
SqlDataSource1.UpdateCommand = "UPDATE MyTable SET MyField1=@MyField1 WHERE MyField2=@MyField2 AND MyField3=@MyField3"SqlDataSource1.UpdateParameters.Add("MyField1", "CustomText")
SqlDataSource1.UpdateParameters.Add("Task_ID", "Parameter")SqlDataSource1.UpdateParameters.Add("Comments", "Parameter")
SqlDataSource1.Update()
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted.
What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])
Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?
Thank you very much for your help.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Is there a way to schedule tasks to run from an ASP script? If not ASP, how about VB? What function is called if any? Thanks!
View 1 Replies View RelatedHowdy,
Is the any easy way to clone a database programatically?
I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database)
and create tables, indexes, etc. via asp. (The databases are always exactly
the same)
It seems to me that it would be a better option to
create one database with tables, indexes, etc and copy
it to a new database (on the same server).
One of the options I'm considering would be to create a database,
fill it with tables and indexes, etc. and then detach it.
Whenever I need a new database all I would have to do is use the
file system object to copy and rename the mdf file and then reattach the
new mdf file.
I realise that I could also make the changes to the model
database so that all new databases have the same structure
but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
You're opinions and advice would be welcomed.
I recently had to find ways to programmatically backup and restore anSQL Server Express database.For backup, I found a one-liner stored procedure that does the job(BACKUP DATABASE Toy to disk = 'c:ProjectsToy.bak'.My question is, is there something as simple for a restore operation?Thanks in Advance-Kostas
View 4 Replies View RelatedHi,The following script does not return any resultset against a test dbwhile I know for a fact tables with letter "aaa" has columns thatcontains "ccc".What's wrong? the the inner cursor?Thanks.-- get all tbls with letter aaadeclare @tbl varchar(8000)declare tblCursor cursor forSELECT nameFROM sysobjectsWHERE xtype = 'U'AND name LIKE '%aaa%'open tblCursorfetch next from tblCursorinto @tblwhile (@@fetch_status = 0)begin-- get all columns with letter ccc and replace it with nothing /remove itdeclare @tbuffer varchar(4000)declare @cbuffer varchar(8000)declare abnormal_cols cursor forSELECT o.name, c.nameFROM sysobjects oJOIN syscolumns c ON o.id = c.idWHERE o.xtype = 'U'AND c.name LIKE '%ccc%'and o.id = object_id('+@tbl')-- ORDER BY c.nameopen abnormal_colsfetch next from abnormal_colsinto @tbuffer,@cbufferwhile (@@fetch_status = 0)begin-- EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%ccc%','')','COLUMN';-- testprint @tbuffer + ', ' + @cbuffer;fetch next from abnormal_colsinto @tbuffer,@cbufferendclose abnormal_colsdeallocate abnormal_cols;fetch next from tblCursorinto @tblendclose tblCursordeallocate tblCursor;
View 1 Replies View RelatedHi!
I have a rdl file and would like to publish it programmatically? Is there any API or Web Service available for this.
Does anybody know can you programmatically instantiate a control at run-time?
I need to make a dynamic number of images appear on a report and programmatically grab an image name based on data in the report.
vbwrangler@yahoo.com