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

Automatic Import CSV Files

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

Automatic Import Of Emails

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

Automatic Data Import

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

Automatic Flat File

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

Automatic Log File Shrinking

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

SSRS Report And Automatic Pdf File

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

Error Regarding File Import Through Import Wizard

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

DTS File Import When File Is Locked

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

How To Import XML File Into SQL.

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

How To Import .mdf File

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

Import A CSV File

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

How Do You Import A DBF File

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

Import File From Web?

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

Import .dbs File

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

Import A Dbf File

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

Import CSV File To DB

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

Possible DTS File Import Bug

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

Using VB.Net To Import A Csv File

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

How To Import *.mdf File

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

Import Csv File To Sql

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

Import CSV File?

May 8, 2007

how can i import CSV data to sql server using sql statement?
thanks

View 2 Replies View Related

How To Import .unl File

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

Import An Mdb File

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

Import Csv File To MS SQL 2005

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

Import Txt File Into Sql Table Using Vb.net

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

How To Import And Export CSV File

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

How To Import Csv File Into Sqldatabase

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

How To Import CSV File In To SQL Table

Apr 7, 2004

Any body know How to Import CSV file in to SQL table using asp.net

View 13 Replies View Related

Need Help To Import Data From Bak File

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

Import XML File Into Sql Server

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

Dts Import Flat File

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

Text File Import

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







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