Here is a TVF:

CREATE FUNCTION myFunctioname
(
    @LdryCode as varchar(100),
    @InvtAcctCode as varchar(100),
    @CustName as varchar(100),
   ...

Why don't you take the loop out and do it all in one statement?

It isn't the ASCII function that's causing your error:

Try these statements:

SELECT ASCII('.');
SELECT ASCII(NULL);
SELECT ASCII(42);

All of them return a valid value.

But these all cause the same error you...

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the...

SQL Server will clean up temporary table when the stored procedure has finished but you can also drop the temporary tables within the stored procedure whenever they are finished with...

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the TABLOCK option...

Do you know which index takes the longest to create or are they both about the same?

You can use MAXDOP in the options when creating an index, which might speed...

On one of our systems we don't give any permissions to any tables or views to users, users only get permissions to a specific list of stored procedures.

If...

The temp table needs to have all the columns in the select.

After the first insert into the temp table you just insert rows that don't already exist in the temp...

I think it should solve your issues...

Create a temporary table with these columns

"ZTBR_TransactionCode",
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Internal_Order",
"Trading_Partner",
"Amount_in_Company_Code_Currency",
"Company_Code_Currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Region_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key",
"Entity_Secondary_Key",
"Master_BRACS_Secondary_Key",
"Acct Type",
"Level 1",
"Level 2",
"Level 3",
"GCoA",
"Account Desc",
"EXPENSE FLAG",
"BRACS",
"BRACS_DESC",
"Primary_ZTBR_TransactionCode",
"BRACS Account Description",
"CLASSIFICATION",
"Direct_Primary_Key"

Insert the rows from the fist query into the temp table

Then insert the...

You could create a temporary table.

Then insert the rows from each query into the temporary table with a WHERE NOT EXSITS (SELECT * FROM temp_table_name WHERE ...) at the end...

Well is says in the spec you should do a UNION of selects.

So I would think you can just have several select statements with inner joins and UNION ALL them...

If you follow the Kimball methodology for dimension tables you will include rows with keys with negative values in the dimension table, then you have values in the FK columns...