I want to pass a dataset to my .net script task as an object. I know how to declare an Obect variable and map the result set of my Execute SQL task to the Object variable lets say tbl1. Then on the Advanced Menu of .Net Script task I input tbl1 as my ReadOnlyVariable. But once in the design view of the .Net Script I am unable to get and work with every row of the my tbl1 variable.
View 5 RepliesHi Friends,
I am having a Package scope variable DS of type system.object .In the Script Component which accept this variable as ReadWrite type I am Making the following assignment.
Public Overrides Sub PostExecute()
Variables.DsReport = DsReport // DsReport is a Data Set Object Created and Populated inside the script Component
End Sub
I am getting error while doing this sort of assignment.
Please help me to solve this problem
Regards,
Mahe
I am trying to execute this code feom Script task while excuting its giving me error that "Object reference not set to an instance of an object." The assemblies Iam referening in this code are there in GAC. Any idea abt this.
Thanks,
Public Sub Main()
Dim remoteUri As String
Dim fireAgain As Boolean
Dim uriVarName As String
Dim fileVarName As String
Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection
Dim emptyBytes(0) As Byte
Dim SessionID As String
Dim CusAuth As CustomAuth
Try
' Determine the correct variables to read for URI and filename
uriVarName = "vsReportUri"
fileVarName = "vsReportDownloadFilename"
' create SessionID for use with HD Custom authentication
CusAuth = New CustomAuth(ASCIIEncoding.ASCII.GetBytes(Dts.Variables("in_vsBatchKey").Value.ToString()))
Dts.Variables(uriVarName).Value = Dts.Variables(uriVarName).Value.ToString() + "&" + _
"BeginDate=" + Dts.Variables("in_vsBeginDate").Value.ToString() + "&" + _
"EndDate=" + Dts.Variables("in_vsEndDate").Value.ToString()
Dim request As HttpWebRequest = CType(WebRequest.Create(Dts.Variables(uriVarName).Value.ToString()), HttpWebRequest)
'Set credentials based on the credentials found in the variables
request.Credentials = New NetworkCredential(Dts.Variables("in_vsReportUsername").Value.ToString(), _
Dts.Variables("in_vsReportPassword").Value.ToString(), _
Dts.Variables("in_vsReportDomain").Value.ToString())
'Place the custom authentication session ID in a cookie called BatchSession
request.CookieContainer.Add(New Cookie("BatchSession", CusAuth.GenerateSession("EmailAlertingSSIS"), "/", Dts.Variables("in_vsReportDomain").Value.ToString()))
' Set some reasonable limits on resources used by this request
request.MaximumAutomaticRedirections = 4
request.MaximumResponseHeadersLength = 4
' Prepare to download, write messages indicating download start
Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", _
Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), String.Empty, 0, fireAgain)
Dts.Log(String.Format("Downloading '{0}' from '{1}'", Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), 0, emptyBytes)
' Download data
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)
' Get the stream associated with the response.
Dim receiveStream As Stream = response.GetResponseStream()
' Pipes the stream to a higher level stream reader with the required encoding format.
Dim readStream As New StreamReader(receiveStream, Encoding.UTF8)
Dim fileStream As New StreamWriter(Dts.Variables(fileVarName).Value.ToString())
fileStream.Write(readStream.ReadToEnd())
fileStream.Flush()
fileStream.Close()
readStream.Close()
fileStream.Dispose()
readStream.Dispose()
'Download the file and report success
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
' post the error message we got back.
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:
"Object reference not set to an instance of an object"
Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:
1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:
create proc usp_testsp
as
begin
select 'whatever' ;
end
2. I create a new SSIS package in BIS.
3. I create an ADO.NET connection to the above SQL 2005 database
4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.
5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None
When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.
And here's what I get with ResultSet set to Single row:
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.
Thanks,
Michael
i have some code in a script task component which is meant to find a cell in an excel sheet and assign a variable to its value in the script component. I receive an error that the object is not set in instance of object. below is the code which i tried to simplify to find the error, but it is still occurring. any help would be appreciated. thank you
Dim vars As IDTSVariables90
vars.Unlock()
Me.VariableDispenser.LockForWrite(Variables.freq)
Variables.freq = "1"
Hi all,
Does anyone see the error below before?
I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.
It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY
€œObject Reference Not Set to An Instance of an Object€? when I click on PARSE QUERY.
This is going against SQL SERVER 2005 SP2 x64 Enterprise.
Note that this task executes fine and the stored procedure updates data.
The stored procedure does the following.
There are other stored procedures of different kinds and they all worked.
But all of them give this error when I click on PARSE QUERY.
Code Snippet
DECLARE @TodayDate datetime
SET @TodayDate = GETDATE()
Exec dbo.updDimBatch
@BatchKey = @BatchKey,
@ParentBatchKey = @ParentBatchKey,
@BatchName = 'Load Customer Increment',
@BatchStartDate = NULL,
@BatchEndDate = @TodayDate,
@StatusKey = NULL,
@RowsInserted = @Count_Insert,
@RowsUpdated = @Count_Update,
@RowsException = NULL,
@RowsError = NULL,
@UpdatedDate = @TodayDate,
@BatchDescription = NULL
OLEDB Sample also give me syntax error
exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''
I tried to change to OLEDB and call the stored procedure like this but got syntax error?
Not sure what is the error here.
Is it possible to pass the dataset dynamically in ssrs???
I am using sqlserver express edition at present. so in future it may change. is it possible to change the dataset in future or not???
I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.
How can I do this?
I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.
HI All,
Is there any way to pass report dataset to Custom function as object?
Regards
Sith.
I hit this error when I run the FTP task. I set IsRemotePathVariable = TRUE and RemoteVariable = User::FTPSourcePath where the variable is set with //DMFTP//PE1.JPG in the script task prior to FTP task.
IsLocalPathVariable also set to TRUE and LocalVariable = User::FTPTempPath where the variable is set to c:BiztalkFTPTemp
The FTP Operation is set to Receive Files.
I have tested the task with IsReportPathVariable to False and it works fine.
Can anyone help me and provide some advise on this? Thank you.
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
In the dataset, I typed
=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")
I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.
Thanks.
I've been using the asp.net's DataSet and it seems to be leaving connections open, I'm not entirly sure if its due to the DataSet or some random code I forgot to close its connection, but I just wanted to cover all of my Basis. Is it possible though, that this DataSet object is leaving connections open?
Thanks for reading!
-Teenprogrammer
I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:
Page 1:
string sql = null;
conn.strConn = connectionstring;
sql = "sqlstring";
SqlParameterCollection newcollect = null;
newcollect.Add("@Switch",1);
conn.OpenReader(sql, newcollect);
while (conn.DR.Read())
{
read data onto page here...
}
conn.CloseReader();
Page 2 (connection class) :
public void OpenReader(string sql, SqlParameterCollection collect)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(sql,Conn);
Command.Parameters.Add(collect); <------This is the root of my question
Command.CommandTimeout = 300;
// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}
Can you do this??? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.
I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?
Thanks!
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.
.
.// read value from row.
Hello All
I am trying to run a simple password reminder email and this is a call into a sql databse to try and get the value of a cell. This is however giving me an error of Invalid object name ''Captains'' where in the code below the Table is called Captains. I was getting an error to say the email was an invalid column but from another post on this forum this seemed to be linked to the quotes and I am not convinved this is not a similar problem but any assistance anyone can make before I throw my machine out of the window would be much appreciated.
comm.CommandText = String.Concat("SELECT * FROM ['" + Table + "'] WHERE EmailAddress = " + Email);
conn.Open();DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(comm);
adapter.Fill(ds);
I am getting the error on the adapter.Fill(ds); Invalid object name ''Captains''
hi i dont know how to do type casting.
this.Variables.ObjVariable this objVariables i have create in variable place below like this
Name:Variable datatype int32 values 0
Name: NumberofRowsdatatype int32 values 10000
Name: ObjVariable datatype Object
My code
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();
//System.Data.Odbc.OdbcDataAdapter oLead = new System.Data.Odbc.OdbcDataAdapter();
//SqlDataAdapter oLead = new SqlDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
DataSet ds = (DataSet)this.Variables.ObjVariable; // here i am getting error
ds.Tables.Add(dt);
ADODB.Record rs = new ADODB.Record();
oLead.Fill(ds, rs, "Variables");
foreach (DataRow row in dt.Rows)
{
{
Output0Buffer.AddRow();
Output0Buffer.Column = (int)row["Column"];
Output0Buffer.Column1 = row["Column1"].ToString();
Output0Buffer.Column2 = row["Column2"].ToString();
}
}
}
}
This is the error
Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
thanks
kedaranth
Hi friends,
The problem that i am facing right now is that I have to show progress bar in my vb front end Application code which call my package using the
Application.LoadPackage(pakage Nothing).
What i am intending is that , I pass my progress bar instance to Package object and as the package executes, I
am changing the progress bar value to reflect the progress of execution.
Please Help ME.. with ur valuable responses
Regards
Maheswar
In the XML Task if you set the OperationType to XSLT, how do you pass arguments to the Transform like you would in .Net by using the XsltArgumentList class? Thanks.
View 3 Replies View RelatedHow do I pass the results of a sql 'FOR XML' query to an XML Task? I tried using a user variable of type string. In my Exec SQL Task on the General tab I set the ResultType to XML. Then on the Result Set tab I map it (Result 0) to my user variable (string). Then in the XML Task I use that variable as the Source. However, it fails in the Exec Sql Task with error
"End tag 'CustomJob秱å’秱怈пField' does not match the start tag 'CustomJobField'
It looks like some extraneous chars are getting inserted into my tags?
I also tried setting the ResultType to SingleRow but that failed with error:
"The type of the value being assigned to variable "User::ForXmlResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
Any ideas? Thanks.
I am trying to execute an SQL task in a ForEach container that is the table name for a select statment I want to execute (table name is passed from a rowset created before the ForEach container).
ForEach Container is looping correctly (I've used a watch to make sure each of the table names in the previous rowset are being passed correctly). I want to use the table name to execute the following query:
Select count(*) as TableRowCount from MyLinkedServer...?
(with ? as the table name)
Problem I am having is that the SQL Task Editor keeps placing a space between the last period and the parameter and then telling me it cannot parse the SQL.
I tried:
Declare @SQL varchar(255)
Set @SQL = 'Select count(*) as TableRowCount from MyLinkedServer...' + ?
Exec(@SQL)
Same error as above. My parameter is varTableName
If I used the Declare and hardcode the table name (i.e. MyLinkedServer...MyTable), and it works fine. Just doesn't seem to want to allow the table name as a parameter...
Thoughts, ideas??? Am very new to SQL 2005 but did the above with DTS... Thank you for any assistance...
Danno
Hello,
I am new to SSIS so I'm sure this is easy to do but I can't seem to figure it out. I created a SQL task that creates a result set. I would like to loop through each result of the result set and insert it into another table.
I'm not sure how to map the result set as input to the second SQL task that will do the insert. I can put the variable in the parameter mapping but I don't know what the SQL should look like to insert this into my table. Each row of the result set has five columns. Normally I have something that looks like INSERT...Values(?,?,?,?,?) but because my input parameter is just one result set object this doesn't work.
Help?
How can I pass a variable to a DataReader in a DataFlow task?
My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'
The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.
I have a DTS package of SQL server 2000 which has two "Execute SQL Task", which gets maximum ID (call it maxID1) from table1 in mysql and maximum ID (call it maxID2) from table 2 in sql server. Those 2 as global variables passes into a data flow which grabs all data from mysql's table1 with its ID > maxID2 and ID <= maxID1 and insert into SQL server table3. This package is working so far.
My question is how to achieve the same thing in SSIS? I created two "Execute SQL Task", which will get maxID1 and maxID2, and one "Data Flow Task". But how to pass those 2 parameters into that "Data Flow Task" without using "Script Task"?
Anyone could help? Thanks!
Exist any way to pass custom user and password info to the Send Mail task using the connection string property?
Hi all--I am writing a DTS package looking to back up all databases in sequence from a specific instance of SQL Server. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of SQL tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure of a particular database to back up to file, execute the following SQL task:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
SELECT @srvname = @@servername
select @db_name = db_name()
set @msg = 'Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
SELECT @srvname, @db_name, @msg
(Many thanks to Whitney Weaver and SQL_Menace for help with sections of this code)
Upon success of #1 and/or #2, go on to the next database, repeating cycle of the SQL tasks above for the next database in the task. For example, this logic goes like:
1. Back up master database; if fails, send email in SQL task; if either task is successful, goto database #2;
2. Back up model database; ...<etc>.
My problem is the current database set in @db_name. I apparently need to reset this variable for each database that gets backed up, and I can't seem to get these set as values to be passed from SQL Task #1 to SQL Task #2. Any suggestions on how I might achieve this?
- Jonathan
Hi, firends,please look at this:
Prepare:
1.prepare a SQLTask component, and read a table by it, return the result in an Variable Object(RegionDim).
2.Pass the Variable into a Taskscript component. In the Script, write this:
Public dtRegion As New DataTable
Public daRegion As New OleDb.OleDbDataAdapter
daRegion.Fill(dtRegion, Dts.Variables("RegionDim").Value)
3.Use dtRegion in the script. It's OK.
Problem:
After the TaskScript, the RegionDim is be cleared in fact. So I can't use it in other places, and need to read the table again.
I think the "daRegion.Fill(dtRegion, Dts.Variables("RegionDim").Value)" mark the RegionDim as the dtRegion, and the dtRegion is a variable scoped inside the Script, which will be distroyed after the Script. So the RegionDim is cleared.
How to avoid this?
Thanks.
Ivan
I'm having a bear of a time with this. I have a recordset that pulls data from one, single table. I would like to iterate through the set, do some processing based on that data, then delete all of the records in that recordset.
I'm trying to do something like the following:
Set dbConn = CreateObject("ADODB.Connection")
Set dbRS = CreateObject("ADODB.Recordset")
dbConn.Open("myConnectString")
dbRS.open dbSQLCmdText, dbConn, 2, 4
While Not dbRS.EOF
Do some stuff
dbRS.Delete
Wend
dbRS.BatchUpdate
dbRS.Close
dbConn.Close
I get an error stating:
"Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
This seems to indicate the connection doesn't think it's capable of reconciling the recordset. Is there a way to do this in one batch or should I stick with executing a single delete statement for every record in my recordset?
Dear All!
I use a "Execute SQL Task" to call a Stored Procedure. My Stored Procedure have an input parameter with type: ntext. And in "Execute SQL Task", I set variable in Parameter Mapping as following:
Variable Name: User::xmlDocument (this variable is a String to store xml data)
Direction: Input
DataType: NVARCHAR
ParameterName: 0
When length of "User::xmlDocument" is too large then error is happen but on the contrary, "Execute SQL Task" run successfully.
So, Can you show me how to pass large data into stored procedure using "Execute SQL Task"?
I am looking forward to hearing from you
Thanks
Hi All,
I have One package that it contains one Execute SQL task in that i have placed a Stored procedure .
Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task "
How can i pass that table column values to that stores procedure thru using Script Task?
Regards,
Maruthi..
I have a ForEachLoop parent pkg that reads thru the file system of dtsx to be executed.
I would like to pass CLI parms to the command itself (dtexec). I'm not looking at passing values to the child pkgs but rather additional command line values such as:
/CONFIGFILE "C:myconfig.dtsConfig.
In my expression builder for the PackageName I added the additional string but during the execution, the reference to my dtsconfig is ignored without any errors and the child pkg runs using the default values provided at design time.
@[User::PackageToRun] + " /CONFIGFILE C:\myconfig.dtsConfig ".
How does one add additional CL values during the runtime for Execute Package Task?
Thanks,
Anatole
Got a powershell script to split a large XML file to split in smaller chunks. I have Execute ProcessTask in SSIS with:Â
Executable: %windir%system32WindowsPowerShellv1.0powershell.exe
argument:Â -ExecutionPolicy ByPass -command ". 'C:WorkspacesSplitToytPMFile.ps1'"
I need to pass File Name as parameter to the PS script. I tried using the StandardInputVariable but it doesn't work.Â
Hi
Don't know if this is possible or not - Need to produce a report and the main report has a grid in it.
For every row in the main report, in one of the grid fields there are n images. So to do this, I am using a sub-report. But there is a problem with going to the database for every row to fetch images that if the user does a report spanning more than about 6 months, there will be thousands of records each with the potential to have many images and the constant round trips to the db server would probably kill it.
So what I want to achieve is to fetch the dataset in the main report with just one trip to the database and then pass the dataset filtered by a uniqueidentifier to each row of the subreport.
Could someone please let me know if this or something like it is possible and how to go about it?
Thanks
Steve
Hi,
i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB.
i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description.
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR :
errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is
available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP
Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Thanks,
Haroon