SSIS Output File With PGP Encryption
Feb 13, 2007I am just wondering whether it is possible to have the output file from SSIS to be encrypted by PGP command line?
ADVERTISEMENT
SSIS : Flat File Input And XML Output
Feb 21, 2007
Hi All,
I want to know is it possible to have source as Flat File and destination as XML
Thanks in advance,
Shagun
How Can I Output Subset Of A Table To A Flat File In SSIS?
Aug 24, 2006Hi All,
I have a table A. I need output subset of a table A to a flat file using query, like:
select A.* from A inner join B on... ..... inner join C......where left(A.id, 3) = B.sid.... AND B.num between 100 and 200).
How can I do this in SSIS? Which data flow item I may need?
Thanks
SSIS - Output Table To Flat Fixed-width File
Oct 19, 2007I am new to SSIS and am having trouble with automatically setting up the destination output columns.
I am sure there must be an easy way to do this.
My table (source) has 86 columns in it of varying lenghts.
In my connection magagers, I have created one for the SQL Server (source data) and one for the flat file (destination output).
I have also created an OLE DB source data object and a destination Flat File object and set them up to the respective connection managers.
Finally I have linked the source to the destination.
Now when I look at the source, it shows me all 86 columns.
When I open up destination, there are no columns set up.
Problem: do I have to type in all the columns manually in the connection manager for the Flat File?
I would think there would be some automatic way that it would self-populate the columns over to the flat file destination.
Simple SSIS Package, Problems With Flat File Output
Sep 20, 2007Hello!
I want to make a very simple package: Export all rows in a table to a flat file.
This package I can create pretty much by only using the wizards.
Now to my problems:
1) I need the output to have this format:
H20070920161522
DS3 Plastpall trippelkrage 40 1
E00000000003
H is a header post, in this case with date and time following.
D is a details post, that is all the rows that was exported.
E is and end post, containing only the number of rows in the file, including H and E posts.
2) I need to set the file name dynamically, preferably using date and time to name the file.
I´ve done this very same thing in T-SQL, like so:
Code Snippet
USE AVK
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
SELECT *
FROM tempProducts
GO
CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
UNION ALL
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1
GO
IF @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
SELECT *
FROM tempProducts
DECLARE @date char(8)
DECLARE @time char(8)
DECLARE @sql VARCHAR(150)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql
--WAITFOR DELAY '0:00:10';
DELETE
FROM tempProducts
COMMIT TRANSACTION
END
DROP VIEW EXPORT_ORDERS
GO
But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell.
Pointers please
SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output
Mar 2, 2014I 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.
Integration Services :: How To Read A Specific String From Text File As Output Variable In SSIS
Nov 5, 2015I am downloading a webpage as a text file in order to read a specific string to assign it as a variable/parameter in order to create an output file name. I would like to know how would I be able to look for a specific string and output as another variable for the rest of the package.
2015 Conforming Loan Limits
------------------------------------------------------------------------
o _Loan Limits for Calendar Year 2015--All Counties _[XLS]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL_FLAT.xlsx>_ ,
_[PDF]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL.pdf>_
​ o _List of 46 Counties with Increases in Loan Limits for 2015
[Code] ...
To explain it a more better way, I have a sample webpage text here. I should be searching for "FullCountyLoanLimitList" appended by the current year (like FullCountyLoanLimitList2015) and copy the entire file name in the text file and assign it to another variable so that I can download that specific file using WebClient connection.
Encryption In SSIS Package
Dec 6, 2006Hello,
I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.
Encryption In SSIS Package
Oct 27, 20061) We have OLTP DB on 2000 & OLAP DB on 2005.
2) We have decided to use SSIS for migration and interface is BIDS to develop, build, deploy & install SSIS packages.
3) We even have to encrypt some tables colmns data while reading from source so that it is secure in network and gets into target database as such requiring decryption for reports and front end display.
4) For database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.
Can you please give links on these 2 encryption mechanisms.
5) Where can we use Symmetric Encryption & Asymmetric Key Encryption i.e on columns of tables i.e. data or password & based on what do we decide which to use?
6) Please post links/code to generate these keys. Where do we fit this code in SSIS package & where do we use the keys. Can you give me code for keys fitted into
following query:
insert into EmpDest
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
go
Thanks in adavnce.
Encryption In MDF File Possible?
Apr 28, 2006I am working on a distributed application that will use a SQL Express 2005 MDF file for the primary application data storage. The program will be storing sensitive data and I would like to encrypt the data in it.
I have searched through the forums and cannot find any reference to how to enable encryption for an MDF file.
I thought about writing my own encrypt / decrypt functions, however, I'm using databound controls and do not have a home-grown data access layer that I can tap into to implement the encryption.
When I originally added the MDF file to my application, under the "Advanced" settings, I saw that I could switch an "Encryption" property to "True"... figuring that would do the trick. However, when the MDF file is created I get an error stating, "The instance of SQL Server you attempted to connect to does not support encryption".
What is the trick for encrypting data in an MDF file that is being used with databound controls? Remember that this will be a distributed app - so if there are any customizations that are required on the SQL Express side to support this - I will need to find some way to configure SQL Express on the target machines during my bootstrap install of it.
Any help would be greatly appreciated! Thanks!
SQL Server 2005 Encryption And SSIS
Dec 21, 2007Hi everyone! I have a problem and I was hoping someone could help me with it.
Here's my scenario:
I have to access to an intermediate SQL Server 2005 database, which I cannot change or alter. In this database is information that a I need to retrieve and put in our website database. One item of information is a persons SSN which is stored in a varbinary field and encrypted using a certificate.
In my Data Flow task which processes this information I am using an Ole Db Source to retrieve the information with the SQL Script:
SELECT
CAST(DecryptByCert(Cert_ID('Certificate_Name'), [IntermediateDB].[SSN]) AS VARCHAR) As SSN
FROM
[dbo].[IntermediateDB].[SSN]
BTW, This script runs fine from within SQL Server Management Studio. It decrypts the SSN to the appropriate value. However, when I run it in SSIS, I receive a truncation error which is no small surprise b/c the SSN value is in a large binary format. I.e:
0x55 0x56 0x69 0x99 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 ...
It goes on further, but for the sake of brevity and my own sanity I thought I should truncate it.
It seems like SSIS's use of the certificate is failing (although I don't get an error for that) and is simply pulling the encrypted SSN from the intermediate table.
So my first question would be, is this actually what's going awry? And secondly, is there a way to fix this without touching the source database??
Thanks! I greatly appreciate it! And Happy Holidays!
Derek
SSIS: Encryption Not Supported On The Client
May 19, 2006Using SQL Server Business Intelligence Development Studio, when I try to create a new OLEDB connection manager and I try to connect to the SQL 2005 DB, I get an error message saying that "Client is unable to establish connection Encryption not supported on the client"
This is ONLY in SQL Server Business Intelligence Development Studio!
If I connect to the same server using SQL Server Management Studio and using server type Integration Services, everything works just fine.
However this way I cannot create a working SSIS package from the client, if not working locally on the server, which is a bit nonsense.
Any suggestions?
SQL Data File Encryption
Jan 4, 2007Is there any way to encrypt the sql server mdf and ldf data files in SQL Server 2005 without using EFS or creating an additional system user? Only one application which has the password embedded (i know this is a security risk) should be able to connect to the database and access the mdf and ldf files.
Greetings
Roland Mayr
Password Encryption In XML File
Nov 8, 2006I am unable to encrypt password of sqlserver database in XML file by Package Configuration in SSIS.
SQL 2012 :: Does SSIS Catalog Use Transparent Data Encryption
Jul 10, 2014In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 0 Replies View RelatedSQL 2012 :: SSIS Catalog Using Transparent Data Encryption?
Aug 7, 2014In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 5 Replies View RelatedGenerate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?
May 16, 2008Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
File System Task - Output File Variable Syntax????
Feb 7, 2007
Hi
This should be incredibly simple and easy, but I can't find any examples of how to do this.
I just want to make a File System Task move a file, and have the destination be filename + date and time. For example \serversharefilename02072007.txt
What syntax do I use in a variable to make this work?
Thanks
SQL Security :: Encryption 2005 - User Defined Function For Encryption And Decryption
Oct 7, 2015I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
                    WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
