I am trying to move a flat file to an archive folder and then rename the file to include the date and time of runtime.
I have set up a File System Task to move the file which works fine and a second File System Task to rename. The IsDestinationPathVariable is set to TRUE and the DestinationVariable is called User::Error_Log_File_Rename. There is an expression as follows:
If I go into the expression and evaluate it it will give me the current date and time. However when I run it as part of the package say 10 minutes later the file is renamed using the same date and time from 10 minutes before when I evaluated the expression. If I wait another 10 minutes and run the package again it still retains the original date and time.
Is there a way to get the expression evaluated with the current date and time during execution of the package?
We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)? I would really appreciate help on this. Thank you.
I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
We have a SSIS package which is accessing a remote Windows file share location. The package first moves the file from folder-1 to folder-2 and also renames the file during this process. Then the package reads the file (using a flat file connection FF_SRC) from folder-2 and renames it again after processing it successfully.
The permissions given to the user executing the package on folder-2 are: Read+Write+Modify+List folder contents.
We are facing an error:
Code SnippetFile or directory "Z:folder-2XYZ.txt" represented by connection "FF_SRC" does not exist.
We are getting the above error when the SSIS package is trying to rename the file the second time in folder-2.
However, the file exists in folder-2.
The OS is Windows 2000 Server SP4.
Any ideas why this could be happening and how it could be resolved?
We are using an ADO.NET provider in SSIS to read data from a SQL Server 2000 table that contains DateTime columns to write to a Flat File Destination. When the date values are written to the file they are formatted in TimeStamp to the 10th decimal position; e.g.€œ2006-04-24 12:00:00.123000000€?. Since SQL Server supports values to Timestamp(3), we need to truncate the last seven zeros to put the data in this format €œ2006-04-24 12:00:00.123€? to keep the file as small as possible.
Since we have several hundred DateTime columns in scope for our requirements we are looking for the least logic/effort to accomplish this task. We can do this via Data Conversion and Derived Column transformations to cast the dates and strings but it is very labor intensive. It would be something like singing 99 bottles of beer on the wall eight times in a row with each verse taking 3 minutes each. Yikes.
We have tried casting the DateTime columns to varchar in the SELECT statement but receive this format €œApr 24 2006 12:22PM€?.
Is there a configuration we've missed that forces timestamp(10) with non significant digits?
I'm having trouble working this out in SSIS. I am trying to use a File System task to rename a file using an expression so that file.zip will be renamed to filemmyy.zip at the end of every month (for instance this month would be file0506.zip).
I am using the destination expression variable. But I'm not sure what to put for DestinationConnection. It seems to want a file name, but the file name is going to be variable, so I'm not sure what to put.
I have a table of parts and want to display some statistics from it. The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query: SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)" OnSelecting="sqlPartCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> </SelectParameters>Code-behind: protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; } 2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get. SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)" onselecting="sqlPartYearCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> <asp:Parameter Name="DateUseByID" /> </SelectParameters>Code-behind: protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; // ? How do I change the date in the field to be in "2008" format and then put that in the sql query? //e.Command.Parameters["@DateUseByID"].Value = currentYear; //currentYear currentDateTime = DateTime.Now; }
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT (TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
I have had a go at using a package with a script to rename and move a file and it works well by using a script task in a package with source and destination variables. See script at bottom But in my scenario the file thst comes in every day will have a slightly different name. It will be called "System_UT_INCR_BOOKINGHEADER_20080228000000.TXT" On the 28th Feb. The date part of the title changes everyday.
SO i need to adjust my "Source" variable which is currently just a string with a value of "C:DatafilesimportsSystem_UT_INCR_BOOKINGHEADER_20080228000000.TXT" So it will only ever look for that exact file name
Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main() Try File.Move(Dts.Variables("Source").Value.ToString, Dts.Variables("Destination").Value.ToString) Dts.Events.FireInformation(0, "", "File Moved Succesfully", "", 0, True) Catch ex As Exception Dts.Events.FireError(1, "", "Source file or destinations does not exist", "", 0) End Try
I'm moving a database (XYZtest) from the test server to the production server via sp_detach/sp_attach. I want the logical file names to be XYZ_data, rather than XYZtest_data, etc. I can easily rename the disk files, but how do I rename the logical file names?
Suppose I have an Excel file in D:Test with the name a.xls. Can I rename that excel file from QA of SQL SERVER in the same location or any other location? Subhasish
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
Is there a way to rename the logical file for a database. For example, if I am moving a development database into production, I can use backup - but the backup takes the logical file names of the database and puts it into my production server. Now I have a production database with "dev_data1" for a logical file.....Can I change that name....?
Hi, After executing a dts package to load data from a file to a SQL table. I need to rename the file after the load. Could any one give me a code sample so that I can add that as a task after the execution of the load in the same package
Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?
I am using a DTS package to extract data from a table and export it to an excel file. This task needs to run on a weekly basis and the filename should contain the date the file was created. I have sucessfully used the activex scropt below to rename .txt files but when I try to use it for Excel files it always defaults to the default filename I specified in the destination file properties. Can anyone show me how to do this for Excel files?
I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer
Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.
What will I need to do now to use old DTS and new SSIS packages on these clients?
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].
ok...i give up..... can this be done? (sql server 2000) all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp. here's my current working file output code: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P' i'd like it to be something like this: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P' but nothing seems to work.
I have set up an export package in SSIS and I would like to assign a file name based on the value in a table on my SQLServer. For example, my file name might be Monthly Reporting Package 20070731.xls where 20070731 is a value in my date table. I can retrieve my date by the following select statement: "SELECT Date FROM DecodeTables.dbo.Date". Can I set up a variable to look up this value and then concatenate it into my file name?
I am created a SSIS package to export data. I am exporting query data to a flat file to a different server. I tried to use the UNC path and it failed saying could not access the file. How can I create a SSIS package to export data from one server to another?
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp €œ12:00 AM€? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp won€™t appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
