Scripting Task And VBA Macros

Aug 10, 2006

How 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, 2006

Hi, 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

View 5 Replies View Related

Script Task Scripting Help

May 25, 2006

Hi 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

View 1 Replies View Related

How To Run An Exe In The SSIS Scripting Task

Jan 11, 2006

Hi,

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

 

View 5 Replies View Related

Update Variables With Scripting Task

Aug 28, 2006

Helo,
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?

View 8 Replies View Related

Unix Shell Script And Macros Are Available In SSIS ?

Aug 18, 2006

Hi 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






View 1 Replies View Related

Execute Access 2003 Macros In SSIS Package

Nov 17, 2006

Hi

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

View 6 Replies View Related

Data Scripting Tool ( NOT DATABASE Scripting)

Mar 16, 2004

Hi 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

View 1 Replies View Related

Can I Query A Spreadsheet Directly From Sql Without Ssis, Excel Macros, Intermediate Table?

Nov 22, 2007

is 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 Related

Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task

Jun 21, 2007

OK. 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

View 5 Replies View Related

Scripting Job - ??? ARG !!! Help Please ...

Jun 29, 2001

SQL 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

View 1 Replies View Related

Scripting

Sep 14, 2000

I 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.

View 2 Replies View Related

Scripting SPs In EM

Oct 28, 2002

Does 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

View 3 Replies View Related

Scripting Help

May 3, 2006

I 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

View 4 Replies View Related

SQL 6.5 Scripting

Oct 7, 2005

Anyone 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 Related

I Just Need To Know - Is This Even Possible To Do W/o Scripting???

Mar 21, 2007

What 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

View 14 Replies View Related

SQL CMD And Scripting

May 15, 2006

Hi 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

View 3 Replies View Related

Scripting A Database

Nov 18, 2003

I 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 Related

Scripting Database Changes

Nov 4, 2004

Can 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 Related

Scripting Constraints

Nov 30, 2001

Hi,

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.

View 2 Replies View Related

Scripting DTS Packages?

Mar 26, 2001

I 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 Related

Scripting Jobs

Apr 10, 2001

I 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

View 2 Replies View Related

Scripting DTS Packages

Jun 5, 2001

Many 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 Related

Scripting GRANTS

Aug 7, 2000

I 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.

View 1 Replies View Related

Scripting Triggers

Jul 16, 2002

How can I get a script of triggers (drop & create them) without scripting the tables with them ?

Thanks!

View 9 Replies View Related

DTS Package Scripting

Jun 28, 2004

Is there any way to script DTS Packages? :confused:

View 1 Replies View Related

SSMS Scripting

Jan 29, 2008

Are 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

View 7 Replies View Related

Scripting Triggers

May 7, 2004

Hi 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.

View 3 Replies View Related

DCS Scripting Language

May 19, 2008

Does 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?

View 1 Replies View Related

Scripting PK&#043;FK&#043;Index

Oct 28, 2004

Hi,

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

View 5 Replies View Related

Scripting Tables

Apr 11, 2006

Dear 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

View 13 Replies View Related

Scripting New Tables

Mar 9, 2007

Hi 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.

View 2 Replies View Related

Scripting Issue

Mar 17, 2007

Does 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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved