Translate DSTX File
Dec 4, 2007Is it possible to translate a DTSX/XML file into VB.Net or C# code, like you can do with a WSDL file? If so, is there a SSIS utility or third party available? Thank you.
JGL
ADVERTISEMENT
DSTX - Failure On Automatic Run
Jan 8, 2008
We migrated a DTS Script from the old SQL2000 Server to DTSX to run on the new Server.
The script is scheduled to run at 3:30am. When it is executed via the scheduler it fails. If we execute it manually everthing works fine.
The history isn't a big hint either, because the error it contains is always the same, whether the script finished successfull or not.
But if it helps - here it is:
Code BlockDescription: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
So the above error can't be the reason for the failure!
The difference in the history is just the last line - the package executed successfully, step succeeded
OR: the package execution failed.
No other hints.
In both cases the step is executed as NT Authorityetwork service. Just the "caller" of the whole script is different, manually it is called by the domain admin (=SA), the scheduler calls by its "working plan"
Can you give me some advice what I can search for or what I can try to fix this (strange) behaviour.
thanks in advance, regards, Rene
Translate Function
Nov 8, 2007Can i use Translate function in sql server query? How i ll use? In my oracle, i m using a Translate function, but i want to run that in sql server. How to do that?
View 3 Replies View RelatedTranslate Function?
Jul 20, 2005Hi allIs there a TSQL function like Oracle's Translate function? Where:Translate('13,000Miles','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP QRSTUVWXYZ, ',' ') wouldreturn '13000'?I'm trying to extract a number from a string. REPLACE doesn't quite dowhat I want.Thanks
View 1 Replies View RelatedHow To Translate Into SSIS??
Mar 12, 2007Hi everyone,
My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:
sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"
rs.execute sql
WHILE NOT RS.EOF
STUFF
ANOTHER SELECT AND ANOTHER LOOP
...
...
INSERT AND DELETE STUFF
END WHILE
Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.
I don't want to do cursors or something like that with T-SQL.
Thanks in advance for your inputs/help and regards,
Can I Use Translate In Sql Server,is It A Keyword
Oct 3, 2006im trying to find the equivalent of the below oracle code in sql server.But to my knowledge i feel "translate "is not supported by sql server. so please send me the equivalent sql server query for this.SELECT 'http://ploft.com/ploft?y='||translate(comp,' ','+')||',+'||translate(california,'','+')||',+'||ca||'+'||pzINTO :ls_MAP FROM project WHERE portno=:dbno;
View 3 Replies View RelatedTranslate Numbers To Words?
Aug 30, 2013In the below query i need to display the amount in words also. How to do that in sql server 2008 R2?
SELECT ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS[TOTAL_CONCESSION_GIVEN] FROM BILL_MASTER BM
Translate Sql Statement Into SQL-92 Standards
Jul 20, 2005Can anyone help me translate this statement from using the legacyouter joins to the SQL-92 standards?Select CA.* From Customer C, Shipper S, Customer_Order CO,Cust_Address CA Where CA.Customer_ID =* CO.Customer_IDand CA.Addr_No =* isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)and C.ID = CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Try as I may, I simply can't find a working left, right, or full outerjoin statement that would give me the same results as the abovestatement gives. I thought this was suppose to work but don't knowwhy it doesn't. Anybody care to try or perhaps tell me why thestatement below doesn't work:Select CA.* From Customer C, ((Customer_Order CO left outer joinCust_Address CA on CA.Customer_ID = CO.Customer_ID) left outer joinShipper S on CA.Addr_No =isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)) Where C.ID =CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Thanks,Tony
View 4 Replies View RelatedHow To Translate Varchar Into Varbinary?
Apr 3, 2007Hi everyone,
We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".
Does anyone have any idea about that?
Thanks for your time and inputs,
How To Translate Oracle Decode
Aug 2, 2006Hi,
How to translate oracle Decode without changed code I mean:
I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I
Wrote this function
select dbo.fnDecode( 1 , 2 , 3 )
first parameter always int, and the others parameters could be char, int or float.
I would like to test first value and return second or third value
-> sql_variant for all parameters, ok
but I can use + or -
I can't do this
select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )
If I put cast ok, but I is there another way, overload this call?
With clr doesn't work, because Sql Server doesn't accept function overload calls from C#
Any ideia????????????????????
cheers,
How To Translate DTSStepScriptResult_DontExecuteTask Into SSIS?
Mar 15, 2007Hi everyone,
We're struggling ourselves with this and we are stuck.
Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?
Main = DTSStepScriptResult_ExecuteTask
Thanks for your input and regards,
How To Internationalize And Translate RDL Files
Aug 10, 2007Hello,
Im new to this forum and my knowledge about RDL is very limited. We are developing software localization tools and some of our customers have requested a solution to translate RDL files.
Our tool has a generic XML parser so I reviewed the RDL documentation but didnt found any information about which XML elements do have translatable content. If there anyone who knows this or is there any documentation around describing which elements must be translated?
I also saw some proposals regarding the internationalization of RDL files. It seems that the proposed solutions were designed for developers and not for professional translators. In an environment where you have to translate several hundreds of RDL files into lets say 20 languages you cant expect that translators start to code RDL files. Are there any other approaches that can be used for a more efficient translation of RDL files?
Any feedback is appreciated.
Achim Herrmann
Translate Equivalent In Sql Sever
Apr 24, 2008
Hi,
I have an alphanumeic string as one of the column values.I want to get numeic value out of the string.
I was planning on translating all the characters of the alphanumeric sting to spaces and then using ltrim and rtrim on it should give me the required numeric sring.But there is no translate function in sql server
Example: abcd123xy 123
xyz45 45
Can someone provide me a way to get the numeric value out of the strng.
Thanks
Bobby
Translate Rows To Columns - SQL2000
Apr 30, 2008Hi
I want to translate the following
SERIALNO TYPE AMOUNT
1111 1 251111 1 5 1111 1 7 1111 1 3 1111 1 101111 3 1 1111 3 2 1111 3 4 1111 3 3
2222 0 1
2222 0 7
2222 0 1
2222 1 5
2222 1 1
2222 1 2
2222 1 3
to output in the following format
SERIALNO SUM of MIN SUM of MAX
1111 50 10
2222 9 11
For each SERIALNO I want to sum the AMOUNT, spliting the columns into 2 based on TYPE
There are only ever 2 values for TYPE for eachs serial no.
I don;t need to track the value of TYPE.
I assume you would use a MIN & MAX function but Im not sure how to write this into a view.
thanks...
Analysis :: Translate Named Sets
May 19, 2011I have some Named Sets that I want to translate. I have done exactly the same way as with the Calculated Members but the Sets don´t use the translations? Any ide why?
View 2 Replies View RelatedOracle Translate Function Equivalent In SQL Server
May 5, 2006Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
How To Compile/translate A SSIS Package In Japanese
Jan 16, 2007Hi,
I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?
Thanks in advance.
Translate This Stored Procedure Call To DDL Statement
Jan 30, 2008 Hi,
to fully support SQL server 2005, I'd like to use DDL statements instead of deprecated stored procdure calls, I'm having trouble "translating" this one :
sp_fulltext_catalog 'catalogName', 'start_incremental';
The only option available with ALTER FULLTEXT CATALOG are REORGANIZE and REBUILD.
Any idea ?
Thanks for your help
Oracle Translate Function Equivalent In SQL Server
May 5, 2006Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
Outer Join Oracle Query Translate In Sql Server
Apr 16, 2004Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.
SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1
Any Help will be appreciated.
Transact SQL :: Strategy To Translate Column Data Into Distinct Rows
Aug 27, 2015I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:
PrimaryID SKUChange DateChange LocationIdChange StateChange
10003 TRUE FALSE TRUE FALSE
etc...
The output I'd like to see would be like this:
PrimaryID Field Changed Previous Value New Value
10003 SKUName SKU12345 SKU56789
10003 LocationId Den123 NYC987
etc...
The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.
Reporting Services :: Parsing SSRS Config File And Dynamically Changing File Path Of Config File In Code
Sep 2, 2015Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
File Will Not Attach To SQL Reason File Is Not Primary File
May 10, 2007I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
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.
Sep 2, 2007Greetings, 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
Attach Mdf File And Data File Without Log File
Mar 1, 2006how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?
Jun 18, 2015I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
Help Please (Check File Exists/ Archive File/ Check If File Empty)
Mar 10, 2008
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?
Jul 31, 2014I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
SQL Server Admin 2014 :: Creating Additional Data File For A Particular File Group?
Jul 6, 2015For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
Integration Services :: Flat File Error File Being Created In-spite Of No Errors
Jun 23, 2015I 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.
View 5 Replies View RelatedIntegration Services :: Renaming / Replacing Part Of A File In File System Task
May 14, 2015I'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.
Integration Services :: Get FileName Fo Each File Created Via Dynamic Flat File Destination
Jul 24, 2015Need 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:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
How To Create, Install And Deploy Dts File For Sqlserver 2000 And Dtsx File For 2005 ?
Apr 24, 2007Hi,
I am looking for tutorials about how to create dts et dtsx files.
Thanks for your help.
Arioule.