I have a number of XLS reports in template form. I want to move these to a new location on the File Server and after they have been populated move them to another location on the File Server.
I have seen some proposed solutions but I haven€™t found any that work. This should not be difficult and I envisage using a File System Task and a Foreach Loop Container. However passing the multiple file names to the File System Task errors repeatedly.
What I am trying to achieve currently with SSIS is to view the content in one folder for example 'New' and if there is files in this then move it to 'Archived'.
Any nice liinks or tutorial or general advice you guys could give me ?
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
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...
I am building a ssis package that imports multiple xml files containing data into the tables using one xsd file. I am using xml source task for this. I can only import one file as the primary key constraint gets violated.
I have four tables with four primary keys. The xml file does not have the primary key column data. So every time these columns get populated as 1, 2, 3, 4.
I am preety new to xml, so was wondering if anyone can help? Why doesn't the xml file have primary key column data?
I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.
What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?
Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.
I have an SSIS package in VS 2010 that uses flat files to load database tables. I would like to check for the flat files existing before continuing to run the package. The flat files each have their own connection manager. I was wondering if I could use the connection managers to determine the file names instead of creating a Script Task and hard-coding each of the file names to check.
I'm just learning SSIS. As I was following the tutorial on foreach loop container (lesson 2) to export multiple flat files in creating a simple ETL package in SSIS, I get the following error:
SSIS package "Take17.dtsx" starting. Information: 0x4004300A at Extract Cobra EBA, DTS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Extract Cobra EBA, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Extract Cobra EBA, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Extract Cobra EBA, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x402090DC at Extract Cobra EBA, Cobra EBA [1]: The processing of file "" has started. Warning: 0x80070003 at Extract Cobra EBA, Cobra EBA [1]: The system cannot find the path specified. Error: 0xC020200E at Extract Cobra EBA, Cobra EBA [1]: Cannot open the datafile "". Error: 0xC004701A at Extract Cobra EBA, DTS.Pipeline: component "Cobra EBA" (1) failed the pre-execute phase and returned error code 0xC020200E. Information: 0x402090DD at Extract Cobra EBA, Cobra EBA [1]: The processing of file "" has ended. Information: 0x40043009 at Extract Cobra EBA, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Extract Cobra EBA, DTS.Pipeline: "component "OLE DB Destination" (194)" wrote 0 rows. Task failed: Extract Cobra EBA Warning: 0x80019002 at Take17: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Take17.dtsx" finished: Failure.
I spent some time in understanding where could be the error. I traced it to the following place. If you take a look at the Lesson#2 in creating a simple ETL package in SSIS, using foreach loop container to import files into SQL server, as soon as I finish going through steps in configuring the file connection manager to use the variable for connectionstring, it immediately disappears. I repeated the tutorial three times exactly as I pointed out and when I reach to this step of configuring file connection manager for connectionstring, it takes the path and when I start debugging it, I get this error. When I go and check if everything is Ok, the connectionstring value is empty.
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I need to import multiple flat files with different formats into different tables of the sql server database and not able to figure out the best way out in ssis to do so...
What are the possible methods in ssis to do so and if possible the process which can be dynamic as file names or columns might change in future.
I have been strunggling to find solution to convert XLSX files with multiple sheets to csv file.
Requirements >>Â Convert XLSX file with multiple sheets to CSV file >>Â CSV file names : XLSX filename + '_' + sheet name >>Â scirpt has to be in VB as i am using ssis 2005 >> I started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task. >> found web link as useful.. [URL] ....
Hi, I'm using SQL 2005 express edition and my database files are in the MSSQL.1MSSQLData folder. When I'm trying to copy them I get "Files already in Use" error. How do I stop the server from running (services or command line). Or is there a better why to export them to a diffrent location.
If I use attach/detach to move my log file to a different drive, willit break any of my permissions for the sql logins. If so, how can I fixthis.This all stemmed from needing to setup log shipping to another serverfor redundancy. Step 1 says to create a share where the log filesreside. Well the sql server was installed by a previous employee andthe log files are in the same directory as the data files. I would feelmore comfortable (security wise) if I only shared out the directorywhere the log files lived.If anyone thinks that I am traveling down the wrong path or has anysuggestions, please let me know.Thanks
I need to move backup files from the production server to another server. This would be regularly scheduled file move only for security reasons. The target server does not have SQL Server installed. Which is the best way to do this?
I need to move the errorlog files from the d: drive to the e: drive on my NT servers. Does anyone know a way to accomplish this without having to re-install? Thanks tcb
Sorry for the ultra-beginner question, but I've just recently started playing with SQL 2005 Express Edition for a task I've been assigned to at work.
I'm building an ASP.NET 2.0 web site that needs to connect to a SQL database to pull information. I installed SQL Express 2005 and installed the SQL Server Management Studio and I've managed to build a small database with one table.
I noticed that by default, the mdf and ldf files are located in C:Program FilesMS SQL ServerMSSQLDATA (or something along those lines). The website I'm working on is temporarily stored in C:WebSite. I need to move the database files over to C:WebSiteApp_Data so I can access them easily with ASP.NET and VS 2005 Express.
I can copy and paste the mdf and ldf files, but then I can't figure out how to point the SQL Server Management Studio to the new location.
I am obviously just a complete newbie at all of this. It's pretty sad that I can't figure out how to simply move the database from one directory to another on the same server... it makes me worry about when I'll need to move it to the new webserver!
Could anyone point me in the right direction at least?
I have been instructed to move a large database we have on one of our servers off the current drive (local RAID-5 driveset in the server) to a EMC "drive" (logical drive, off-server).
I know one option is to back up the database, delete the database, re-create the db using the new drive for data/log files, then restore the database.
However, I was wondering if it would be better to just detach the DB, move the data/log files, then reattach to them?
Is it half-doz of one, and 6 of the other?
How should I go about this dastardly deed?
Off to poke around in BOL, but thought I would post first in case it's an incredibly easy answer for y'all
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf'); GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions?????????????????????? Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Trying to find out if this is the best way to move log files in databases that are in an availability group.
remove the DB from the AG Run alter database commands like you would normally to take offline ,move file,bring online ,etc drop the db from secondary node then rejoin the DB to the AG
Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups
I have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:
Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:
Hello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David
I am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. Â I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.Â
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed. Â
-- Move the existing file to the new disk and rename it. ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%) ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%) ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance. Â Also, what are peoples thoughts on percentage growth for tempDb? Â I've read that it's not recommend and yet it seems to be the norm.
