Home TSQL relace a string text with variable
Reply: 0

TSQL relace a string text with variable

user1299
1#
user1299 Published in June 19, 2018, 4:14 pm

I am trying to replace the folder location in line three of the code below (Set @ProjectBinary ) to a variable.

The original code:

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

I have created a variable: DECLARE @Location AS varchar(250) = 'C:\SSIS\DWH_IS.ispac'

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = ('SELECT * FROM OPENROWSET(BULK ' + @Location + ', SINGLE_BLOB') as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

I keep getting an error: Msg 257, Level 16, State 3, Line 61 Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Any suggestions on how to change this to get the code running please?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.393627 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO