Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Extracting VarBinary(MAX) column to Flat File - Facing Issue. Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 11:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
windows_mss (7/30/2014)
Thanks Guys for your guidance...

I have overcomes this problem using XQuery functionality.


Any chance of you posting the code/package you ended up with?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597885
Posted Friday, August 22, 2014 1:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:54 AM
Points: 41, Visits: 93
Hi,

I am facing similar kind of problem.I have an SSIS package which has a flat file source and an oledb destination.
I am transferring flat file content to a table.This SQL table has a column named 'SSN' defined as VARBINARY(300).Because i need to store the encrypted value in it and not the actual value.
I have created an 'Instead of Insert' Trigger on that table.So this trigger will encrypt and store the value in SSN field.

But in text file this SSN column comes as Varchar. How to type cast this varchar to varbinary in SSIS package.
I tried to convert the datatype by using Derived column,Data conversion etc.,But it did not work out.

Can anyone help me on this!

Thanks
Nisha V Krishnan
Post #1606490
Posted Friday, August 22, 2014 3:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:49 PM
Points: 300, Visits: 670
nishav2 (8/22/2014)
Hi,

I am facing similar kind of problem.I have an SSIS package which has a flat file source and an oledb destination.
I am transferring flat file content to a table.This SQL table has a column named 'SSN' defined as VARBINARY(300).Because i need to store the encrypted value in it and not the actual value.
I have created an 'Instead of Insert' Trigger on that table.So this trigger will encrypt and store the value in SSN field.

But in text file this SSN column comes as Varchar. How to type cast this varchar to varbinary in SSIS package.
I tried to convert the datatype by using Derived column,Data conversion etc.,But it did not work out.

Can anyone help me on this!

Thanks
Nisha V Krishnan


As a work around i tried it in two steps
1) in the first DFT i got the data from the flat file to a staging table
2) then in the second DFT i used the SQL Command to cast the column to binary.

As of now this seems to be working.

thanks

Post #1606509
Posted Friday, August 22, 2014 3:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:54 AM
Points: 41, Visits: 93
Thanks for your reply.
The problem I cannot use a staging table since the data is sensitive data.
Post #1606515
Posted Friday, August 22, 2014 4:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:54 AM
Points: 41, Visits: 93
Hi,
As mentioned below i need to convert varchar field to varbinary in SSIS.Please suggest.
PS : *)Cannot use Staging table.
*)Sensitive data(in my case SSN) should be encrypted on the fly. And should be stored in encrypted format.

ISSUE :
******
I am facing similar kind of problem.I have an SSIS package which has a flat file source and an oledb destination.
I am transferring the flat file content to a SQL table.This SQL table has a column named 'SSN' defined as VARBINARY(300).I need to store the encrypted value in it and not the actual value.
I have created an 'Instead of Insert' Trigger on that table.So this trigger will encrypt and store the value in SSN field.

But in the text file this SSN column comes as Varchar. How to type cast this varchar to varbinary in SSIS package.
I tried to convert the datatype by using Derived column,Data conversion etc.,But it did not work out.

Can anyone help me on this!

Thanks
Nisha V Krishnan
Post #1606536
Posted Sunday, August 24, 2014 9:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
nishav2 (8/22/2014)
Thanks for your reply.
The problem I cannot use a staging table since the data is sensitive data.


You're saying that your normal data contains clear-text SSNs? And that you can't build a staging table in that same database?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1606960
Posted Monday, August 25, 2014 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 2,420, Visits: 6,735
Jeff Moden (8/24/2014)
nishav2 (8/22/2014)
Thanks for your reply.
The problem I cannot use a staging table since the data is sensitive data.


You're saying that your normal data contains clear-text SSNs? And that you can't build a staging table in that same database?


A staging table with the right COLUMN Permissions for the sensitive data looks just about right for this application.
Post #1607029
Posted Monday, August 25, 2014 7:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:49 PM
Points: 300, Visits: 670
though MS documentation says that Conversion from DT_STR to DT_BYTES is a legal conversion, Nether data conversion or derived column column compopnents seems be doing this right with out throwing a cast error.

http://msdn.microsoft.com/en-us/library/ms141704.aspx

The only way i was able to cast is using the script componenet.


Row.OUPUTCOLUMN = System.Text.Encoding.UTF8.GetBytes(Row.INPUTCOLUMN);

Not sure if your case how far this will be helpful.

Thanks
rm
Post #1607076
Posted Monday, August 25, 2014 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:54 AM
Points: 41, Visits: 93
Thanks guys for all your valuable suggestions. Will give it a try by using script task..
Post #1607106
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse