Hello. I have a OLE DB Data Source Component that uses an SQL command as the data access mode. On the Connection Manager "tab" of the OLE DB Source Editor I can sucessfully parse my query and produce a preview of the data. However, when I go to the Columns tabI seeno available external columns? Apart from the fact I'm using a union I can't think of any reason whyI can't seeany columns, it doesn't make sense. Please Help. Thanks

do you have a use db clause in the statement? I have received a similar error when Copying and pasting from the management studio that included a use clause before... I know that I have seen this effect in a few other situations, but I can't remember where right now... Edit: It would probably help if you posted your query as well...

There is no USE clause in it but I think I have found out what is going on. The queryuses acolumn froma local temporary table and this is what SSIS doesn't seem to like. The temp table Irefer to iscreated within the SQL that I am passing inso this iswhy I was, and am stillconfused as to why I can seea previewof the dataSSIS seems to "forget"when I go onto the columns tab and there are no available columns. Here is the query infull: DECLARE @IMPORTDATE DATETIME SET @IMPORTDATE = GETDATE() SELECTACCOUNTNUM, ACCOUNT.ACCOUNTID, 'O' AS STATUS, ACCOUNT.MODIFYDATE AS MODIFIEDDATE INTO #ACCSTATUS FROM ACCOUNT JOIN TPTACCOUNTEXTRA ON ACCOUNT.ACCOUNTID = TPTACCOUNTEXTRA.ACCOUNTID SELECT DISTINCT @IMPORTDATE AS ImportDate, #ACCSTATUS.STATUS AS Status, 0 AS Exported, TPTACCOUNTEXTRA.ACCOUNTNUM AS AccountNum, CASE CHARINDEX('The ',ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT),1) WHEN 1 THEN RIGHT(ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT),LEN(ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT))-4) ELSE ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT) END AS Account, ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT) AS DispAccount, TPTACCOUNTEXTRA.ECON, ISNULL(TPTACCOUNTEXTRA.PAYLOCATION,'Unknown') AS PayLocation, CONTACT.PREFIX AS Prefix, CONTACT.FIRSTNAME AS Firstname, CONTACT.LASTNAME AS Lastname, CONTACT.SUFFIX AS Suffix, CONTACT.INITIALS AS Initials, ISNULL(CONTACT.TITLE,CONTACT.DEPARTMENT) AS Title, ADDRESS.ADDRESS1 AS Address1, ADDRESS.ADDRESS2 AS Address2, ADDRESS.ADDRESS3 AS Address3, ADDRESS.CITY AS City, ADDRESS.COUNTY AS County, ADDRESS.COUNTRY AS Country, ADDRESS.POSTALCODE AS Postalcode, TPTADDRESSEXTRA.ADDRESSUNKNOWN AS AddressUnknown, TPTADDRESSEXTRA.OVERSEAS AS Overseas, TPTADDRESSEXTRA.COCOMPANY AS COCompany, TPTADDRESSEXTRA.COCONTACT AS COContact, ISNULL(CONTACT.WORKPHONE,ACCOUNT.MAINPHONE) AS Workphone, ISNULL(CONTACT.FAX,ACCOUNT.FAX) AS Fax, ISNULL(CONTACT.EMAIL,ACCOUNT.EMAIL) AS Email, MODIFIEDDATE FROM ACCOUNT JOIN #ACCSTATUS ON ACCOUNT.ACCOUNTID = #ACCSTATUS.ACCOUNTID JOIN TPTACCOUNTEXTRA ON ACCOUNT.ACCOUNTID = TPTACCOUNTEXTRA.ACCOUNTID JOIN CONTACT ON ACCOUNT.ACCOUNTID = CONTACT.ACCOUNTID JOIN ADDRESS ON CONTACT.CONTACTID = ADDRESS.ENTITYID JOIN TPTADDRESSEXTRA ON ADDRESS.ADDRESSID = TPTADDRESSEXTRA.ADDRESSID AND ADDRESS.ISMAILING = 'T' AND TPTADDRESSEXTRA.ADDRESSUNKNOWN != 'T' JOIN TPTCONTACTROLE ON CONTACT.CONTACTID = TPTCONTACTROLE.CONTACTID AND CONTACTROLE = 'Primary Admin Contact' WHERE ACCOUNT.STATUS = 'Employer' UNION SELECT DISTINCT @IMPORTDATE AS ImportDate, #ACCSTATUS.STATUS AS Status, 0 AS Exported, TPTACCOUNTEXTRA.ACCOUNTNUM AS AccountNum, CASE CHARINDEX('The ',ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT),1) WHEN 1 THEN RIGHT(ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT),LEN(ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT))-4) ELSE ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT) END AS Account, ISNULL(TPTACCOUNTEXTRA.ACCOUNTSHORTNAME,ACCOUNT.ACCOUNT) AS DispAccount, TPTACCOUNTEXTRA.ECON, ISNULL(TPTACCOUNTEXTRA.PAYLOCATION,'Unknown') AS PayLocation, CONTACT.PREFIX AS Prefix, CONTACT.FIRSTNAME AS Firstname, CONTACT.LASTNAME AS Lastname, CONTACT.SUFFIX AS Suffix, CONTACT.INITIALS AS Initials, ISNULL(CONTACT.TITLE,CONTACT.DEPARTMENT) AS Title, ACCADDRESS.ADDRESS1 AS Address1, ACCADDRESS.ADDRESS2 AS Address2, ACCADDRESS.ADDRESS3 AS Address3, ACCADDRESS.CITY AS City, ACCADDRESS.COUNTY AS County, ACCADDRESS.COUNTRY AS Country, ACCADDRESS.POSTALCODE AS Postalcode, ACCTPTADDRESSEXTRA.ADDRESSUNKNOWN AS AddressUnknown, ACCTPTADDRESSEXTRA.OVERSEAS AS Overseas, ACCTPTADDRESSEXTRA.COCOMPANY AS COCompany, ACCTPTADDRESSEXTRA.COCONTACT AS COContact, ISNULL(CONTACT.WORKPHONE,ACCOUNT.MAINPHONE) AS Workphone, ISNULL(CONTACT.FAX,ACCOUNT.FAX) AS Fax, ISNULL(CONTACT.EMAIL,ACCOUNT.EMAIL) AS Email, MODIFIEDDATE FROM ACCOUNT JOIN #ACCSTATUS ON ACCOUNT.ACCOUNTID = #ACCSTATUS.ACCOUNTID JOIN TPTACCOUNTEXTRA ON ACCOUNT.ACCOUNTID = TPTACCOUNTEXTRA.ACCOUNTID JOIN ADDRESS ACCADDRESS ON ACCOUNT.ACCOUNTID = ACCADDRESS.ENTITYID JOIN TPTADDRESSEXTRA ACCTPTADDRESSEXTRA ON ACCADDRESS.ADDRESSID = ACCTPTADDRESSEXTRA.ADDRESSID JOIN CONTACT ON ACCOUNT.ACCOUNTID = CONTACT.ACCOUNTID JOIN ADDRESS ON CONTACT.CONTACTID = ADDRESS.ENTITYID JOIN TPTADDRESSEXTRA ON ADDRESS.ADDRESSID = TPTADDRESSEXTRA.ADDRESSID AND ADDRESS.ISMAILING = 'T' AND TPTADDRESSEXTRA.ADDRESSUNKNOWN = 'T' JOIN TPTCONTACTROLE ON CONTACT.CONTACTID = TPTCONTACTROLE.CONTACTID AND CONTACTROLE = 'Primary Admin Contact' WHERE ACCOUNT.STATUS = 'Employer' As a workaround I have placed the query into a stored procedure with the large union query being written into a physical table. The sp then simply does a select * from the pyhsical table so there's obviously no problems for SSIS when it's done this way. I'm still interested into why the original query doesn't work though, DTS could handle local temporary tables in SQLscript tasksso I expected SSIS to be able to do the same. There maybe a fix for it I don't know,my SSIS version is SP1 (9.0.2153). Thanks

