question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked ChristopherJack-1763 commented

Replacing sql seems to be causing failure

Hi,

I have the following tsql code in SSIS.

 t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),

The line worked before I added the replace...

Am I using it correctly?


Full SQL is

 declare @t nvarchar(max)
 declare @tablename nvarchar(128)
 select @tablename = ?
    
 select @t = 'Update t
 set t.CampaignCode = s.CampaignCode,
 t.CampaignDescription = s.CampaignDescription,
 t.MediaDescription = s.MediaDescription,
 t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),
 t.PromotionCode = s.PromotionCode,
 t.PromotionDescription = s.PromotionDescription
 FROM ' + @tablename +  '  t
 left outer join
 MaginusStaging.dbo.MediaCode_CampaignCode s
 on
 t.MaginusMediaCode = s.MediaCode
 where t.MaginusMediaCode IS NOT NULL'
    
 exec sp_executesql @t




Thanks for help.




sql-server-transact-sqlsql-server-integration-services
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It all depends on what you want to achieve.

Could you clarify a little more what you are up to? Please also clarify "failure". Do you get an error message? Unexpected results? Something else?

By the way, what is the data type of NoOfCats?

0 Votes 0 ·

Thanks Elland for responding.

The noofcat field is nvchar or varchar.

I am getting a compile error in ssis.

Basically the sales team upload the data into our sales system and the ssis extracts and converts it as an integer. We can't change the sales system so we convert it to an int in ssis.

The varchar has it has '4,234' but we need to remove the comma as if that is in it fails the cast.

0 Votes 0 ·

I am getting a compile error in ssis.

Which is top secret, since you don't share it?

What is the full statement where this line occurs in?

1 Vote 1 ·
Show more comments

While asking a T-SQL question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

If you are using SSIS Derived Column Transformation, please provide more details.

0 Votes 0 ·

Hi @ChristopherJack-1763 ,

Could you please share the whole tsql code in SSIS?

May I know if you use the ssis expression?

If so, please refer to Integration Services (SSIS) Expressions, REPLACE (SSIS Expression) and Cast (SSIS Expression) .

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·

Hi Moalv, thanks for getting back to me.. I would but our SSIS server bricked so we dont currently have access to anything .

Will have a look at what you suggested though.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered ErlandSommarskog commented

Please try the following.
The issue was pertaining to the single quotes inside single quotes overall.
They need to be doubled.

SQL

 DECLARE @t NVARCHAR(MAX);
 DECLARE @tablename NVARCHAR(128);
 SET @tablename = ?
        
 SET @t = 'UPDATE t
  SET t.CampaignCode = s.CampaignCode,
  t.CampaignDescription = s.CampaignDescription,
  t.MediaDescription = s.MediaDescription,
  t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,'','','''') AS INT), 0),
  t.PromotionCode = s.PromotionCode,
  t.PromotionDescription = s.PromotionDescription
  FROM ' + @tablename +  '  t
  LEFT OUTER JOIN
  MaginusStaging.dbo.MediaCode_CampaignCode s
  ON t.MaginusMediaCode = s.MediaCode
  WHERE t.MaginusMediaCode IS NOT NULL';
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Yitzhak -

The error I am getting once change to double quoates is

Error: 0xC002F210 at Update Campaign Codes, Execute SQL Task: Executing the query "declare @t nvarchar(max)
declare @tablename nvarch..." failed with the following error: "An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Campaign Codes

The only change is adding the replace function if I remove the replace and just have the cast it works fine ??

0 Votes 0 ·

IMHO, it would better to create a stored procedure with a parameter for a table name.

0 Votes 0 ·

When I Yitzhak's code, it fails with an error that the table is missing, which is not surprising, since I just put in a dummy name.

On the other hand, I don't know what table name you are passing. If your table names has spaces or other special characters, interesting things can happen.

Add this line after the first assignment to @tablename.

SET @tablename = quotename(object_schema_name(object_id(@tablename))) + '.' + 
                      quotename(object_name(object_id(@tablename)))


This will wrap schema and name in brackets, so that if you pass My table, you will get [dbo][My Table] .

If you need to include the database name in the parameter, it gets more complicated.

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
1 Vote"
JeffreyWilliams-3310 answered

Remove the 't.'

  t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),

Should be:

 NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.