SSIS Handling of Hex Strings - Zeros out value when passed as parameter to stored procedure
Hi Ladies and Gents, I'm having a little issue with SSIS specifically in dealing with Hex strings coming out of the SYS.FN_CDC_GET_MAX_LSN() function. I'll describe the dataflow a little so you can gain some familiarity. 1. Get max lsn from stored procedure and store as string variable in SSIS. a. The output parameter of the proc is a varchar(40) and I use a call to sys.fn_varbintohexstr() to convert the binary value from the sys.fn_cdc_get_max_lsn function. b. The first time the procedure is called with no input parameter it works fine. 2. I do a bunch of work in a dataflow based on the value 3. I make a call to that same stored procedure using the string value that was just returned by it. Inside the stored procedure there is logic to be performed given a specific value of this parameter. When I get to the pre-execute break on step 3 I see a value for this variable. When I get to post-execute a null is returned for some reason. I debugged this a little and noticed SSIS sending a null to the proc in profiler!! declare @p4 int set @p4=NULL Does anyone know why SSIS is taking my hex string and blowing it away, specifically between the pre-execute and actual execution of the execute sql task? Thanks! Jon
January 24th, 2011 6:07pm

Hard to understand the actual elements you are using within SSIS from your description, but I will say that SSIS is very hard to work with when it comes to passing parameters. Most everything is possible, but the data connection type and many other factors come into play when passing a var back into a task. 90% of the time, it's either a limitation of the conn type (some will only accept column name parameters, not tablename etc) or, it's a syntax issue. Sometimes you can use @var syntax, sometimes it's '?' only
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 6:31pm

Thanks for the reply JW, For clarity, both calls to the stored procedure in question are using an OLEDB connection and have their parameters configured properly. Jon
January 24th, 2011 6:36pm

In light of JW's comment, I decided to recreate the object via copy/paste (as was done the first time). Now it works fine... Just a fluke?
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 6:42pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics