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


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

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

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

About Scripting In SSIS.

May 17, 2007

Hi all,
I am new to SSIS...and wanted to know where can i get a lot of info on how to deal with ActiveX Scripttasks & Script tasks in SSIS...the place i am working has a lot of VB Scripting done in DTS Packages...having a hard time in understanding the Scripts, as i am more like back-end guy and wanted to learn a lot in SSIS, once i understand the scripts it will help me a great deal as to how to approach the tasks...Is there any website which teaches how to avoid Scripting in SSIS as i read somewhere that Scripting should be avoided as much as possible by making using of so many tasks in the SSIS tool.

I will look forward for someone to help me out and show me a way.

thanks
ravi



Nothing much that i can do..!!

View 1 Replies View Related

SSIS Scripting

Feb 6, 2008

Has anyone used this shiit ??

its an absolutely useless pile of crap ??

I can't do simple things that were done easily with Active X ..

USELESS...

View 7 Replies View Related

Scripting In SSIS

Dec 6, 2007

Hi, Does anyone have any suggestions on a book about scripting in SSIS. I'm currently using the WROX SQL Server 2005 Integratin Services book. This is an excellent book, but I need something that has more on scripting in SSIS.
My background is dba work, not programming. Thanks.

View 1 Replies View Related

Advice On Scripting In SSIS..

May 17, 2007

Hi all,
I am new to SSIS...and wanted to know where can i get a lot of info(from web/book) on how to deal with ActiveX Scripttasks & Script tasks in SSIS...the place i am working has a lot of VB Scripting done in DTS Packages...having a hard time in understanding the Scripts, as i am more like back-end guy and wanted to learn a lot in SSIS, once i understand the scripts it will help me a great deal as to how to approach the tasks...Is there any website which teaches how to avoid Scripting in SSIS as i read somewhere that Scripting should be avoided as much as possible by making using of so many tasks in the SSIS tool.

I will look forward for someone to help me out and show me a way.

thanks
ravi



Nothing much that i can do..!!

View 1 Replies View Related

Books For Scripting (.NET) In SSIS...

Apr 10, 2008

Could someone recommend good books for scripting with .NET in SSIS? Anyother useful websites that have this kind of stuff? Appreciate any help.

View 5 Replies View Related

Best SSIS Scripting Tome?

Nov 29, 2006

Is The Rational Guide to Scripting SQL Server 2005 Integration Services Beta Preview by Donald Farmer the best way to learn how to use scripting in SSIS as of late 2006? I'm not a .NET developer, although I come from a Java and C++ background.

I already own Professional SQL Server 2005 Integration Server, but that one doesn't cover scripting so much.

Thanks in advance.

View 2 Replies View Related

4 Errors When Using SSIS Scripting Component

Nov 7, 2006



Hi guys, I got these errors when writing a scripting component. Anyone encounteer these errors before?

Warning 1 The dependency 'EnvDTE' could not be found.
Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.



-Daren

View 4 Replies View Related

SSIS Data Transformation Using Look Up Or Scripting???

Mar 6, 2008

Hi all,

I've got to change values in my source database as follows:

Source: Target:

X 1
Y 1
Z 2

Can I create a lookup table and us a look up task in SSIS to do this or do I need to script it?

Thanks

F

View 1 Replies View Related

Deleting Files Using SSIS Scripting Object

Jun 5, 2006

I am utlizing a scripting object in my ssis to combine two text files into one final file, and then I want to delete the original files. To do this I am utilizing the FileSystemInfo namespace and associating the file names, then utilizing the DELETE functionality.

The creation of the final file works perfectly...unfortunately, my base files do not delete, and I do not get a failure message or indictator.

Here is my code:


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.IO.File
Imports System.IO.FileSystemInfo
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strCurrentMonth As String
Dim strCurrentYear As String
Dim strWriteFileName As String
Dim strReadHeaderFileName As String
Dim strReadBodyFileName As String

'Utilizing a case statement, determine the monthname & year and set the appropriate variables
Select Case Month(Now())
Case 1
strCurrentMonth = "January"
Case 2
strCurrentMonth = "February"
Case 3
strCurrentMonth = "March"
Case 4
strCurrentMonth = "April"
Case 5
strCurrentMonth = "May"
Case 6
strCurrentMonth = "June"
Case 7
strCurrentMonth = "July"
Case 8
strCurrentMonth = "August"
Case 9
strCurrentMonth = "September"
Case 10
strCurrentMonth = "October"
Case 11
strCurrentMonth = "November"
Case 12
strCurrentMonth = "December"
End Select

strCurrentYear = Year(Now()).ToString

