OSQL Command - Execute All Files In A Folder;

Aug 8, 2006

Hi,

How do I execute all the .sql scripts in a folder with OSQL command?

Thanks in advance,

Hari Haran Arulmozhi

View 4 Replies


ADVERTISEMENT

SQL Server 2008 :: Use Folder Name As Database From Batch File To Execute Files

Jul 17, 2015

I've been struggling with this issue,

1) Test--FolderName (This Test folder name should use as a database name for below sub folders)

a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql

[Code] .....

The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..

Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?

The databases that they need to run against have the name of the database at the beginning of the name of the folder.

View 0 Replies View Related

Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!

Apr 24, 2008

In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.

I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.

Thanks for the help in advance and i apologize for the long lines of code!

example for step 1:
----------------------------------------------------------------------------------------------------------

' 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 Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo

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 cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

example for step 2:
-------------------------------------------------------------------------------------------------------

' 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 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()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "ftp.name.com")

cm.Properties("ServerUserName").SetValue(cm, "username")

cm.Properties("ServerPassword").SetValue(cm, "password")

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'ftp.SetWorkingDirectory("..")

ftp.SetWorkingDirectory("directoryname")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C: emp", True, True)

' Close the ftp connection

ftp.Close()





'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View 16 Replies View Related

Any Way To Pass Command Line Variables To Osql

Sep 13, 2004

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.

Is it possible to do this.

View 1 Replies View Related

How Can I Get A Return Code Of 1 For An Osql Command Which Has A Lower Severity..

Jul 20, 2005

I am running the following OSQL command and capturing the return codefor the error .Whenver i have an error like server not exists or uableto login I get a return code of 1 for the %ERRORLEVEL%.Howeverwhenever I have an errorof a wrong dbcompatibility error the retuncode id 1 even though sql returns an iformation message from OSQL thatthe right copatibilty levels are 60,70 and 80.How can i get OSQL toreturn the right return code whenver a error of this type occurrs frombatch mode sql.The OSQL i am running from the batch isosql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txtECHO %errorlevel% >> %logbatch%IF %ERRORLEVEL% NEQ 0 Goto SQLErrorsqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80Thanks in anticipation.Ajay

View 3 Replies View Related

Running A SQL File From OSQL Command Line Utility

Jul 20, 2005

Hi,I have dumped a very large database from mysql (using mysqldump program)as a raw sql file. The reason was, convert this database to a MSSQLdatabase. Since mysqldump creates the file as raw sql file with thedatabase-table structures and the data in it, I thought using OSQL commandline utilities should work to out this whole database in MSSQL server.I have run this command from command line:osql -u sa -i mysqldump.sqlIt is going since yesterday. It has been almost 36 hours that it'sstarted. And in the mssql server, I see no database created yet. On thescreen of the command line, I see bunch of numbers are going in order. Iassume they are row numbers of the tables processed. But, if it is doing it,then where is it saving all this data ? I have checked the tempdb, pub db,other dbs, and I see no tables related to the database I am inserting. Willit populate it at the and of the job ? Or, am I doing something wrong here?Regards.Murtix.

View 2 Replies View Related

Passing Error Messages From Stored Procedure To Osql Command

Oct 7, 2004

I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

Questions:

How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.

View 3 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

Feb 24, 2015

I have the need to delete old backup files via TSQL job. Found this solution online:

PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD

It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?

View 6 Replies View Related

Execute All Scripts In A Folder

Aug 18, 2006

Hello



I know its possible since i saw it on a demonstration ;)

I want to loop through all files in an folder and execute them as SQL Script in an SSIS Project. Can anyone help on how to realize this? I dont know which task can be used to read the content of a file into a variable

View 1 Replies View Related

Run All .sql Files In A Given Folder?

Jun 4, 2008

Hi,

say I have th following in my post-deployment script:

:r ..ScriptsFolderScript1.sql
:r ..ScriptsFolderScript2.sql
:r ..ScriptsFolderScript3.sql
...

How can I do the equivalent of

:r ..ScriptsFolder*.sql
??

I've tried the above and the syntax is not supported.

Your help is much appreciated! =)

View 3 Replies View Related

Get Files In Different Folder Using Sql

Aug 9, 2006

Hi All,I have a multiple files, but they are store in different directory onthe server. I want open those files and insert it into the databaseusing bcp.Example files structure dir:\xyz123abc ext1.txt\xyz123abc ext2.txt\zyz123999 ext2.txtbcp "dabase" in \xyz123abc ext1.txt -c -S"servername' -Usa-Ppassword -T".is there away to loop througth each dir, get the files, excecute thebcp, then go to next folder.Please help. Thanks in advance.Ted Lee