Transact SQL :: Output A File With A File Header
Aug 27, 2015In my SSIS Package, I have to write my [FileHeaderRecord] row, then my [BatchHeaderRecord] row, then my details. How can I do this in a SQL Server Query? When I try my SSIS, my file looks like so..
FHTEST 00000208262015Â Â Â Â Â Â Â Â Â Â Â Â BH000208262015Â Â
I want my BH, Batch Header data, to appear on a new row in the file.Do I have to build a dynamic query to do this?Is there any trick in SSIS to do something like this?I did try creating separate Data Flow Tasks to Query the [FileHeaderRecord] and then use a Flat File Destination and then another Data Flow Task to Query the [BatchHeaderRecord] and use a Flat File Destination again NOT overwriting the file.
Output Column Width Not Refected In The Flat File That Is Created Using A Flat File Destination?
May 11, 2006I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?
Any inputs will be appreciated.
M.Shah
SSIS SqlDumper Output
Jan 27, 2006Hi,
I am posting this for a colleague of mine. Any thoughts or recommednations?
"Is there any way for me to analyze the sqldumper files? One of my pipeline components is causing SSIS to dump and I'm trying to key in on what may be causing the issue. "
Thanks,
- Joel
Output File
May 29, 2001Hi all!
My question is quite simple I think... I am directing the result of my query to an output file my problem is how to set the rowsize of my output file to more than 256 (that is the default maximum)? My query result is quite long per row (assuming one column) in query anlyzer, I can easily adjust the result to more than 256 but the result in the output file is truncated because the ouput is more than 256 characters per row...how can I set (in the code) the length of the row of the result in the output file... thanks for the help... this is quite urgent...
Lhot
Output File
Nov 16, 2006venkatesh writes "Hi all,
I have a simple sql Query, when I execute the query the result should also be written to a text file. Can I do that in SQL 2000?
Thanks
-Venky"
SP Output Into A File
Jul 13, 2007I have SQLServer7. I am running some stored procedure. I would like to know how I can redirect the output of the Stored_procedure run into a file?
R
Need Help With XML Output To File
Sep 18, 2007I'm using SQL Server 2005 / 9.0.3042
I'm not new to sql server, but making my first experience with xml in sql server 2005.
I have a query like this (based on <Table> with neccessary data):
SELECT TAG, PARENT, <columns...>
FROM <Table>
FOR XML EXPLICIT
This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.
I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.
Any direction would be greatly appreciated!
Output To A File
Jul 18, 2007Hi, All
I have a header row and a footer row and a bunch of detail data I have managed to split up into temp db tables, know i need to know what would I need to do to write the header , detail data and footer to the same file after all my validations have completed , this would be a different file then the one I originally imported all the data from (append to text delimited file).
Idealy I would only like to change the header and footer from the original file.
Any ideas - I am thinking of writing a application that does this for me and just executing it from
SSIS but I would really like to stick with standard SSIS components first before starting to write my own stuff.
Thanx in advance
Rudolf Erasmus
EDI File Output
Oct 2, 2007any good example of extracting data from sql server 2005 in EDI file format? I need to generate EDI files from sql server
thanks
SQL 2012 :: Moving SSIS Solution To Another User - Reset User Encryption Key
Oct 22, 2014I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
SSIS - ERROR OUTPUT In OLE DB DESTINATION
May 16, 2008
Am new to SSIS and developing a component which pulls data from a staging table and drops them into another table in the same database.
Am using a
1) OLE DB Source to get the data from the staging table.
2) OLE DB Destination to insert or push the data into another table of the same database.
3) Script component to get the error rows and to update the staging table column with a flg value.
The rows that throw an error like primary key violation, or any other error should be redirected to the script component and the process should get completed.
The Error Output of the OLE DB Destination doesnt show any columns to be selected for Redirect Row option
The script executes without any error and the records are shown in error path but the records are not updated in the DB.
This is what i have in the script
Public Class ScriptMain
Inherits UserComponent
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim connMgr As IDTSConnectionManager90
Dim txnIdParam As SqlParameter
Dim errorDescParam As SqlParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ErrorConnection
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand("UPDATE STG_TRANSACTION SET ERROR_FLG='Y' AND ERROR_DESC=@errorDescParam WHERE TXN_ID=@txnIdParam ")
fueltxnIdParam = New SqlParameter("@txnIdParam", SqlDbType.BigInt)
errorDescParam = New SqlParameter("@errorDescParam", SqlDbType.VarChar)
sqlCmd.Parameters.Add(errorDescParam)
sqlCmd.Parameters.Add(txnIdParam)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
sqlCmd.Parameters("@txnIdParam").Value = Row.TXNID
sqlCmd.Parameters("@errorDescParam").Value = Row.ErrorCode
End Sub
End Class
Pleas let me know the solution and any help will be appreciated.
SSIS Output On Sql Agent History
Jan 29, 2007I have been using SSIS for about two months now and one of the main difficulties I have had is migrating Sql Agent SSIS jobs around environments.
Usually, the SSIS fails because of permissions to file systems, data bases, etc, but until now, I have had to discover these almost by guessing because the job history never game me any useful information. Also, from what I see, the SSIS logs have been unreliable, at best.
What I have done lately is creating Operating System (CmdExec) Steps as part of the job (not SSIS steps) and inserting the output of dtexec.exe into the history.
This seems to give me information that has been really hard to get, otherwise.
My question is (I am no DBA or ETL expert), is this the best way of getting the output of the package when debugging migration issues? Why doesn't the SSIS Steps have a similar feature of outputting useful info to the job history?
Evenly Splitting Output In SSIS
Oct 25, 2006I would like to split the data stream so that 1 copy can be sent to a lookup task, and the other can be sent as input to another target.
There doesn't seem to be a way to do this in SSIS; but maybe I should be considering other options?
Can anyone suggest a way to copy data to two different sources?
SSIS Retrieving Output Messages
Aug 29, 2007Hi,
I am designing a simple SSIS package that tests database connectivity. To use the same connection for each database, I am changing the "InitialCatalog" property of the Connection Manager object to test each database in a server.
When the connection fails, I can capture a generic error message from the exception in a Try...Catch block, but it doesn't tell me why a connection attempt failed. I have tried Package Logging as well and that was actually less helpful.
I would have stopped there except the Output Window and Progress/Execution Results tabs BOTH show that the reason for the error was a login failure. I am trying to capture the message that appears in either of these windows as they are the most helpful.
Has anyone been able to programmatically capture these messages?
Thanks!!!