'Set variables with file names (reader files and write file) for ease in readability and to
'set final (write file) with appropriate nameing convention utilized by Matria HealthCare.

strWriteFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO Matriacup_ref_cup_" & strCurrentMonth & strCurrentYear & "_ftp_ReferralFormat.txt"

strReadHeaderFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO MatriaMatria_Referral_Control.txt"

strReadBodyFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO MatriaMatria_Referral.txt"

'create stream reader/writer objects

Dim sr As New StreamReader(strReadHeaderFileName)
Dim sr2 As New StreamReader(strReadBodyFileName)
Dim sw As New StreamWriter(strWriteFileName)

'feed the header record into the final file

Do Until sr.Peek = -1
'write the header record
sw.WriteLine(sr.ReadLine)
Loop

'close the read stream for the header record file
sr.Close()

'Feed the body records into the final file
Do Until sr2.Peek = -1
'write all base records
sw.WriteLine(sr2.ReadLine)
Loop

'close the read stream for the body records
sr2.Close()

'close the write stream for the final distribution file
sw.Close()

'dispose of all stream objects
sr.Dispose()
sr2.Dispose()
sw.Dispose()

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

'final statement for SSIS package to determine script result

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I would appreciate any light you can shed on this. Thanks!

View 5 Replies View Related

Add Reference To Excel In Scripting Tool In SSIS 2005

Sep 24, 2007

How can I add a reference to the Microsoft excel 2003 in the script tool in SSIS 2005? I need to automate Excel for formatting.
Thank you.

Dan

View 1 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

SSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables

Feb 21, 2008



I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.

The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.

I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)

Any option anyone knows will help.

Thanks.

View 17 Replies View Related

SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction

Feb 1, 2007

In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View 9 Replies View Related

New 2005 SSIS Task: File Properties Task

Nov 7, 2007

A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.

http://www.pragmaticworks.com/filepropertiestask.htm

View 5 Replies View Related

How Do You Get A Reference To The Task Host In An SSIS Task

Feb 26, 2008

I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1

According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?


I need to get a reference to the task host in an SSIS Task component.

Basically the scenario is this:

I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.

What I did is this:

I've implemented IDtsTaskUI and during the initialize section I wrote:

Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
' Store the TaskHost of the task.
Me.taskHostValue = taskHost
Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
myTask.myTaskHost = taskHost
End Sub

My Task is named: CustomTask. I have a public variable in my task as follows:

Public NotInheritable Class CustomTask
Inherits Task
Implements IDTSComponentPersist
Public myTaskHost As TaskHost = Nothing

Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.

Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.

I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI

How do I solve this?



View 10 Replies View Related

SSIS - FTP Task

May 9, 2008

Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.

I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.

Is there a way to tell the FTP task to just send *.txt, or something to that effect?

Thanks in advance for you help!

Thanks,

Mark

View 5 Replies View Related

SSIS Task

Jun 21, 2006

Hi ,

I have a master database. Based on certain criteria's I will be creating one new database. Once I have finished the creation, I have to check the value of a particular column in both database tables.

This is actually a status check. if both are not equal i have to update detail database table column value with master column value.

I thought i can use lookup transform for this. The problem is master database table's status datatype and detail table's status datatype is diff.

Can anyone suggest me any other way for this problem?

thanks in advance.

View 1 Replies View Related

SSIS Task

Mar 26, 2008

In my package i am loading data from a flat file into multiple tables like table1, table2, table3 all tables have a column called cust_num.
But data for this column cust_num is only coming in table1 and i need to load the data from this column to other tables how can i do this.

View 4 Replies View Related

Need Help!!!! (SP Or SSIS Task)

Sep 6, 2007

I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

I have some 15 tables being populated using this SPROC.

