Passing In Current Database Name To DTS-based SQL Task?
Sep 28, 2006
Hi all--I am writing a DTS package looking to back up all databases in sequence. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure, send the following message:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
select @srvname=srvname from master.dbo.sysservers
print @srvname
select @db_name=name from master.dbo.sysdatabases
print @db_name
set @msg='Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
Upon success, go to the next database to back up, execute through these steps for the next database. Here's my problem: The @db_name variable captures the proper value for the current SQL Server, but I want to turn both @srvname and @db_name into global variables. I would like to pass in @db_name as the current database being backed up (and failing), and @srvname as the current SQL Server instance being backed up.
The above statement "select @db_name=name from master.dbo.sysdatabases" definitely does not capture the current database name value. Any suggestions as to how to pass this in, if not as a global variable?
Thanks in advance,
Jonathan
View 2 Replies
ADVERTISEMENT
Sep 28, 2006
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
View 1 Replies
View Related
Oct 23, 2007
Hi All,
His there any way to build a connection string to sql server based on the current user credential?
I mean, intead of using
user id=Adminpassword=adminPass
Is it possible to do something like
user id= Context.User.IDpass=???
I'm asking cause, iI don't want to use impersonisation in my code.So that I can be sure logged user only sees what they can
(I'm a newbie, so this whole thing may makes no point(thanks for clarifying
View 4 Replies
View Related
Sep 3, 2014
I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.
ACCT_ID ACCT_NMORD_NBRORD_DT ORD_AMT_USD
595709114ASDASD444447/28/2014 546
2224809440ASDASD444445/2/2012 546
1724031572ASDASD444446/22/2011 546
1702887651ASDASD444447/3/2014 546
1724020508ASDASD444447/16/2012 546
1148151895ASDASD444449/18/2013 546
2125154824ASDASD444449/2/2014 546
1503552723ASDASD4444412/20/2011 546
2224689808ASDASD4444410/4/2010 546
931387698ASDASD4444412/31/2010 546
View 4 Replies
View Related
Feb 5, 2008
I am trying to match records that are >= the current date. I have tried using:
SELECT DISTINCT name
FROM table
WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE())
ORDER BY name
but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?
View 5 Replies
View Related
Jul 28, 2014
I need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
View 1 Replies
View Related
Dec 8, 2006
Hi everyone
I'm trying to write a monitoring application for SSIS packages deployed on my machine. I know I can look at running packages via the DtsRuntime.Application object's GetRunningPackages method. Does anyone know if there is any way one can view tasks in that package that are currently being executed?
Cheers
Sachin
View 4 Replies
View Related
Aug 18, 1998
Hi!!
If I have declared a variable, such as char variable_name[x], and have assigned it a value, how do I go about passing this value to an SQL query such as:
SELECT * FROM variable_name
where my value replaces variable_name.
Cheers, Marc
View 1 Replies
View Related
Sep 30, 2015
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies
View Related
Dec 14, 2006
Hi,
I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.
I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.
So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?
I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.
I would also welcome alternative suggestions for how to achieve this.
Many thanks,
Keith.
View 10 Replies
View Related
Feb 11, 2015
I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.
create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.
View 2 Replies
View Related
Sep 12, 2007
I have a main report and a subreport. Subreport is not at row level but is at report level. The sub report has a parameter that needs to be passed from mainreport. However, the subreport's parameter should be set and the subreport should be refreshed when the user clicks on a row in mainreport. Can I use drill-through / navigation feature in any way to build this functionality?
Any help will be most appreciated.
View 3 Replies
View Related
Feb 20, 2006
Hello,
I have a SQL task wich executes the following statement on a OLEDB connection (SQL 2005 DB):
TRUNCATE TABLE DimTime
GO
DECLARE @CurrentDate AS Datetime
DECLARE @EndDate AS Datetime
SET @CurrentDate = '10-20-2003';
SET @EndDate = '10-20-2005';
while(@CurrentDate < @EndDate)
begin
INSERT INTO DimTime SELECT
DATEPART(month, @CurrentDate) AS MonthNumberOfYear
,DATEPART(quarter, @CurrentDate) AS CalendarQuarter
,DATEPART(year, @CurrentDate) AS CalendarYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester
,DATEPART(quarter, @CurrentDate) AS FiscalQuarter
,DATEPART(year, @CurrentDate) AS FiscalYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester
set @CurrentDate = dateadd(month, 1, @CurrentDate)
end
GO
Now I want to parametrize the statement creating an external variable:
1. I created a datetime package variable (DimTimeStartDate)
2. Add update the above SQL statement to
SET @CurrentDate = ?;
3. I mapped the package variable to a new parameter (name 0)
Executing I get the following error:
[Execute SQL Task] Error: Executing the query " DECLARE @CurrentDate AS Datetime DECLARE @EndDate AS Datetime SET @CurrentDate = ?; SET @EndDate = '10-20-2005'; while(@CurrentDate < @EndDate) begin INSERT INTO DimTime SELECT DATEPART(month, @CurrentDate) AS MonthNumberOfYear ,DATEPART(quarter, @CurrentDate) AS CalendarQuarter ,DATEPART(year, @CurrentDate) AS CalendarYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester ,DATEPART(quarter, @CurrentDate) AS FiscalQuarter ,DATEPART(year, @CurrentDate) AS FiscalYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester set @CurrentDate = dateadd(month, 1, @CurrentDate) end " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Not clear what is wrong. Any help ?
Thanks,
Pierre
View 12 Replies
View Related
Nov 5, 2007
Hi
The genereal Properities of my SQL Task are
ResultSet : None
Conection Type : OLEDB
SQLSourceType : Direct Input
SQL Statement :
Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?
BypassPrepare : True
Parameter Mapping
Variable Name Direction Data Type Parameter Name
User::Variable2 Input LONG 0
When executing the SSIS Package I get the Following Error
SSIS package "Test.dtsx" starting.
Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query
"Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Company ID and Name
SSIS package "Test.dtsx" finished: Success.
Please Help
Many Thanks In Advance
Que
View 6 Replies
View Related
Aug 28, 2007
how can you pass variables from one 'Execute SQL Task' to another?
View 9 Replies
View Related
Mar 20, 2008
Hi All,
I have a Doubt in Parameter Passing in Execute SQL Task.
In Execute SQL Task, I have an Insert query in which I want to pass the Database Name Dynamically i.e. passing it as a parameter.
Eg.
The query is --------- Insert into [?].[dbo].[DimCurrency] values( value1, value2, value3)
I want to pass this Database name using a user variable. But I am not able to do so.
What is the catch in this, Can anybody please help me out.
Thanks & Regards,
kapadia Shalin P.
View 5 Replies
View Related
Sep 25, 2006
I am trying to exectue SQL task as below by passing a parameter
If I try....
@v1 datetime
set @v1 = convert(datetime, ? ,103)
it fails with below error
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
however the below code works well
delete from t1 where last_update = convert(datetime, ? ,103)
What could be the problem?
View 4 Replies
View Related
Jun 23, 2015
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table
Sample Data:
SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.
View 6 Replies
View Related
Aug 16, 2004
In my DTS package, is there any way to pass the task name and error description to another task that gets called on the task's failure?
View 1 Replies
View Related
Jun 11, 2008
Hi,
I am creating SSIS package which using Execute SQL Task to which I am supplying one .sql file code is
delete from Test where ID = @ID
I defined @ID variable to SSIS and also path where .sql file placed but i am not able to execute this package i am getting error like can not find C:@Path file......
As i got information that passing such variable to .sql you need ADO.net connection so I changed SQLSERVER Database connection string to ADO.net .... after that when i set hard core value for these variable i.e for @ID and @Path then it runs sccessfully but by setting parameter i am getting above error
Any suggestion that will be gr8 help for me
T.I.A
View 2 Replies
View Related
Mar 29, 2007
Hi,
am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.
Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.
Variable:
Variable Name = Subsystem
Scope= Package
Value = pgc
Data Type = string
SQL:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?
Have also tried:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0
Result Set = Single Row
Parameter Mapping:
VariableName = User:ubsystem
Direction = Input
Data Type=Varchar
Parameter Name= 0
Parameter Size= -1 (have also tried 3 - length of variable)
Oracle Table:
SQL> desc sys_subsystem
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSYSTEM_ID NOT NULL CHAR(3)
SUBSYSTEM_DS NOT NULL VARCHAR2(40)
....
....
...
The Error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".
I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?
SQL:
UPDATE sys_subsystem
SET as_process_fg = 'X'
WHERE subsystem_id = ?0
The parameter mappings are the same as above.
Any assistance here would be much appreciated.
Thanks
Mick
View 5 Replies
View Related
Aug 6, 2015
In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)
[code]...
View 6 Replies
View Related
Apr 26, 2008
I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.
Dan
View 6 Replies
View Related
Aug 27, 2007
Hello,
Newbee here
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector)
DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is
Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
Thanks
View 5 Replies
View Related
Nov 24, 2006
Microsoft says it is possible but I just do not see how. Here is the
link to the help file where it said that variables could be pass as
input to web methods...I do not see the check box they mention on my
IDE.
Any help would be greatly appreciated.
Thanks,
Catherine
View 4 Replies
View Related
Oct 12, 2007
Hi, this might be a simple one, but I have been stuck on it for days. I am just getting into SSIS and have been muddling through it for the rest of this package but I am stuck on this. I am using this SQL Execute Task to run some lookup queries and then call the sp_send_dbmail stored procedure. I have this placed in a For Each loop container. I am using a SELECT DISTINCT Branch FROM table1 into an Object parameter, and passing that into the loop container. I am then using a Input Parameter into this SQL Execute Task of type String. I have run a Script task right before this step to ensure that the variable is populated and correct. Any assistance would be greatly appreciated!
Here is the SQL Execute Task :
DECLARE @SQL varchar(2400), @emaillist varchar(200),
@branchMgrEmail varchar(100), @officeMgrEmail varchar(100),
@branchMgrEmpNo varchar(5), @officeMgrEmpNo varchar(5), @subjectline varchar (100),
@Today varchar(10), @BranchNumber varchar(2)
SET @BranchNumber = ?
SET @Today = convert(char(8),getdate(),1)
SET @SQL = 'SELECT rtrim(CONVERT(char(10), PostedDate, 101)) AS Posted_Date,
CAST(Branch AS CHAR(2)) AS Branch,
CAST(Department AS CHAR(2)) AS Department,
CAST(InvoiceNumber AS CHAR(7)) AS Invoice_Number
FROM onbase.dbo.MHC_IncompleteRepairOrders
WHERE Branch = ' + @BranchNumber +
'AND HardCardCount = 0
AND WorkAuthCount = 0
AND QualityControlCount = 0
AND MiscDocsCount = 0'
SET @subjectline = @Today + ' - Repair Order Validation Notification for Branch #' + @BranchNumber
SET @branchMgrEmpNo = (SELECT branchempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @officeMgrEmpNo = (SELECT officeempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @branchMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @branchMgrEmpNo)
SET @officeMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @officeMgrEmpNo)
SET @emaillist = @branchMgrEmail + '; ' + @officeMgrEmail
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@email.com',
--@recipients = @emaillist,
--@copy_recipients = 'email@email,
@attach_query_result_as_file = 0,
@subject = @subjectline,
@body = 'This email contains a list of Stuff
',
@query = @SQL,
@query_result_header = 1,
@query_result_separator = '|',
@query_result_width = 150,
@exclude_query_output = 1,
@profile_name = 'MAIL'
END
This is a copy of the ERROR message that I am receiving. Any suggestions?
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Send email to Branch Mgr and Office Mgr
View 11 Replies
View Related
Oct 18, 2006
Hi!
I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:
Variable name: User::serverName
Direction: Input
DataType: Varchar
Parameter Name:0
and the sql is the following:
CREATE LOGIN [?ASPNET] FROM WINDOWS
But I get the error:
Executing the query "CREATE LOGIN [?ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am I doing wrong?
Thank you!
View 10 Replies
View Related
Apr 28, 2004
I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?
View 6 Replies
View Related
Feb 23, 2007
I need a little help here and appreciate any insight into this issue.
I am building an SSIS package that retrieves data from a database to use in a web service task. So let me give you a little more broad overview of the package so you can understand how this is supposed to roll. A database is queried and those values are dumped into a recordset. A foreach loop uses each row of variables to call the web service and dump the returned values to another database. The first database holds a bunch of fields, but the four fields of interest are: a StartDate (DateTime), a StartFormat (single char), an EndDate (DateTime) and an EndFormat (single char). The output from the query of the first database is the input in the signature of a web service's .Load method. Sounds easy, right? Sure, why not?
Well I dragged the Web Service Task on to the pane and took a look inside. Lo and behold, I can hardcode the variables in for the web service or I can assign the inputs to package variables. How fancy, thanks Microsoft!
But that's where the difficulty begins. The method call for the web service looks like this: service.Load(string, int, GTTimestamp1, GTTimestamp2). The web service is expecting a string, an int, and two objects of the type GTTimestamp, which is a very simple class defined as this:
<System.Xml.Serialization.SoapTypeAttribute("GTTimestamp", "http://util.gtdw.pci.com")> _
Public Class GTTimestamp
Public calendar As Date
Public displayFormat As String
End Class
In the input pane for the Web Service, when I click in the value of the two GTTimestamps like I'm going to hardcode them in, another window pops up saying "Enter the values for complex type -in4" and the pane looks exactly like the previous pane with one very important exception: There is not place to check to assign the value from a package variable!!! Dang you Microsoft!!! I don't really understand why they would leave us out to dry on this... Oh, well, maybe they'll take care of it later...Time to work around it.
SSIS does allow you to create a variable of type System.Object, so after playing with the Web Service for a few minutes and giving up on that, I decided to create a script task that is supposed to create two GTTimestamp objects and assign them to two object variables in the package for passing to the WebService Task. The first challenge was to get the web service to play nice with the script. For those who have never done this, use a command prompt and the wsdl.exe to generate a .vb or .cs file to add to your script file using the right click, add existing item...
Once the file was accessable to my scripts, I created two GTTimestamp objects and assigned them to the Package variables of type System.Object. Running the package, I got this error:
"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Type 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp' in assembly 'VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' is not marked as serializable.
Not marked as serializable, huh. Okay, so I made the GTTimestamp serializable by adding the <Serializable()> before the class declaration. Then the error changed to:
The error is: Type is not resolved for member 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp,VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'.
And here I am, at a loss for what to do from this point. I'm a little lost, so I thought I'd stop and ask for directions. I'm sure I'm not the first to want to use complex variable types.
Only two options at this point. One is to try and store the GTTimestamp in the database and see if SSIS can deal with that. I'm not sure how to store objects in a database or if that is even an option for me, but it came to mind so it made it into this post. The other is to get this to work through the script above that I have run into a wall.
Again, any help is appreciated. Thanks for your time.
View 11 Replies
View Related
Feb 20, 2008
Hi
Can anyone help me in for the following.
i want to execute a exe file with two variable parameters
Executable : C: empMyExe.exe
Parameter1 : User::Category type is string (below example A)
Parameter2 : User::Amount type is string (below example 1)
in dos it looks like this
c: empMyExe A 1
This will executes fine.
Thanks,
Madhu
View 1 Replies
View Related
Nov 9, 2007
I am running my "execute process task" in a foreach loop that uses two variables (file paths) from the recordset that feeds into it.
My executable line looks like this: C:Program FilesWinZipWZZIP.EXE ,
which runs the command line Winzip executable and my argument line looks like this: -rp @ZipArch @ZipTxt.
When I replace the variables with literal strings, the process works (only once, of course).
My Argument won't parse in the expression editor, nor does the process run when embedded in the foreach loop. What am I doing wrong? Thanks for your help.
View 12 Replies
View Related
Oct 18, 2007
Hi,
I have an Execute SQL Task that contains the following line:
kill ?
I have a variable (Spid) that I would like to pass as a parameter.
The connection is OLEDB.
I have tried naming the parameter 0, 1 but to no avail
I have also tried:
kill @spid
and named the parameter @Spid. This also does not work.
The variable contains a value obtained from a query that returns a spid for a locked table and is type INT32.
Can I even use the kill statement in such a context?
View 4 Replies
View Related
Mar 22, 2007
Hi,
I'm very new to SSIS and I€™m trying to do the following in a SQL task
RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10
I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?
Many thanks
Martin
View 4 Replies
View Related
