Dynamic File Names For Raw Files Created By Conditional Expressions
Feb 26, 2007
Hello,
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?
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.
For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :
SELECT Division, [2] AS CurrentPeriod, [1] AS PreviousPeriod FROM ( SELECT Period, Division, Sales_Amount FROM Sales.SalesOrderHeader WHERE ( Period = @period OR Period = @period - 1 ) ) p PIVOT ( SUM (Sales_Amount) FOR Period IN ( [2], [1] ) ) AS pvt
Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).
Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900
What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?
Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.
[2] AS CurrentPeriod, [1] AS PreviousPeriod
FOR Period IN ( [2], [1] )
I have tried to use the @period but it doesn't work.
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
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....
I 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.
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)
I want to return a subset of data from my 'items' table using a stored procedure based on a number of parameters as follows :
customer code varchar(8) userid varchar(10) status tinyint
where the customer code can be any valid customer code OR blank, the userid can be any valid userid or blank and the status can be 1 (item is open), 2 (item is closed) or 3 (item is open or closed, i.e. no filter on status)
Is there a better way to achieve this than the following disaster:
IF LEN(RTRIM(LTRIM(@custcode))) = 0 BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE BEGIN IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END
END ELSE BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam
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 am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I've about 8 output files from the "conditional split". For each run, only one condition is valid. When I run the package with value 0, I need to get only one output file with condition 0's data. That's working fine but I'm getting all other output files with 0KB, which I don't want. I'm getting like: Var0.txt ---------------> 2KB Var1.txt ---------------> 0KB Var2.txt ---------------> 0KB Var3.txt ---------------> 0KB ........... etc.
Created a stored procedure which returns Selected table from database. I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure ( @condition varchar(20), @ID bigint, @date1 as datetime, @date2 as datetime )
AS /* SET NOCOUNT ON */ IF @condition LIKE 'all' SELECT CllientEventDetails.* FROM CllientEventDetails WHERE (ClientID = @ID)
IF @condition LIKE 'current_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom <= ISNULL(@date1, EventFrom)) AND (EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE '' SELECT CllientEventDetails.* FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
Hi is possible to create dynamic column name example Declare @StartDate as dateTime Select @StartDate = '2004-06-05'
select SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)], SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)] from table1
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
I am trying to achieve something a bit complex (or at least appears to be for me).
I have the following table structure:
UID, GroupID, ColumnName, ColumnValue
here is some example data:
UID, GroupId, ColumnName, ColumnValue 1, 1, MAC Address, 8a7sd87sad 2, 1, IP Address, 192.168.1.100 3, 1, Name, John 4, 2, MAC Address, 09a8sd098as 5, 2, Name, Steven
and here is what I would like the example to come out like:
GroupID, MAC Address, IP Address, Name 1, 8a7sd87sad, 192.168.1.100, John 2, 09a8sd098as, NULL, Steven
It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...
I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:
Code Snippet
DECLARE @ColumnName varchar(128) DECLARE @Sql varchar(255) SELECT @ColumnName = [ColumnName] FROM tblSourceData SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData' EXEC (@Sql)
Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?
I'm having an issue trying to load some old CVS files created in the year 2005. I'm using SQL server 2008 express edition for now. I've never seen files in this format. For reason, i can't go into, i can't user SSIS and need to load them using the bulkinsert comand. the files contain about 20 fields with a mix of numerics and text. the text fields are delimited by 2 sets of quotes, and a , and the numeric fields by just a , with each line contained in double quotes.
ex """abc"",12,198504,""tom"",""Smith"",49,3322,""mai n st."""
how to define the format file so that i can remove the "" quotes before and after each text field and how to remove the set of quotes that each record is enclosed with.
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
I cannot see the file created in the directory. The account under which sql server the agen job run have full privileges on it and is sysadmin.Then i run the Command in ssms
BACKUP DATABASE [F1SB] TO DISK = N'F:SqlBackup2014<server>F1SBFULLIGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])
and I get this error message:
Msg 3013, Level 16, State 1, Line 13 BACKUP DATABASE is terminating abnormally.
This index files are created in my hidden folder. Now, my problem is, 1) Is this files associated with Full Text Search. 2) when the file will be created. 3) For what purpose, this files are stored.
I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file. I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the data from Source to Destination.
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say Select OrderId from Order. But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name???
Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...
Hi I have developed an application in ASP/SQL server 7. Ths system is single user. One of the tables is updated by usr actions( say Table A). To make it multi user. I want to create table such as A_Username. How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username . Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option? pls suggest
I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.
With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.
When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).
How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...
I have set the environment set for AutoRecover (for every 3 minutes and Keep information for 7 days under the SSMS 2014 Menu: Tools -> Option ->Environment -> AutoRecover).
I've rebooted the box and restarted the SQL Server service and nothing seems to create the files.
We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int; declare @table table(rno int, name varchar(max)) declare @b varchar(max) insert into @table select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables declare @tblname varchar(max)