Here is some helpful description
ENTITYNAME gives me the table i need to work
FIELDNAME gives me the field i have to work on
CHANGEDVALUE gives me the value changed in that field
( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

while in Cursors i need to get using these above variables other variables like
FLAG
KeyName

Thrugh SQL1 I get the KeyValue

then using this KeyValue check if the data exist update else insert new data.

QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

/*******************************************************************************************************/



DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage

OPEN Table_cursor


FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE


WHILE @@FETCH_STATUS = 0



BEGIN

DECLARE @FLAG NVARCHAR(50);
SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';

DECLARE @KeyName NVARCHAR(50);
SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR(50)

DECLARE @SQL1 NVARCHAR (1000)

SET @SQL1 = N'Select @KeyValueOUT = '+ @KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @KEYID + ' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

EXECUTE sp_executesql @SQL1, N'@KeyValueOUT INT OUTPUT', @KeyValue OUTPUT;


DECLARE @WC_TABLE NVARCHAR(100)
SET @WC_TABLE = 'WorkingCopy' + @ENTITY

DECLARE @SQL2 nvarchar (1000);
SET @SQL2 = 'IF EXISTS (SELECT '+ @KeyName +' FROM ' + @WC_TABLE + ' WHERE ' + @KeyName + ' = ' + @KeyValue + ' )' +
' BEGIN UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @ENTITY + ' WHERE ' + @Flag + ' = ' + '''Y''' + ' AND '+ @KeyName + ' = ' + @KeyValue + ';' +
'UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END'

EXECUTE sp_executesql @SQL2


FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE

END


CLOSE Table_cursor
DEALLOCATE Table_cursor

View 1 Replies View Related

FTP Task In SSIS

May 21, 2007



Hello,



I m having an FTP Task in SSIS project, from where I have downloaded .csv file.

And also having a Processed Folder in which the processed .csv files are kept.



Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing



Can anyone help me out how to process about.



plz reply as early as possible.



Thanks & Kind regards ....

Aparna





View 1 Replies View Related

SSIS Script Task

Jun 19, 2008

I have written a very basic Script Task using VS 2005, it just looks through a sqlDataReader and display a message.
I want this script to be triggered by an SQL Agent. I am not sure what to do from this point on. How do I publish or whatever it is called to get it to the SQL server or agent?
This is my very first attempt, no I have no clue what I am doing, will need lots of help and tutorials, thanks for reading.

View 1 Replies View Related

Script Task In Ssis

May 25, 2007

i am trying to generate a dynamic connection string using a variable 'servername'
i know where and how to do it but i am not sure abt the details of the paramenters that are to be passed in the script

and anyone give me a sample script

this is what iam trying to do

Public Sub Main()
'
Dts.Connections("SQL????").ConnectionString = "Data Source????" + Dts.Variables("servername").Value.ToString() + ???windows authentication and what else ????
'
Dts.TaskResult = Dts.Results.Success
End Sub

Thanks all

View 3 Replies View Related

SSIS Custom Task

Nov 19, 2007

Hi there,
I'm still pretty new to SSIS, what I am really battling with is getting a custom task to work.
I have developed a custom task, digitally signed it added it to the Global Assembly Cache, written the UI for it - Pretty much as it is done here: http://technet.microsoft.com/en-us/library/ms136080.aspx
but my problem is that when I drag it from the tool box into the control flow pane, I get this message: "The task user interface specified by type name 'XXXXXXXXXXXXXXXXXXX' could not be loaded.
(Microsoft.DatatransformationServices.Design).

Does anybody have an idea of what I am doing wrong here?


Duane.

View 2 Replies View Related

SSIS Script Task

Feb 20, 2008

Dim RootFolder As String


RootFolder = DTSGlobalVariables("gRootFolder")


I have an error that RootFolder is not being declared with this ?
but it is , why is .NET such a heap of sh*t ?

View 1 Replies View Related

Moniker For Own SSIS Task

Aug 27, 2007

How can i find out the moniker for my own ssis task?

View 3 Replies View Related

Mail Task In SSIS

Sep 8, 2006

Please help me i am new to ssis ....I am trying to send an email using mail task and I am sure that ISP works fine with smtp. I am entering all the correct information in the task but it takes a long time to run the task and then comes back with a failure error. can someone help me find an easy way to send an email to group of emails...I even tried to send it to 1 email and it is still not working.

View 10 Replies View Related

How To Do A Simple ETL Task Using SSIS?

Apr 24, 2008

Hi

I am new to SSIS. I wanted to do a daily ETL load from sourceA to targetB database

Please let me know the best method to that i should follow to do the below task

1. Get the Yesterday's date or last months's from-date & to-date dynamically.
2. Pass the from-date and to-date to my source SQL [e.g select emp_name, emp_dept from emp where hiredate between @param1 and @param2]. Acually my source sql has subquery and the parameters has to be passed to the subquery's where clause.
3. Insert the resultset into a staging table in the targetB database.
4. Join this targetB table with another table in sourceC db
5. finally load the result set in to the targetT database table
6. How to configure/schedule this package so that it runs daily, automatically pass the yesterday's date to the package.

I have done this using Oracle PL/SQL but my requirement is to do this in SSIS. I am not sure which control flow or transformation items to be used to do this in an effecient way.

Please guide me.

View 4 Replies View Related

FTP Task For SSIS Missing

Dec 22, 2006

Hi Everyone,

When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.

Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.

Thanks

View 1 Replies View Related







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