DTS Automatic Dat File Import
Jul 20, 2005
Hi y'all
I'm having some trouble with the following script I use in a
dts-package. I use it to automatically import a dat-file of which the
name changes every day. Today the name f.e. is P0000020.dat, tomorrow
it's P0000021.dat and the day after P0000022.dat etc etc The error I
get is end of instruction expected.
This is the script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim oPkg, oDataPump
Dim sSourceTable, sDestinationTable
' Derive the new table names
sSourceTable ="" & GetName
sDestinationTable = sSourceTable
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Set the new values
oDataPump.SourceObjectName = sSourceTable
oDataPump.DestinationObjectName = "[Test].[test].[tblXafaxOutput]"
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Function GetName
Dim number, counter, file
counter = 1
number = DTSGlobalVariables("NextNumber").Value
number = number + 1
For counter = 1 To 7 - Len(number)
file = file & "O"
Next teller
file = "P" & file & getal & ".dat"
DTSGlobalVariables("NextNumber").Value = number
GetName = file
End Function
I've used this script before in another dts-package where the
file-name needs to be based up-on the date and it worked just fine.
You can find the script at:
http://groups.google.be/groups?hl=n...l e.com#link10
I hope anyone can help me.
thanx in advance
Piedro
PS I've my english is crap it's because it ain't my mothertongue.
View 1 Replies
ADVERTISEMENT
Mar 22, 2001
Dear all,
I'm new to SQL server. I would like to ask how to do the following. Now I have many CSV files in a directory, which are uploaded through ftp from my client computers daily. How can I automatically import those CSV files to the SQL server? Can I do it by a script or a stored procedure or something else?
Your help would be much appreciated.
Thanks,
Roland Lee
View 2 Replies
View Related
Jul 14, 2006
Is there a way to automatically import incoming emails into a SQLServer Database?I know it is inefficient, but we have to run a number of rules on theemail after it is received. Store it in a database and be able toconnect other things, meetings, contacts, etc. Moving it back andforth in chunks can be difficult and the overhead could spiral out ofcontrol.So we were thinking of moving it to the DB as it was retrieved.What do you think would be a better solution to retrieve it?Does my email make sense?
View 2 Replies
View Related
Oct 17, 2006
I just got sql 2005. Can someone point me in the right direction on getting started importing data. I need to be able to load ascii data into SQL 2005 on a weekly basis. This ascii file will have the same data structure but data will change weekly.
I have heard to do this automatically (basically with a click of a button) you should use a DTS package. I also see this SSIS. I am trying to understand what to use for this import process. I would really appreciate any help on this. Thanks.
View 1 Replies
View Related
Aug 20, 2001
1. I want to create a flat file from sql server on every night
which will contain data from particular table of that paticular date.
2. I wanna put the file in the particular folder.
3. I wanna give the file name like 'ALT-yyyyddmmhhmmss'
where 'ALT' is some constant and 'yyyy' is year,'dd','mo','hh','mm','ss'
is the day,month,hours,minutes,seconds of the day respectively. So that we can find from the name of the file when it is created.
4.Please suggest me how to go for that.
Thanks,
Harish
View 1 Replies
View Related
Oct 16, 2007
I'd like to create a stored procedure to automatically shrink the transaction log file on a certain database monthly. As I am not the DB admin (it's a web-based db on a remote server), I'd like to put together a script to create the procedure so I can just send it to the DB admin to run. It seems like it should be pretty easy, but I'm still pretty new to SQL server. Is anyone able to give me some pointers?
View 1 Replies
View Related
Mar 15, 2007
Hi
I am tring to create a tool which should do the following:
will generate reports say 100 too 500 per week .
strore those reports as a pdf file and email the pdf file as an attachment to customer/donor .
Current system is In MS Access and not happy with it.
My first question is what programming language should I consider for automation of the file storage in PDF format . as we are looking for regenerating the reports with SSRS.
My second question is do I need to have Adobe writer / Adobe distller ?
Thanks in advance for sharing your experience regarding this topic.
Srabani
View 3 Replies
View Related
Jan 12, 2006
Hi all,
when trying to ímport files to our database server from a client, I keep getting an error:
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
(SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
best regards
Musa Rusid
View 1 Replies
View Related
Jan 29, 2003
Guys.
This has been an issue for me. It happens once in a while.
I am importing a File from a shared folder thru DTS package. Once in a while the DTS package fails due to the following error.
"Error Opening Data File: Process cannot access the file because it is being used by another process"
Is there anyway I can specify that the file is going to be opened in read only, so that the DTS will not fail?
Any other solution/suggestion?
-MAK
View 1 Replies
View Related
Dec 28, 2005
I want to import XML file as the '@doc' value when I execute 'sp_xml_preparedocument', many thanks!
View 1 Replies
View Related
Sep 28, 2001
Hello every one
i unstalled my sql server and installed it again, and i want the old database. so how i can i import the old database file .mdf, ldf here. i tried to copy this 2files in to data folder, but its not showing database files in the server.
please help me out,i havent taken any backup before uninstallation
with regards
kittu
View 1 Replies
View Related
Jan 2, 2002
Im looking to write a DTS script to import a CSV file into an existing table. I have made sure that all columns correspond. I ideally want to create a stored procedure and a variable to be entered as the file name.
How can I do this?
View 1 Replies
View Related
Apr 15, 2004
How do you import a dbf file to sql and dump it into a table please help . iam totaly new at this.
View 2 Replies
View Related
Apr 8, 2008
Is there a way through SSIS to import a file from the Internet instead of a local file?
http://www.domain.com/files/filename.csv
I would like to be able to do this. If it is not possible, what is a good practice for doing this?
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 5 Replies
View Related
Apr 9, 2008
I have a problem in importing .dbs file into sqlserver 2005.
Please suggest me with a way.
View 4 Replies
View Related
Oct 31, 2007
I want to schadule a file import, but file is in a zip format. is there any way to import it? in zip there is a foxpro(dbf) file.
waiting for help in this regard.
View 6 Replies
View Related
Nov 1, 2007
I'm new to the SQL environment and am looking for some help. I have a client that has a CSV file that we need to import to a table but we need to make this part of an existing job. I'm wondering if anyone has any scripts for importing a CSV file into a DB. I've looked at the Import Wizard but don't think that is going to work the way I want it to.
Thanks
Damon
View 4 Replies
View Related
Jul 23, 2005
Env: SQL Server 2000 on in WIN NT 5.xJob: import mutiple flat files into several tables daily.Catch: one or two of the several flat files might be empty.First thought/test:Use [first row as fields] option for the import process.Problem, DTS can't complete (as a package).As an alternative, I could probably detect if a file is empty thendecide what to do with it, with VB activeX, it might be feasible,question, VB has a command for "FileExist", how about "FileLen" or thelike for determining the length of a file?TIA.
View 6 Replies
View Related
May 15, 2008
Is there a simple VB.Net code example to import a csv file from a directory into a table in sqlserver using SSIS?
I have a file located on a network drive. I want the program to import the file (with options like use row one for column names) into a table.
I know how to do it using SSIS but I need the program to be able to pass the filename.
Any simple example to get me started?
View 5 Replies
View Related
Jun 2, 2006
I have a SQL Server 2005 database *.mdf file that I would like to import into another SQL Server 2005 engine on another computer. I don't see how to do this.
How can I import a *.mdf file to create a copy of an exiisting database from another computer?
Thanks.
View 7 Replies
View Related
Jun 30, 2007
Hi guys
i am trying to import a csv file into the sql, but my problem is that I have a message in one of the columns. suggestions
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "INTVAL" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "output column "INTVAL" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "INTVAL" (22)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "E:minessightlastinfoattrib.csv" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - blastinfoattrib_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
View 4 Replies
View Related
May 8, 2007
how can i import CSV data to sql server using sql statement?
thanks
View 2 Replies
View Related
Oct 8, 2006
I have made the exact replica of a database in SQL server 2005, and i got unloads from an Informix database.
How do i load them into my SQL server database?
View 1 Replies
View Related
Mar 28, 2006
Hi everyone,
I have an mdb file that I want to import using sql server 2005 express edition. I am using the Management Studio Express and have had no success.
Thanks in advance
View 6 Replies
View Related
Dec 22, 2006
Hi Guys,
I have been trying to search for a free asp or asp.net script that will allow me to upload a .csv file and import it into an MS SQL Database. As its going to be a ProductCatalog and pricing changes nearly 2nd day. And wanting to an a script that I can put in my admin panel on my site to upload a .csv file and import it to a MS SQL Database.
I will be updating fields as well as adding new products. So the upload script would need to be able to handle those two things.
Is their any good free scripts around that people can recommend.
Thanks
Matthew
View 3 Replies
View Related
May 3, 2007
hi!
my problem both concerns vb and sql. the problem is I have a text file delimited with semicolon. I have to enter this data into Sql table.
for example;
item description ;code1;code2;code3;
there are too many lines like this. The code should enter the table these values.Table name is products, column names are desc,code1,code2,code3
I am using vb.net and sql server
thanks all!
View 13 Replies
View Related
Aug 25, 2007
Hi frnz ,
i want to know how to import a CSV File and export CSV File From database SQLSERVER-2005 Using ASP.NET With C#
IF any one knows Help me
Vinnu
View 1 Replies
View Related
Sep 26, 2007
Hi All,I have to import a csv file to sql database.I searched on the net and found plenty of exampls but all with msaccess database.I did not find any example with sql server.Can anybody have the code or the link?here is one example with msaccess
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:;Extended Properties=""Text;HDR=No;FMT=Delimited""")
Dim da As New OleDbDataAdapter()
Dim ds As New DataSet()
Dim cd As New OleDbCommand("SELECT * FROM C:Test.csv, cn)
cn.Open()
da.SelectCommand = cd
ds.Clear()
da.Fill(ds, "CSV")
dg.DataSource = ds.Tables(0)
cn.Close() but when i am using the sqlserver how to get csv data by setting extended properties .RegardsBrijesh Singh
View 1 Replies
View Related
Apr 7, 2004
Any body know How to Import CSV file in to SQL table using asp.net
View 13 Replies
View Related
Jan 13, 2005
Hi,
I backup my database through SQL Server 2000 SQL Query Analyzer :
"BACKUP DATABASE Mysite to disk = 'd:BatabaseBackupMysite.bak' ",
and got a bak file to import it to hoster server. Now I need import (backup)
database from that hoster server back to my local server. I got a bak file call
"MysiteExport.bak" file and transfer it to my computer d:BatabaseBackupMysiteExport.bak' . Then use SQL Server import wizard to import "MysiteExport.bak" and fail to do it.
How can I import/restore database from this "MysiteExport.bak" which have already
under my computer. If use SQL Query Analyzer, what is the correct statement.
Please help.
Lin
View 6 Replies
View Related
Feb 22, 2005
Hi all,
I have exported all data(including database, tables wiith data) into an XML File.
How to import this xml file into MSDE.?
Can anyone suggest me with good coding in C#?
Thanks and with regards,
Madhusudhan
View 1 Replies
View Related
Mar 7, 2001
Hello, I'm trying to import data from a flat file into a table that has smalldatetime data types. I tried creating triggers on the smalldatetime data types that converts the data from a string to a datetime value but the import is still unsuccessful. What should I do?
Col002 looks like this in my flat file 'ex: 20000112'
DTSDestination("entry_dt") = DTSSource("Col002")
I get an error when trying to put the value of col002 into entry_dt.
Thanks.
View 2 Replies
View Related
Apr 17, 2000
SQLServer 7.0
Hi,
I have a small project on that involves importing a series of csv files held within an ftp directory into our Datawarehouse. Every day a series of csv files will be added to the directory. These will be named something like:
Audit1.csv,Audit2.csv etc.
I would like to automate this process as this can involve up to 400 files at a time. The proecedure would need to identify a valid file, import it into the database, delete the file and then move onto the next one.
Does anyone know of a way to achieve this? I was thinking along the lines of using a cursor and bcp but I'm not sure how to identify these files to the database i.e. how do i make it step through the directory and process the files?
Any help would be greatly appreciated.
Thanks
Rob
View 1 Replies
View Related