Databases
StackOverflow2013
Postsdbo
POSSIS Multiple Unknow Column Updates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS Multiple Unknow Column Updates
    primarykey
    data
    text
    <p>I wonder if anyone has come across a similar situation before that could point me in the right direction..? I'll add that it's a bit frustrating as someone has replaced the NULL value with a text string containing the word 'NULL' - which I need to remove.</p> <p>I have 6 quite large tables, over 250+ columns and in excess of 1 million records in each and I need to update the columns where the word NULL appears in a row and replace it with a proper NULL value - the problem is that I have no idea in which column this appears.</p> <p>As a start, I've got some code that will list every column with a count of the values and anything that looks to have a lower count than expected, I'll run a SQL query to ascertain if the column contains the string 'NULL' and using the following code, replace it with NULL.</p> <pre><code>declare @tablename sysname declare @ColName nvarchar(500) declare @sql nvarchar(1000) declare @sqlUpdate nvarchar(1000) declare @ParmDefinition nvarchar(1000) set @tablename = N'Table_Name' Set @ColName = N'Column_Name' set @ParmDefinition = N'@ColName nvarchar OUTPUT'; set @sql= 'Select ' + @ColName + ', Count(' + @ColName + ') from ' + @tablename + ' group by ' + @ColName + '' Set @sqlUpdate = 'Update ' + @tablename + ' SET ' + @ColName + ' = NULL WHERE '+ @ColName + ' = ''NULL''' print @sql print @sqlUpdate EXECUTE sp_executesql @sql, @ParmDefinition, @ColName=@ColName OUTPUT; EXECUTE sp_executesql @sqlUpdate, @ParmDefinition, @ColName=@ColName OUTPUT; </code></pre> <p>What I'm trying to with SSIS is to iterate through each column, </p> <pre><code>Select Column_Name from Table_Name where Column_Name = 'NULL' </code></pre> <p>run the appropriate query, and perform the update.</p> <p>So far I can extract the column names from <strong>Information.Schema</strong> and get a record count from the appropriate table, but when it comes to running the actual UPDATE statement <strong>(as above, sqlUpdate)</strong> - there doesn't seem to be a component that's happy with the dynamic phrasing of the query. </p> <p>I'm using a <strong>Conditional Split</strong> to determine where to go if there are records (which may be incorrect) and I've tried <strong>OLE DB Command</strong> for the update.</p> <p>In short, I'm wondering whether SSIS is the best tool for this job or whether I'm looking in the wrong place!</p> <p>I'm using SSIS 2005, which may well have limitations that I'm not yet aware of!</p> <p>Any guidance would be appreciated.</p> <p>Thanks,</p> <p>Jon</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJonTout
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload