Retry N Times If The Source File Is Not Available In The Specified Location
Sep 26, 2007
I have a flat source file(.csv) which I am importing to SQL server. Now, if the source file is not available at the specified location, then the SSIS package should retry to execute n times( say 3 times) after certain time interval. The number of retries and the time interval should be configurable.
I have used Flat File connection manager for the source and OLEDB connection manager for the destination.
I am quite new to SSIS. Any help would be highly appreciated.
View 1 Replies
ADVERTISEMENT
Oct 24, 2007
Hi All,
I created a SSIS package to extract data from a flat file source and load them into a table in a data base. After I created the package i checked it in to source control(perforce).
But the problem is once a month new flat source file comes and data should be updated.
Once the new flat file comes, is there anyway that SSIS package can identify the path of the flat file and execute the package automatically? In Flat file source only the data will be changed. Not location or data type or anything.
Can i use parameters to do that?
Thanks
View 6 Replies
View Related
Feb 28, 2006
If I want to download a file, but I don't know if it's available yet (actually positive it won't be available for some time), how do I make FTP Task retry/wait until file shows up in the ftp folder?
View 1 Replies
View Related
Jul 14, 2006
I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?
View 4 Replies
View Related
Nov 8, 2007
Hi,
Is there a way to set the XSD programatically? I don't want to hard code an XSD filename/location.
Alternatively, is there a way to set the location in a relative path name (i.e. relative to the path of the package)? I did see a similar question here but that wasn't answered. I tried but I wasn't getting anywhere.
As far as I can see there is no way to set this with an Expression either.
Another alternative I thought of would be to set this with using the SSIS object model but I have no experience with this and I don't know if it can be done at run time. If it can be done I would appreciate a pointer to some further information on doing this.
Thanks,
Mark
View 4 Replies
View Related
Jan 2, 2008
Happy new year everyone!!!
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:
1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:
SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
Help anyone? Please ...
View 5 Replies
View Related
Jun 26, 2007
Hi,
I have developed a SSIS package on my desktop and the package involves loading of XML data into a database. The XML does not have inline schema and I generated the XSD file from SSIS.
I used Derived Column and Data Conversion to load the data into the database.
Now, i want to migrate the package to a server. Now, when i change the path of the XML and XSD files, all the tasks show error such as
"Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow...."
Why is this so? I am using the same XML/XSD files after i moved to the server.
Please advice a fix for this?
Regards,
Vikram
View 6 Replies
View Related
Nov 21, 2007
I am using the "XML Source Adapter" in an SSIS package.
I notice that although you can specify the XML filename as an expression, the XSD appears to have to be a fixed file path.
This is a problem for me since the path for the XSD is different in my development than it will be in production (in production it's on drive E:, which I don't have).
I'd like to have the file location specified in the config file, but since I can't make it an expression how can I do that?
Also, since they don't have Connection Managers I can't switch DelayValidation on.
Thanks.
View 24 Replies
View Related
Mar 16, 2015
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies
View Related
Mar 14, 2007
I am finding that in order to have the Web Services Task work successfully the location of the WSDL file has to be on a local drive that SSIS is executing upon. Is the current intended behavior?
In my SSIS task I use a URL path to store information extracted from the Web Service. The information is stored on a different server than the one that SSIS is running upon. This works properly without error.
I have confirmed that SSIS has appropriate permissions to read/write to that directory on that server. When I attempt to reference the WSDL file (located in the same URL directory that I am saving the information) I get a web services error, 'The Web Services Name is empty, Verify that a valid web service name is available."
When I update the Web Service Task attribute to point to the WSDL file located on a local drive it works correctly. I have confirmed that both WSDL documents are exactly the same.
The behavior seems a little strange...so I must be missing something subtle.
...cordell...
View 2 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Jun 3, 2008
Easy question, I hope We are setting up an active active sql cluster. The management have done everything the wrong way around and purchased the hardware already.
We are planning on having 2 databases 1 in each instance. We will place the transaction log files for each database on their own cluster "physical disk" resource. We only have 4 disks availalbe for the transaction log files. To make things a little more complicated I have been given no information on the expected transaction use other than they should be no larger than 50GB
Here are my options
1. 2 Mirrored raid arrays one for each sql instance
2. 1 Raid 10 array with 2 logical drives (so the transaction logs are sharing the same disk spindles)
What would you do? seperate the disk spindles or share them and go for fast disk performace.
Thanks in advanced
View 1 Replies
View Related
Jul 11, 2002
Is the name of the most recent backup file for each database stored anywhere in SQL2K? I want execute a SQA job periodically that takes the BAK from database A and restores it over database B (using the T-SQL RESTORE DATABASE procedure), but I need to know the exact name of the .BAK file; i.e. I need to know the yyyymmddhhmm value at the end of that file.
TIA,
Al
View 1 Replies
View Related
May 14, 2008
I've got some users that created a database with the log file on a drive that doesn't have a lot of space. I'd like to truncate the log and move it to a different drive. I can truncate it, but is moving it as easy as changing the files properties through SSMS?
Thanks
View 2 Replies
View Related
Jul 18, 2007
version:SQL Server 2000.
db size: 25.6GB
trans log file: 32GB
so far: I have read the Forum FAQ on "clearing the transaction file" and some DBCC SHRINK in Books Online.
I am trying to set up maintenance plan or backup plan. File sizes are getting very large and performance is horrible since we have had no regular maintainence in the past (No DBA in house).
1. What is the best way to identify the location of the transaction log file associated with a particular database?
When I right-click on the database name in Enterprise Manager and select properties, I can see one location for the transaction log.
However, If I right-click on the database > All Tasks > Shrink Database > click Files and select Temp_Table_Log, I get a different location.
2. We delete then repopulate about 105000 records in one particular table each day. In addition, we do the same with about a hundred rows in several other tables daily.
-- Should I be doing Full Backups nightly?
-- I have the option set to "AutoShrink" on the db. Will this truncate and shrink the transaction log as well as shrink the db when I do a full backup?
Thanks,
Più pranzo libero!
View 1 Replies
View Related
Oct 13, 2015
I am requested to list out SQL Server setup file location but a bit confused with it, I can see two setup files in two different locations:
All Programms>Microsoft SQL Server 2008 R2>Configuration Tools>SQL Server Installation Center (64bit)>Right Click>Open File Location: Directing to LandingPage
C:Program FilesMicrosoft SQL Server100Setup BootstrapSQLServer2008R2x64
But here below it is found the setup file location:
E:SoftwareSW_DVD9_SQL_Svr_Enterprise_Edtn_2008_R2_English_MLF_X16-29
Here in case I have to install a new instance which one I have to select?
To repair the existing instance which setup file I have to select?
View 11 Replies
View Related
Dec 22, 2006
I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?
View 1 Replies
View Related
Dec 22, 2005
Is it possible to set the log file location during installation? I don't see an option under Advanced. Any suggestions are greatly appreciated. Thanks.
View 1 Replies
View Related
May 2, 2005
Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directory
and file structure will be a folder with a client name (can be called
anything) and it will have files in it (these files will have the same
filenames as all the other directories. So I will have folder
JimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue with
files a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location to
a DTS package so I can import all the data from the text file into a
SQL server. The way the SQL server will be set up is that each
Folder from the FTP site will be a separate Database and each file will
1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the file
location to pull all those files and then importing them to the proper
database.
I'm not limiting the solution to DTS packages so if .NET can be
incorporated to make it easier then so be it. But keep in mind I
can have up to 200 folders with 12 - 20 text files ranging from
hundreds of rows of data to many thousands of rows. And the
package needs to be ran twice a day so time/performance is an
issue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
View 3 Replies
View Related
Dec 28, 2004
Hi
I want to change the default backup location of MSSQL. I know we can configure it while installing. But I want to change the default backup location of a particular database, running on a database server.
The default location is like.
Program FilesMicrosoft SQL ServerMssqlBackup
Thanks in advance,
Prasanna.
View 2 Replies
View Related
Apr 28, 2008
I'm writing a script for a pending migration (2k enterprise to 2k5x64 enterprise). I've got about 325 or so databases, spanned across 3 instances that I need to migrate. My plan is to do a mass scripted detach of all DBs of the old boxes, copy the files (on a new domain) and then reattach all of the mdf and ldf files.
I've been writing a sql script that will scan a "staging" directory for each of the MDF and LDF files, then programmatically run sp_attachdb. The problem is that the mdf and ldf files weren't always a 1 to 1 naming convention. My predecessors stuck all sorts of things in the file names that make doing a simple scripted search pretty difficult. The disk paths are going to be different from server to server (simpler), so I need to move the files
Is there a way (using sp_attachdb or otherwise) that I could somehow scan each of the MDF files to get the path of the LDF? I can already do that with a .bak file using the RESTORE FILELISTONLY command, but I'm not sure how to do it like this. I'm figuring there has to be a way though, as SSMS seems to know where the LDF is if you try to attach an MDF.
Usually, Google does well, but I haven't been able to find anything as of yet. Any ideas?
View 14 Replies
View Related
May 19, 2008
I want to move index file(69 gb) to different location.Right now data and index files are on the same drive(e), I am trying to move index files from e drive to f drive, so I will get 69 gb free on e drive . Can anyone please advise me what precautions I should take and advise best practice.
Thanks in advance
View 2 Replies
View Related
May 17, 2007
Hi, I am working on a new installation which I did not set up and realized was using the wrong partition of the server to store the data and log files, I have already created several databases, I want to use another partition for these databases without having to drop them and create them all over again.
In BOLine i saw this command but want to make sure its safe, hope somebody can comment on this or if I am missing something.thank you
proposed command:
"MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )"
View 7 Replies
View Related
Sep 19, 2007
I asked this before and was told that it was an option on setup, but for the life of my I can't find it.
I have a requirement for all the transaction logs to go to a separate drive (L:), I would like to be able to specify this location as part of the install but I don't see the option. I have been successful doing so after the install but it is a bit annoying to have to do it that way when it seems like the install should have this option.
I am reaching out once more to see if anyone can tell me if I am missing something in the install process that will allow me to do this?
Thanks in advance.
Future guru in the making.
View 19 Replies
View Related
Sep 19, 2007
Hello,
I have a requirement to have all the transaction logs for our SQL Server databases go to a drive letter L: - I would like to do this during the install process but I don't see any way to specify in the setup. I am able to go in and do it after the install but it is a bit of a pain to do.
Anyone know how I can do this in the install itself?
View 4 Replies
View Related
Apr 10, 2008
hi,
Can you guys please give me the steps of how to create a destination file dynamically. What i mean is for example i want to get everything from a table and ssis should create a file distination according to today's date and save it in a specific folder.
thanks
View 7 Replies
View Related
Dec 14, 2006
I have developed an SSIS package which uses an XML configuration file for connection information. It runs fine both from Visual Studio and from an Agent job after I specify the location of the XML configuration file in the Agent job definition.
My question is regarding the target location of the XML configuration file after deployment. I do my SSIS development on a local workstation with SQL Server 2005 DE and Visual Studio 2005 installed on it. I deploy my SSIS packages to a remote SQL Server 2005 SE server. Both machines are running SP1. I double-click the package manifest from my local workstation and specifiy the remote server in the deployment utility. The package gets deployed to the remote server just fine but the XML configuration file ends up on my local workstations C:Program FilesMicrosoft SQL Server90DTSPackages path. I would have expected that the XML configuration file would have gone to the same path on the remote server, where the package itself was deployed. Obviously, when I run the deployment utility from the remote server, it stores the XML configuration file in the same path on the remote server. But this is not a workable production solution.
View 1 Replies
View Related
Jul 6, 2006
I've been working on package configurations here, mainly trying to figure out what works, and how. If I use an XML configuration, and and don't specify the path, ie just typing in test.dtsconfig, it creates the config file on my desktop. When I execute the packge in BIDS, it seems to be looking in that location. If however, I execute the package manually on the filesystem, test.dtsconfig must be in the same directory as the package (which is what I would expect). Why does this work differently in BIDS. Also, if the package is deployed to a SQL server, where would the configuration file need to be in this case? Thanks for the help.
View 1 Replies
View Related
Jul 31, 2006
Currently
File Connection Manager Editor with
Usage Type : Create File
and
File: eg C:DBALogSSISPackage1.log
The desired Goal:
Rather than hardcode the file directory for a log file
eg C:DBALogSSISPackage1.log
can the definition be parameterized.
For example \MachineNameDBALogSSISPackage1.log
where MachineName is the location where the package is phyiscally
located.
View 6 Replies
View Related
Oct 12, 2015
We have 1 tb of dbsize of 3 different db which include ,mdf and log and mdf file and also how some sql jobs ile daily and weeklybackup jobs .what is the best option to copy these db and sql jobs . Is there any tool tools available which can copy these file over the network .How much time will it take
1) If we go in for fullback and restore option ?
2) make the sql server db offfine and copy and paste these mdf ,ldf,log file to different location ?
3) what would happen to the will database login accounts , cross database ownership chaining ? etc
View 2 Replies
View Related
Oct 5, 2006
I have an SSIS package that I created that looks like the following:
Execute SQL Task [Determines the file name]
Data Flow Task
Source - Query [Grabs the data]
Destination [Saves data to file on network drive]
Send Mail Task [Tells user to get file from network]
When I run this package inside Microsoft
Visual Studio I get the results I want. It sends the file to the
network drive and because there are SSN's in the file, it doesn't
actually e-mail the file but puts it in a secure location for the
person to get the file.
When I schedule this in SQL Server 2005 I get the file saved to: c:windowssystem32dynamicfilename.csv
So somehow it is still able to determine what the file name should be, but instead of sending the file to the right destination when you run it through VS, it flops out and sends it to this wacky directory.
I've
spent too much time on this and was wondering if someone can point me
in the right direction without going to the extent of writing a script
like this suggests. thread1555-1265108
Thanks,
Keith
View 6 Replies
View Related
Feb 27, 2007
During installation of SQL Server 2000 I set the default location for data files as D:MySQLServer which resulted in the location D:MySQLServerMSSQL.
I then installed SQL Server 2005. I do not remember being given the option of specifying location for the Data Files. Then I read that the location for named instances is deteremined by the first installation of SQL Server. The location for the data files for SQL Server 2005 turned out to be MSSQL.1 but under C:Program Files.....
I want the default location for SQL Server 2005 to be under D:MySQLServer, something like D:MySQLServerMSSQL.1. How do I do I change the default location for the Data Files.
Jay
View 9 Replies
View Related
Jan 15, 2008
Hi,
I am using SQL Server configuration indirectly with the connection to it configured in an XML configuration file with the location of the XML file and the connections all being different between development and production. I deploy to the file system using the SSIS Deployment Manifest/Package Installation Wizard.
It seems that sometimes the path to the XML config file is updated in the deployed packages and sometimes it is not.
When the path is updated, all connection strings (as displayed in the Connection Managers section of the Execute Package Utility) are displaying the new values as well.
When it is not, it looks for the XML file in the development location and since it cannot find it all configurations still point to the development connections.
Does anyone know of any conditions when the location of the XML config does not get updated and when it doesn't?
Thanks,
Sven
View 6 Replies
View Related
