Hi,
Every day I ftp a zip file. Inside the zip file are 4 text files. Every day the names of the 4 files are different, so I can't know beforehand what the names are.
I am wondering what is the best (most efficient) way for me to get the file names inside variables? Should I use a script task? Or is there another way to do this?
Thanks
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
I need to move specific files from a server to another server on a monthly basis. There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server. I would like to easily add or delete the file list as needed. I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them. With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable. Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....
View 10 Replies View RelatedI'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.
Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.
Can anybody tell me how to accomplish this.
Hi All,
I am using query analyzer to execute my sqls .
I want to take out .mdf and .LDF files and put it in another machine .
But there is no .mdf or .LDF file with prefix as my database name . I think the file name might be something else .
Please help me in finding out the .mdf and .LDF of a particular database .
Thanks ,
Sushi
Hi ALL!
Our application needs to search for exact match of file names (which will be 70 characters in length) in a table which has thousands of file names in its file_name field. What is the best way to this? Shall we index the table on file_name column? Or any othe way, as the application will do this search several times all day. Any ideas are appreciated.
Thanks.
KSN.
Hi,
Does anyone know how to change the logical file name of a database?
Appreciate any help.
Steve
I'm performing a full backup of a database, and the file name that I am creating for the backup is truncating. This keeps happening and can't figure out why.
I have a stored proc that I run:
CREATE PROC usp_Backup_old_srvra_to_srvrb
AS
DECLARE @DOW varchar(10),
@Filelocation varchar(50)
--Get Day of Week and set backup file name
SET @DOW = DATENAME(dw,GETDATE())
SET @FileLocation = 'SERVERAE$MSSQL7BACKUP'+ @DOW + '__old_BKUP_SRVR_0418_0800.bak'
BACKUP DATABASE TEST
TO DISK = @FileLocation
WITH INIT,
STATS
RETURN (0)
The backup file that was created ended up being:
THURSDAY__old_BKUP_SR
Any idea why it didn't pick up the whole file name that I wanted?
I receive several TXT files daily that need to update information in SQL Server databases. The process requires that all TXT files be appended to a master file and also update individual files' information based on the TXT file name. For example:
File TABLE1_x_ddmmyy.TXT (ddmmyy = date, x = "O" or "B") is to be appended to the master file and also update SQL table "TABLE1" by setting a flag for those records in the table that match a unique key that is provided in the TABLE1.TXT file.
In VFP, I had the following process in place:
a) open the TXT file.
b) read its file name and open the corresponding VFP file
c) update the VFP file based on the key provided in TXT
d) append the key to the master file.
e) repeat c-d for next record in TXT
f) repeat c-e for next TXT file
Using the same process with ADO takes a considerable time since I am processing one line at a time.
Is there any way to do this using a DTS package of some sort? How can I read the TXT file names in SQL Server?
Thank you.
I have below requirement and it needs to be addressed using sql.
There are 18 text files(file names are dynamic) and each file having different row counts in some “xyz” folder.
Here, I need to generate 19th text file which has the list of file names with row counts.
I am planning to import all of the text files into database and trying to generate 19th one.
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?
View 1 Replies View RelatedI am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.
I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.
thanks,
Scott
Hi SQL server experts,
I'm trying to restore from a backup file.
I need to restore the datafile and logfile to different names.
I modified the options page, changed the 'Restored As' file name.
I clicked on the General page and Clicked on the Options page again, the old values came back.
How do I handle this?
Thanks in advance.
Pingx
Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
View 14 Replies View RelatedBackground:
I am new to SSIS.
I have a data flow task within a For Each Loop Container.
Its reading a Demilited Flat File and inserting into a DB table.
The Flat File Source reads a specified folder and picks up all files with extension .txt
Question:
How can I record and save the file names the package picks up and loads it in ?
For eg:
If I have under C:Test folder
File1.txt
File2.txt
File3.txt
And the package picks up all the files above and loads them in, is it possible to read the file name its processing ?
I have a need to read these file names ( in this example File1,File2,File3 ) and store them in a DB table.
Appreciate your help.
Thanks in advance.
AkB
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
Hi,
A collegue of mine is having some issues. I hope someone can help
Hi all,
This is my first call for technical assistance so go easy on me.
I'm having a problem in one of my SSIS packages. In brief, the process involves copying the rows from an untyped table to a typed table. There are about 45,000 rows in this table and during the copy ("OLE DB source" to "OLE DB destination") the process appears to hang for about 5 minutes eventually crashing with a "The buffer manager could not get a temporary file name. The call to GetTempFileName failed."
After several attempts using all the trace info I could master this is the order of events with some interesting numbers and facts:
1. The SSIS package goes swifty through "Validation", "Prepare for Execution" and "Execution" in less than 1 second, with "Execute phase is beginning" being the last message on the "Progress tab".
2. Using Performance counters I note that in the next 5 mins the values for "BLOB bytes read" slowly rises and then after a couple of mins so does ""BLOB files in use" the latter reaching a figure of 65534
3. When this figure is reached, SSIS starts creating thousands of zero-size files with the name DTS####.tmp (where #### is hex e.g. DTSB4C1.TMP) in the TEMP folder (C:Documents and Settings<username>Local SettingsTemp in my case).
4. When I started running this package there were 130 files in my TEMP folder; As soon as the combined total of files in TEMP reaches 65664 (i.e. 65534+130), SSIS starts producing the errors list which includes the one I listed above and eventually it clears the TEMP folder down to the original 130 files.
5. My conclusion (thus far) is that SSIS creates all these 1000s of tmp files but in my case hits some kind of maximum (either a folder limit or runs out of hex combinations for the file names) and then crashes.
6. The only thread I found on the internet suggested setting up an environment variable "BLOBTempStoragePath" and assigning a value of "C:Temp1;C:Temp2;C:Temp3;C:Temp4" so that SSIS can span across a number of "temporary" folders instead of the 1 default folder contained in the "TEMP" environment variable.
7. Setting the above environment variable in Windows 2000 did not work for me (tried it as both a user variable and a system environment variable). So here are the facts so far - ANY assistance will be hugely appreciated - I have no idea why all these temporary files are being generated - I have created SSIS packages handling data sets 10 times bigger than this one without these problems so I don't think it's size related.
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.
For instance:
database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf
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].
Hi,
On x64 versions of Windows server 2003, there is a folder called "Program Files (x86)". The use of parenthesis in this name has caused a few issues so far, particularly with connecting SQL Server 2005 to Oracle using SSIS. After several hours of searching I realized that the parenthesis was causing it, and eventually I found that the best way is to avoid creating such a name while installing the OS itself, by using winnt.sif. This would be better than renaming it and changing registry entries after the installation is done.
I realize this may not be the best question to ask of Microsoft, but why not just call it "Program Filesx86" by default so no special procedure has to be done to get around these issues? The use of parenthesis is likely to cause even more compatibility issues with other software later?
BTW I use Microsoft products regularly and love most of them
Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedHello,
I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.
My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible?
Regards
RH
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
Ok I am faced with working with XML on a regular basis, which is fine.
DECLARE @ViewSN INT
IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType>
insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments)
select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
[code]...
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
Im using the ImportExport wizard to export the top 5 lines from a MS Sql table into a fixed format (ragged?) file. But I want the first record to contain the column names of the exported fields so I selected the Column names in the first data row? option of the Choose a Destination? box. When I run the Package I get:
>>>
Information 0x402090dc: Data Flow Task: The processing of file "C:arkingdogExportWithheader.txt" has started (SQL Server Import and Export Wizard)
Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)
>>>
When I de-select the Column names? option, the package works fine. Other than manually, how can I et the column names in output file?
TIA,
Barkingdog
Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
Hello guys,
here is my issue.
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2
1,2
3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this-
Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2
1,2
3,4
Col1,Col2
5,6
7,8
Can anyone help me to get the file as shown in the highlighed
Any help would be appreciated .
Thanks
Hi,
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
Any help appreciated,
TIA, Anthony.
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies View RelatedWe run std 2008 r2. I'm looking at the files this transform is complaining about. They seem to be named appropriately. The customerid folders don't exist when this runs. I'm going to put one in place to see if that is the problem.
The errors i'm getting are...
[Export Column [22]] Error: The file name "c:usersmyuserid heprojectnamecustomeridafilename.doc" is not valid. The file name is a device or contains invalid characters.
[Export Column [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Export Column" (22)" failed because error code 0xC020207F occurred,
and the error row disposition on "input column "FILENAME" (29)" specifies failure on error. An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (22) failed with error code 0xC0209029
while processing input "Export Column Input" (23). The identified component returned an error from the ProcessInput method. The error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
I've been asked to create a new SQL server and restore an old server's DBs to the new server.
I'd like to generate a list of the DB names using powershell to distribute to their creators, in case some of these DBs are no longer needed.
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
Below is a sample:
Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?