View 2 Replies View Related

How Can I Execute An Sql Query Which Is In An External Folder, From My Program??

Feb 22, 2008

how can i execute an sql query(which is in test.sql) which is in an external folder, from my program??

View 1 Replies View Related

Is There A Way To Save All Files In A Folder

Jan 11, 2007

to utf8 format through BCP or throuhg the dos command prompt?

Cheers

View 3 Replies View Related

FTP Task- No Files In Folder

Dec 2, 2007

Hi,

I'm tring to copy files from FTP address, the problem is that sometimes the FTP folder is empty, and then the FTP Task is failed.
Why is it failed if there are no files? Any suggestion how to avoid the error?


Thanks,
Hadar

View 4 Replies View Related

Import All Xml-Files From A Folder Into SQL

Jun 14, 2006

Hello

I'm just starting with SQL and BIDS. I have created a xml-Import to SQL Server which works without any problem.

Now I like to import (daily) all xml-files of an appropriate Folder at once to the DB. The files will have different names each day.

I could not find any help in the internet to solve this problem.

Can someone give me an Idea how to import many xml-files out of the same folder to SQL?

Thanks & regards

Chaepp





View 4 Replies View Related

SQL 2012 :: Converting XML Files To PDF And Zip In Folder

Jun 16, 2015

Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.

Can it be possible in SQL server BI world?

If possible make this an automated process for every 100 files.

View 3 Replies View Related

SSIS Processes All Files In A FTP Folder

Jan 2, 2007

Hello everyone - wonder if you can help.

How do you process all files in an FTP directory - similar to the for each loop for files.

I need to be able to download each file and then move it to an archive folder on the ftp site.
Might also want to do some things in between.

I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.

Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it.

View 2 Replies View Related

Accessing Files On A Shared Folder

Jan 7, 2008

can sql server 2005 access files on a shred folder (which sql 2000 was not able)?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

View 3 Replies View Related

Why Have SQLCE... Files Under Temp Folder

Mar 14, 2007

I am developing application with SQLCE2.0, NETCF1.0,Sp1,VS2003.

I found there is some files are created under "Temp" folder by the system with size "0B".

Why/when these file are created? Do I need to clean it periodicly? If not, will this cause exception like "Not enough storage is available to complete this operation"?



Thanks.

View 10 Replies View Related

Files Inside RSTempFiles Folder

Oct 15, 2007



Hello;

I started to get out of space on the server C: drive.

I checked and I was using file system storage on the C drive in:

C:Program FilesMicrosoft SQL ServerMSSQL.xReporting ServicessRSTempFiles

I checked the folder and it is 1 GB.

My questions or doubts;

1) I read that the path can be changed without any problem, in the same server we have a D drive with 40GB of space, so I am planning to change the path to: D:RSTempFiles. Any experience on changing that path?

2) If the change of path is successfull, how I can delete the files on the RSTempFiles on the C: drive. Can I directly delete the files? Ther just previous snapshots or I will lose something? Any experience with this will be great.

Thanks everybody and nice weekend.
Luis

View 1 Replies View Related

SSAS 64 Bit Not In Progam Files (x86) Folder

Jan 9, 2008

I have installed SSAS on a 64 bit machine and am trying to connect to Oracle but having little success.
Some of the solutions I've read indicate to uninstall SSAS and then reinstall, making sure that the reinstall does not use the Program Files (x86) folder since Oracle has a problem with the parentheses.
Here's my question... How do you NOT install in the Program Files (x86) folder. It seems SQL Server always wants to put a small part in there.
Thanks in advance and sorry if this is a dumb question.

View 2 Replies View Related

Transact SQL :: Check If There Are Files In Folder

Jul 20, 2015

I'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something

else End
exec master.dbo.xp_cmdshell
'dir C:Test'

View 2 Replies View Related

Using DTS For Reading Dynamic .CSV Files From A Folder

Sep 20, 2006



Hi All,

I have a challenge i am trying to overcome, hopefully soneone would have come across this issue before..

I am creating a DTS package that will be scheduled to run at a certain time everyday. A source folder exists that get a set of new files everyday.The DTS Package will then read each file and copy the data into a load table in my database the challenge is this:

I am trying to load files from a source folder into my load table, Within each file, the entires are in a specific format using pipes to seperate the data that goes into which column e.g

example of a file entry:

column1 | column2 | column3

data1 | data2 | data3

data1 | data2 | data3

