Scripting Task And VBA Macros
Aug 10, 2006How do I run Office macros by using Automation from the Scripting Task in SQL Server Integrated Services?
View 11 Replies
ADVERTISEMENT
Accessing Components And Task From Scripting Task
May 8, 2006Hi, I have to researching of how to accesss package tasks and component using the SqlServer.Dts.Runtime class and so far, I havent found any solution. For example, if you package has a scriptiong task and a data flow task(<- which contains a data source component). Is it possible to use the scripting component to access the data source component in the data flow task and manipulate its properites like sqlcommand etc.
Emmanuel
Script Task Scripting Help
May 25, 2006Hi all,
I use oledb connection in the script just like as follows:
msConnStr = "Provider=SQLOLEDB;SERVER=" & msServer & ";DATABASE=" & msDb
msConn = New OleDbConnection(msConnStr)
msConn.Open()
ds = New DataSet
msData = New OleDbDataAdapter(msSqlStr, msConn)
rows = msData.Fill(ds, "TableName")
This command is working fine, I just need to ask if there is a method
to do retry on the connection if the connection cannot be opened for
reasons like server is temporary down? instead of just make the script
task a failure. Thanks in advance.
Daren
How To Run An Exe In The SSIS Scripting Task
Jan 11, 2006Hi,
Can anyone help me how to run an exe file in the scripting task.
I was trying the below code but it's giving an error.
Set wshShell = WScript.CreateObject ("WSCript.shell")
wshshell.run "c:TestABC.exe", 6, True
set wshshell = nothing
Thanks,
rkn
Update Variables With Scripting Task
Aug 28, 2006Helo,
I'm using a simple scripting task to update the value of a variable:
Public Sub Main()
Dim EndeJahr As String
EndeJahr = InputBox("Text")
Dts.Variables("EndYear").Value = EndeJahr
MsgBox(Dts.Variables("EndYear").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
The Package is working fine and the message box shows the actual value.
My problem is that when I change the of value of my variable, the value of variable is not changing, when I restart my package!!!
What's the problem?
any ideas?
Unix Shell Script And Macros Are Available In SSIS ?
Aug 18, 2006Hi all ,
i am preaparing POC Document for SSIS and also compare with already created Job in Oracle Using Data Stage Server. The follwing List tasks are we can do in Data Stage Sever.
"
1. Unix Shell scripts can be called before/after the job runs.
2. Programming can be done through:
a. Routines
b. Macros
c. Subroutines
d. Functions
e. Expressions
3. Programming can be done in Mainframe jobs and COBOL code and JCL files can be generated.
4. The DataStage Reporting Tool is flexible and allows you to generate reports at various levels within a project, for example, entire job, single stage, set of stages, etc. "
The above Task will we do in SSIS Pacakge or not ? pls any one give me the correct answer?
Thanks & Regards,
Jeyakumar.M
Execute Access 2003 Macros In SSIS Package
Nov 17, 2006Hi
Does any one know how to execute Macros in SSIS Package.
I have an Access 2003 Macro which has to be executed in a SSIS package...
I need to pass some runtime parameter also .
How to go about it....
Thanks & Regards
Deepu M.I
Data Scripting Tool ( NOT DATABASE Scripting)
Mar 16, 2004Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
Can I Query A Spreadsheet Directly From Sql Without Ssis, Excel Macros, Intermediate Table?
Nov 22, 2007is there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?
View 5 Replies View RelatedSend Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task
Jun 21, 2007OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
Scripting Job - ??? ARG !!! Help Please ...
Jun 29, 2001SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
Scripting
Sep 14, 2000I know it's a simple question, just can't figure it out:
How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables.
thanks all.
Scripting SPs In EM
Oct 28, 2002Does anyone know how to prevent or change the
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
that appear between each Create? I can't see any options for changing then or eliminating them when generating the SQL Script for my stored procedures.
Thanks,
Scott
Scripting Help
May 3, 2006I have to come up a script that will be going across 60 different servers that will be looking for document type that are not being used across all servers and then deleting them.
To start with:
Select Enabled from MHGROUP.CUSTOM5
where Enabled ='N'
then delete records
and where would I run this type of script or should I say can this be done?
Thanks
SQL 6.5 Scripting
Oct 7, 2005Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.
View 10 Replies View RelatedI Just Need To Know - Is This Even Possible To Do W/o Scripting???
Mar 21, 2007What I need to do - can it be done without scripting?
1.) I need to get a list of sp's from a table (EXEC SQL task)
2.) Next, loop through the list of sp's and execute (FOREACHLOOP)
3.) Then, write the output of each sp to a uniquely named file. (DATA FLOW task)
So far, I have had success with 1 and 2. But getting the DATA FLOW task to work has been imposssible thus far.
I tried everything I can think of. I even created a child package that gets called from the FOREACHLOOP, using a Parent Variable Configuration to pass down the name of the stored procedure variable to the child package. No dice.
I am ready to throw in the towel here.
So, my question is, can what I want to do even be DONE without scripting? I was trying to avoid the "manual" approach to this project. It seems like what I want to do "should" be so simple.
In sum, I am simply trying to read a list of sp's from a table, execute the sp's, then write the output of the sp's to a uniquely named file.
Can this be done without scripting? I am beginning to think NOT.
Sigh. This is driving me crazy.
Please, any advice!!
Thanks
SQL CMD And Scripting
May 15, 2006Hi Gurus/MVPS:
Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd?. I need to perform the following:
1). Script out the text of the stored procedure
2). Output to a sql file (text file) under some directory like C:sql
3). Import the file into the destination db and run it.
The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.
Please help!!!.
Thanks
Ankith
Scripting A Database
Nov 18, 2003I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
View 6 Replies View RelatedScripting Database Changes
Nov 4, 2004Can you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
View 1 Replies View RelatedScripting Constraints
Nov 30, 2001Hi,
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
TIA,
Seoras.
Scripting DTS Packages?
Mar 26, 2001I am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
View 2 Replies View RelatedScripting Jobs
Apr 10, 2001I would like to move over all of my DTS packages and corresponding jobs onto another MS SQL server machine. The problem that I have is that I haven't been able to find any straight-forward info on how to script up these jobs, move them onto another machine and execute them. I have scanned through previous posts written by people with the same problem, but every response is simply "script up the jobs and move them onto another server." Maybe I'm missing something, but how "exactly" do I get a job to show up on this destination machine in EM, in the management/sql server agent/jobs folder. I did the jobs/all tasks/script job... but then what? I saved it on this destination machine, but am unclear as what to do next. The MS SQL Server Administrators companion book is of no help on this subject, and I do not understand how this simple task can be so confusing and is not documented. Any help would be greatly appreciated.
Thanks in advance!
TB
Scripting DTS Packages
Jun 5, 2001Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
View 2 Replies View RelatedScripting GRANTS
Aug 7, 2000I know there must be a way to script GRANTS for all users for specific objects(tables, sp's, etc). Can't find much in BOL, etc. Does anyone know how?
Any comments would be appreciated.
Scripting Triggers
Jul 16, 2002How can I get a script of triggers (drop & create them) without scripting the tables with them ?
Thanks!
DTS Package Scripting
Jun 28, 2004Is there any way to script DTS Packages? :confused:
View 1 Replies View RelatedSSMS Scripting
Jan 29, 2008Are you kidding me?
I can't create 1 object per file anymore AND I can't have the drop created along with the create
Are they forcing you to use ALTER?
What a piece of junk
Scripting Triggers
May 7, 2004Hi all,
Is there any way to script all the triggers in a database just the way we script tables and stored procedures?
currently I am using the system tables.
regards,
Harshal.
DCS Scripting Language
May 19, 2008Does anyone here know anything about the DCS Scripting Language.
A contract vacancy has arisen using this language but I've never heard of it and there does seem to be anything on the internet about it. Can anyone help?
Scripting PK+FK+Index
Oct 28, 2004Hi,
Does anyone have scripts that generate the following:
1. All PK
2. All FK
3. All Indexes
Separate from the table DDL.
( EM can generate but with table DDL as well )
rockmoose
Scripting Tables
Apr 11, 2006Dear friends,
I have alot of tables to create in sql server 2005. They are all ready to copy and paste. Is there a window where I can just script all in at once?
Thankyou very much
Chopsmum
Scripting New Tables
Mar 9, 2007Hi all.
I am looking to write a script to create several new tables. What i would like to know is how do i check if they already exist before i create them and if they already exist dont create them? If they dont exist, create them.
Cheers people.
Scripting Issue
Mar 17, 2007Does anyone have any insight as to what is wrong with my SQL statement? All tables exist in this DB however, I get the messages at the bottom.
CREATE TABLE animal_colors(row_id INT NOT NULL IDENTITY(1,1) ,color VARCHAR(50))
INSERT INTO animal_colors (color)
SELECT
[description]
FROM
color
DECLARE @i INT
DECLARE @row_count INT
SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM animal_colors)
WHILE @i < @row_count
BEGIN
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
WHERE color.[description] = animal_colors.color
and animal_colors.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i
DELETE FROM color WHERE color NOT IN (SELECT TOP 1 color FROM color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i
SET @i = @i + 1
END
DROP TABLE animal_colors
Result:
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.