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
ADVERTISEMENT
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
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
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
Mar 9, 2014
I've got an archive of old emails from a previous employer I want to import in SQL for a little side project - to make the emails easily searchable, queryable, and to do a few things with the contacts. I've stripped all the attachments out because I didn't want to store them or have them cause issues.
I don't know of a way to do this - exporting the folder in Outlook to CSV is garbage. That CSV file is so mangled with junk...
I tried access, but get this error in 2013 when trying to import for an external source
Microsoft Access can't find the wizard. This wizard has not been installed, or there is an incorrect setting in the Windows Registry, or this wizard has been disabled.
I don't have any addin - I installed Access from the web....i can't imagine an incorrect setting, but I've tried repairing and search Google with no luck...
View 6 Replies
View Related
Jan 11, 2007
to utf8 format through BCP or throuhg the dos command prompt?
Cheers
View 3 Replies
View Related
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
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
View 20 Replies
View Related
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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×tamp 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
Jul 23, 2013
I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
View 9 Replies
View Related
Apr 29, 2015
My files are stored as Newborn.txt under each year's subfolder, How to implement this.
Eg: C:RaviN90Newborn.txt
C:RaviN91Newborn.txt
C:RaviN92Newborn.txt
C:RaviN93Newborn.txt
How to read the files using the for each loop container"
View 5 Replies
View Related
Jun 3, 2015
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
View 0 Replies
View Related
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
Aug 4, 2015
I'm using the FTP Task in SSIS to send files. They task succeeds but the files get uploaded to the wrong folder. Instead of being sent to the cg268301 folder they are being sent to the cg268300 despite selecting /cg268301 from the remote path field in the FTP task editor.
I've tried uploading this file to the /cg268301 file using an execute process task and it works fine. I just don't know why it won't work with the FTP task.
View 3 Replies
View Related
Jun 4, 2015
I have Developed ETL Package Which Supplying the CSV File, if I run the package Next time if Same File name is there I need to Rename the that File with Currentdatetime need to move in to Archive Folder. if that File is not exist in that location no need to move the file into Archive file.
View 4 Replies
View Related
Jul 15, 2015
We have the following requirement :
1. In daily basis auto loop through each item in the invoice table.
2. passing invoice number into a Summary SSRS report as parameter.
3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf
how to achieve this via SSRS, Store Procedure or Power Shell?
View 4 Replies
View Related
Mar 5, 2007
Where do I go in my computer to correct this problem so that I can complete the installation?
View 3 Replies
View Related