Well, SSIS does *not* like temporary tables - as you've found out. You can find a workaround at Jamie Thomson's site, http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx.There are two primary issues with temporary tables and SSIS: temporary tables are generally scoped to the connection being used, unless they're made globally (## vs #), and SSIS is very strongly tied to the data shape. The reason preview works is your connection has not been released after the creation of the temporary table; columns do not appear because the temporary table cannot be found and validated, as the connection has been released and recreated.I believe DTS operated on a single connection throughout the operation; thus the temporary table was available for examination.I would recommend installing SP2 for SQL Server/SSIS. There're a number of fixes for SSIS in SP2, and a large number of performance gains.I'd be curious to see if a table variable would produce the same results as a temporary table.-bms

bobbins, Three questions: 1 - Why are you selecting into variables? 2 - What do you intend to do with this output? (from question #1) 3 - Can you simply eliminate the temp table and use a subselect instead?

Hello Phil, in response to your questions 1 - I am not entirely sure myself as It's not actually SQL that I wrote, it was done by someone who has now left our companyand I have had to pick it up the maintenance of this former DTS package that I'mnow attemptingmigrating to SSIS 2- the output is loaded into a tableon aremote server 3- I suppose I could in this scenario but we have many DTS packages thatuse theoutput of temporary tables that may not be as simple to change. As I kind of elaborated to in 1,I'm trying to get all the DTS2000 packages we have migrated to SSIS but Ikeep finding someit's features (or annoyances depending on your take of it) are slowing down the migrations. Iguess the lesson here is not to use theoutput of temporary tablesin data flow tasks but I'd prefer it if it worked in the old DTS context purely from the selfish pointof view that it would make mylife a tiny bit easier! Cheers

The same thing happens with both temp tables and table variables. This is a serious quirk/bug that needs a fix or reasonable workaround, because there are situations like mine where you can't just move the code into a SPROC (the code works in SSMS 2005 and SSIS 2005 can both parse and execute/preview the results, but no dice with the columns showing up). Here's my code (temp tables)- SET FMTONLY OFF --SET NOCOUNT ON IF 1 = 0 BEGIN SELECT CAST (NULL AS INT ) AS [Year], CAST (NULL AS INT ) AS [Month], CAST (NULL AS FLOAT ) AS [Transaction Amount], CAST (NULL AS INT ) AS [Transaction Count], CAST (NULL AS FLOAT ) AS [Check Amount], CAST (NULL AS INT ) AS [Check Count], CAST (NULL AS FLOAT ) AS [DirectPay Amount], CAST (NULL AS INT ) AS [DirectPay Count], CAST (NULL AS FLOAT ) AS [Total Amount], CAST (NULL AS INT ) AS [Total Count] END DECLARE @FirstDayof11MonthsAgo as SmallDatetime DECLARE @CurrentDay as SmallDatetime DECLARE @FirstDayof11MonthsAgoString as Char(10) DECLARE @CurrentDayString as Char(10) SELECT @CurrentDay = GETDATE() IF DATEPART(d, @CurrentDay) = 1 BEGIN SELECT @CurrentDay = @CurrentDay - 1 END SELECT @FirstDayof11MonthsAgo = DATEADD(m, - 12, @CurrentDay) SELECT @FirstDayof11MonthsAgo = DATEADD(d, - DATEPART(d, @FirstDayof11MonthsAgo) +1,@FirstDayof11MonthsAgo) SELECT @CurrentDayString = CAST(DATEPART(mm, @CurrentDay) AS varchar) + '/' + CAST(DATEPART(d, @CurrentDay) AS varchar) + '/' + CAST(DATEPART(yy, @CurrentDay) AS varchar) SELECT @FirstDayof11MonthsAgoString = CAST(DATEPART(mm, @FirstDayof11MonthsAgo) AS varchar) + '/' + CAST(DATEPART(d, @FirstDayof11MonthsAgo) AS varchar) + '/' + CAST(DATEPART(yy, @FirstDayof11MonthsAgo) AS varchar) SELECT DATEPART(yyyy, at.postingdt) AS [Year], DATEPART(m, at.postingdt) AS [Month], SUM(at.billingamount) AS [Transaction Amount], COUNT(*) AS [Transaction Count] INTO #Transactions FROM accountTransaction AS at WITH (NOLOCK) WHERE at.postingdt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString AND at.transactionType <> 'Y' GROUP BY DATEPART(yyyy, at.postingdt), DATEPART(m, at.postingdt) SELECT DATEPART(yyyy, pcl.createDt) AS [Year], DATEPART(m, pcl.createDt) AS [Month], SUM(pcl.Amount) AS [Check Amount], COUNT(*) AS [Check Count] INTO #Checks FROM purchaseCardLog AS pcl WITH (NOLOCK) INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId WHERE (pt.PaymentTypeName ='CHECK') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString) GROUP BY DATEPART(yyyy, pcl.createDt), DATEPART(m, pcl.createDt) SELECT DATEPART(yyyy, pcl.createDt) AS [Year], DATEPART(m, pcl.createDt) AS [Month], SUM(pcl.Amount) AS [DirectPay Amount], COUNT(*) AS [DirectPay Count] INTO #DirectPays FROM purchaseCardLog AS pcl WITH (NOLOCK) INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId WHERE (pt.PaymentTypeName = 'SETTLE') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString) GROUP BY DATEPART(yyyy, pcl.createDt), DATEPART(m, pcl.createDt) SELECT tr.[Year], tr.[Month], ISNULL(tr.[Transaction Amount],0.00) AS [Transaction Amount], ISNULL(tr.[Transaction Count], 0) AS [Transaction Count], ISNULL(ch.[Check Amount], 0.00) AS [Check Amount], ISNULL(ch.[Check Count], 0) AS [Check Count], ISNULL(dp.[DirectPay Amount], 0.00) AS [DirectPay Amount], ISNULL(dp.[DirectPay Count], 0) AS [DirectPay Count], ISNULL(tr.[Transaction Amount],0.00) + ISNULL(ch.[Check Amount], 0.00) + ISNULL(dp.[DirectPay Amount], 0.00) AS [Total Amount], ISNULL(tr.[Transaction Count],0) + ISNULL(ch.[Check Count], 0) + ISNULL(dp.[DirectPay Count], 0) AS [Total Count] FROM #Transactions AS tr LEFT JOIN #Checks AS ch ON tr.[Year] = ch.[Year] AND tr.[Month] = ch.[Month] LEFT JOIN #DirectPays AS dp ON tr.[Year] = dp.[Year] AND tr.[Month] = dp.[Month] ORDER BY 1,2 DROP TABLE #Transactions DROP TABLE #Checks DROP TABLE #DirectPays