DTS - Many Files To Import - A Question On SWYNK Article
Sep 26, 2000
There is something in a SWYNK article I do not understand. There is a demo of a DTS import to manage many files in a directory. I do not understand what they mean by this line:
"Load both packages and save as Server packages "
How do you do this? I see a couple of files that came with the ZIP package
DTSDirDemo-Import.dts
DTSDirDemo-Loop.dts
I assume these are the ones you load and save as ...
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication). The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
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!!!!
Is it just me and the strange web browser I use (IE 4.01 SP 1 on NT) or is it impossible to download any of the scripts in the script library on SWYNK?
There are some really useful sounding utilities on there, if I didn't get 'downloaded file not available' whenever I tried to access them.
I created the admin database and all the scripts provided at this link. When I tried to run them I found that the master..spOAGetProperty etc stored procedures are not present in my installation. I have SQL Server 7 Standard.
Hi i have a question. How can i import dump from a text file , to an exsiting database, i´m will not useing DTS, i what to use BULK cmd in sql queryanalyser etc.
I have SQL 2000 running under windows 2000 server.
Question: I'm receving CSV files from the field. I would like to built SQL database from daily CSV files. I've been doing this manually :( however, is there anyway I can do this task automactically so that once new daily CSV file ~200KB appear in the folder, progarm automatically insert recent file to SQL database...:) Do I need to write VB progarm for this? Please help...
I have created a stored procedure to handle xml data. One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file. Now I would like to create a SSIS package that takes the xml files from the network and uses this SP. Should I have a file system task inside a for loop container? How do I get started on this please? Thanks
I have created a stored procedure to handle xml data. One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file. Now I would like to create a SSIS package that takes the xml files from the network and uses this SP. Should I have a file system task inside a for loop container? How do I get started on this please? Thanks
Hi, I have about 300-400 XML files I want to load in my SQL database (2005). The following code will load one (1) file. How do i do a mulitple collections? INSERT INTO MEL (DATA) SELECT * FROM OPENROWSET (BULK'C:TempCHAPTER1.xml', SINGLE_BLOB) AS TEMP Thanks,
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?
I have data in excel files I want to import it to existing SQL Server database. I can use SQL Server built in import tool but before Importing, the data needs to be validated. What is the best way to do this?
I have 8GB of text files which are basically log files from the past few years. There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder). It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )
Surely there is a way to mass import without all the costly applications that google searches give me? cheers :P
I have a job that calls about 5 DTS's. Each DTS imports a text file. The problem is that some of the files may not be there every day. How do I exit the DTS without an error if the file does not exist, but I want my job to keep running...
I have a script which imports the contents of a csv file from our CRM system and updates a table in my database. This works OK but the problems I have are that a) sometimes there is more than one file in the folder, and b) that I wish to move any csv files that have been imported into an archive folder. The csv files arrive with a time/datestamp and I currently rename them manually to FREXPORT before importing (the name is in the format FREXPORT_20141101_1217.csv).How do I:
1) get it to process the file without me having to manually rename the file(s) each time, 2) if there is more than 1 file in the folder process all the files and 3)move the correctly processed files to an archive folder which is: importarchive?
Ultimately, I would like the script to be run as a scheduled job, so it also has to deal with the fact that sometimes there will be no files to import too.
I have several hundred .csv files that have a specific cell that I need to get into a SQL table. These files are named after the date on which they were created...ie 8252005 would be todays date. Im looking for a way to import this cell to SQL... the same cell in each file.... Thanks for any help
I have a task to import *.log files in to the Microsoft SQL Server and need help in this regards.
This import should be the best table structure/format for the web log (considerations for import time, size, indexing, querying, reporting, etc.), and best utilize all available space on the server
I'd like to set up a dts package to import .csv files and on testing one .csv with only one row I am getting this error:
Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description: Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
data set from .csv: -31,0,"CENTER","","","64 ROCK DR. #4100","BETH","MD.","20417","0470-5",30109,10598,"52492000 083","1018","406800","6896CE","7007","G00887",1,13983.24,2139.66,42741.9,150101.36,830317.41,1562843.99,30128.45,2,506,0,0,0,0,0,0,37.87,1,0,0,476.39,472.15,0,1191.12,0,90073.46,43038.97,0,309926.59,-14880.49,20060102,20090101,"A","","",""," ","C62454",0,0,0,0,0,0,0,0,8.39,0,0,0,0,"",0,"00000000",""
i'm trying to find out if SSIS is a right toolbox for importing of many SPSS-files into the large market research data warehouse.
A simplified version of SPSS-datasheet looks like this:
- many columns (up to 3000). some of them are interview attributes (InterviewID, Date, etc.) and variables, representing survey questions
- relative few rows with "cases" or "interviews"
- in cells there are answers to the questions (in columns) by respondent (in rows)
As first step i would like to unpivot the dataset in the following way:
- Pass-Through: all interview attributes, that shouldn't be unpivoted
- Input Columns: rest 2990 columns
- Destination Column: Question
- Pivot Key Value Column Name: Answer
It works allright, except that i has to manually define destination column for all 2990 input columns, which takes a lot of time (multiplied by the number of SPSS-files i want to import). Is there a way to automate this (default value for destination column and/or scripting?)
Does anyone have a completed script that will import Perfmon logs from csv format into an existing database that follows the SQL Log File Schema EG; CounterData, CounterDetails, DisplayToID tables accurately. I don't know enough T-SQL to get it right. Thanks for any input you may have.
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Hi, I try to import csv files to Sql Server using .net. The code is as following: string strCsvConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"; using (OleDbConnection cn = new OleDbConnection(strCsvConn)) { string strSQL = "SELECT * FROM " + strFileName; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cn.Open();using (OleDbDataReader dr = cmd.ExecuteReader()) { // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn)) { bulkCopy.DestinationTableName = strSqlTable; bulkCopy.WriteToServer(dr); } } } And the data is as following (simplified): Model,Serial AFICIO 3045,K9464900965 AFICIO 3045,K9464900932 Fax 5510L,A3761290041 Fax 2210L,A4978800008 AFICIO 3025,K8565201014 AFICIO 3025,K8565102398 The result of the 2nd column is: 9464900965, 9464900932, null, null, 8565201014, 8565102398 - either the first character is missing or the whole entry is missing. One more weird thing is that some other files work fine, though I am not able to tell any difference between them. Any idea is hoghly appreciated. shz
Hi, I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?
If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.
Hi, Using SSIS, I am successfully importing data from excel files into a table in sql server 2005. These excel files are ALWAYS open and are being updated from external sources i.e. third party tools. After doing alot of investigations, I have reached the conclusions as follows: When the excel files are NOT being updated, then the ssis package works BUT when the excel files are being updated, then the ssis package does not work. The error is:
[Excel Source [749]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Please note that the excel files MUST be open so that they get updated by the third party tool. AND, every few minutes i.e. 5 mins, my ssis package should import these excel files.
Question: How is it possible to load the excel data while it is being updated by external third party.
It's very simple import, without any data modifications; from severalexcel files to one table.I tried the wisard and it gives me selection for only one file.I am not used to create DTS packages and schedule jobs, so I would needsome help.Thank youInna
Hi all,New to SQL Server - trying to create an SSIS package that will look forand import a series of Visual Foxpro tables (.DBFs) when they appear ina folder.The tables are/can be all different fields, field widths, etc. Withquite a bit of overlap though.The end result should be table "ABC.DBF" is pulled into SQL Server astable "ABC"Using: SQL Server 2005 Enterprise, SSIS, *latest* version of VFPOLEDBdownloaded from MSI have set up a package and tested it with several different tables andit works great - but I have to redo the data source and destinationeach time...I need to get this to be a somewhat automated process, pulling in all..DBFs no matter what they contain.Can I do this with SSIS alone (and variable substitution) or do I needto write a bunch of code...Thanks very much for your time and thoughts...
Hi all,I have de following application to do :I receive several .csv files from another application in a determined folderof my PC.Those files are named with the format log1.csv logs2.csv logs...The number of file is variable but the internal format is always : time_sec;levelSo the files content a field that may be used as unique key in the target database.I'm trying to build a DTS package that should import periodicallyall the CSV's present in the folder and then destroy them if donesuccessfully.Apparently its not so simple than I supposed. I have always to give the nameof the table I want to import.any idea?