data1 | data2 | data3

And now i am using DTS to specify the file format and map the cloumns as apprporiate to my table...all this is well and good, but my problem is each file has a different name as well as being timestamped, now how do i use DTS to specify the source folder, open each file sequentially and read (or more appropriate, copy the entries into my table, inserting new data from each file into my load table as well as overwriting old data in the load table from the files in the folder ?) is there a way in specifying your source folder in DTS rather than specifying the file in the Menu options (in the transformation data task properties )given, and or do i need to write a script for this(reading the file?)

can someone please give me a solution and how to approach this?

thanks in advance

View 4 Replies View Related

Execute A Flatfile Source To Oledb Destination For Each File In A Folder.

Jan 24, 2007

Hello, I wanted to do the following.

Copy a full directory from source to destination (Done)

then for each file on the destination directory,it must process that file and insert rows on the table.

So I created a foreach loop, and created a varriable aclled CURRENTFILENAME, and assigned it into the foreachloop to index 0.



Inside the foreachloop I created a dataflow task, in the dataflow task I dragged a flat file source and an oledb destination, but I noticed that the flat file source requires flat file manager, and the flat file manager requires a unique FILE NAME. I cant put this c:copia*.txt.

I took a loot at flat file source properties and it has associated the flat file manager, but I can not assign the filename to the variable from the foreach.



Any ideas please



View 5 Replies View Related

Stored Procedure To Return All Files In A Folder?

Jul 1, 2006

Hi, I am wondering how can we return each file in a folder ? I am trying to get each file in a folder to perform other job.  Below is the description ...
while folder is not empty
        foreach file
            run the job
        end for
end while
 

View 1 Replies View Related

Rename All Files Located In Folder But Not Working?

Apr 24, 2015

SET nocount ON
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
DECLARE @out_fileDate CHAR(1024)
DECLARE @fileDate DATETIME -- used for file name

[code].....

View 0 Replies View Related

64Bit - Creates Program Files Folder - Why?

Aug 30, 2007

We noticed SQL Server 2005 is creating Program FilesCommon FilesMicrosoft SharedDW on our largest drive for each 64-bit installation. Does anyone know what this is? It appears there is no Microsoft documentation regarding this installation and if we need to keep it. It may be .NET related, but I have no idea why it is needed.

Dave

View 5 Replies View Related

Send E-mail Attach All Files In A Folder

Dec 8, 2006

Is there a way to send an e-mail from SSIS that attaches all the files in a specified folder?

View 5 Replies View Related

Truncate All Raw Files In A Given Folder (Repost As Requested)

Mar 30, 2007



I am looking for a way to truncate raw files without losing the metadata. The metadata of the raw file should be automatically detected at run-time. The result will be a raw file with the same metadata as the original file, but no data in it.



There are two reasons I would like such a tool. First, I want to erase the potentially sensitive data stored in the raw files. Second, I want to keep Validation enabled so that development is simpler.



Is anyone aware of such a tool?



Thanks,

View 4 Replies View Related

Integration Services :: How To Run All SSIS Packages In A Folder Using Execute Package Task

Jun 26, 2015

I have created for each container to call all the packages in a folder like below, also created a variable.

Then I add execute package task inside of foreach container and selected file system in a location and in connection called currently creating package name finally in connection properties i added variable in expression which i created and mapped into for each loop container. I referred below link 

[URL] ....

All the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute. 

View 24 Replies View Related

Move The Source Excel Files To Archieve Folder

Apr 15, 2008

Using the below script task I am checking for the excel file existence and upon file existence using the data flow task will load the excel data into sql table. After the data is loaded from one file or however number of excel files present, I want to move those into a archieve folder with datetimestamp to the filenames,please let me know how I can move those files with datetimestamp to the filenames, any help is greatly appreciated. Thanks!!

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

View 8 Replies View Related

How To Create Dynamic Folder Which Contains All The Files Which Are Used By All The Packages In A Project

Mar 19, 2008

Hi,
My Problem goes Like this....

I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project.
If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.

Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.

one more doubt is..

If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)

But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.

View 5 Replies View Related

Move The Source Excel Files To Archieve Folder

Apr 15, 2008

Using the below script task I am checking for the excel file existence and upon file existence
using the data flow task will load the excel data into sql table. After the data is loaded from one file or
however number of excel files present, I want to move those excel files into a archieve folder with date&timestamp to the filenames, please let me know how I can move those files with datetimestamp to the filename,
any help is greatly appreciated. Thanks!!
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

View 6 Replies View Related







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