1

SSIS newbie here.

I have an SSIS package I created based on the wizard. I added a SQL task to run the script I was running previously separately, in order to reduce the process to one step. The script uses lots of temp tables, and one global ##temp at the end to make the result accessible outside the process.

When I try to execute the package, I get a complex "Package Validation Error" (error code 0x80040E14). I think the operative part of the error message is "Invalid object name '##roster5'."

I just realized it was the Data Flow task that was throwing the error, so I tried to put another SQL Task before everything else to create the table so that Data Flow task would see that the table is there; but it is still giving me the error: "Invalid object name '##ROSTER_MEMBER_NEW5'."

What am I missing/doing wrong? I don't know what I don't know. It seems like this shouldn't be that complicated (As a newbie, I know that this is probably a duplicate of...something, but I don't know how else to ask the question.)

hmmmmm
  • 15
  • 8
  • There a few solutions to this. First tell me how many rows you expect in ##roster5, ##roser)member_news5. – plditallo Sep 05 '17 at 20:27
  • oops, ##roster5 was just short for the full name; there are approximately 5 million rows produced by the script – hmmmmm Sep 05 '17 at 20:51
  • You'll see some suggestions to convert your #temp table to a table variable (e.g. @temptable) . The optimizer will treat variable table as a table with a single row which will impact the performance. You will also see other suggestions, such as set frmt off (https://stackoverflow.com/questions/1579476/using-temp-tables-in-ssis ) . This too will cause a performance hit. I would create a staging table with an identifiable schema, populate it, then when through truncate. – plditallo Sep 05 '17 at 21:29
  • A table variable for >5M rows??!??!! I've tried adding several of the things suggested by the thread you posted. In the PROC directly: SET NOCOUNT ON SET FMTONLY OFF SELECT TOP 0 field = CAST(NULL AS INT) IF 1 = 0 BEGIN -- Publish metadata SELECT CAST (NULL AS INT ) AS id, CAST (NULL AS NCHAR ( 10 )) AS [Name], CAST (NULL AS NCHAR ( 10 )) AS SirName END In the calling object: IF 1 = 0 BEGIN SELECT CAST(null as int) AS Foo END; Exec usp_WaterfallLogic – hmmmmm Sep 05 '17 at 22:05
  • One other thing: In the object to pre-create the table: SELECT TOP 0 field = CAST(NULL AS INT); if OBJECT_ID('tempdb..##ROSTER_MEMBER_NEW5') is not null drop table ##ROSTER_MEMBER_NEW5 create table ##ROSTER_MEMBER_NEW5 (... – hmmmmm Sep 05 '17 at 22:26
  • As @plditallo suggested, the best way to go about is to replace the summary temp table from which you are deriving the result with a staging table. Truncate the table at the start of the procedure. – VKarthik Sep 05 '17 at 23:30
  • I don't have permission to create/drop/truncate as myself. So I use an alternate login (which I should be using sparingly) and I can log in (SQL authentication) while developing, but when I run (in SSIS) it won't remember the pw. NOW what am I doing wrong? – hmmmmm Sep 06 '17 at 15:01
  • wow, that was short-lived; the alternate log in is going away, so I am back to square one: I won't be able to create actual tables; I guess this entire thing is a dead-end – hmmmmm Sep 06 '17 at 15:41

2 Answers2

0

Based on your responses, another option would be to add a T-SQL step in a SQL Agent job that executes stand-alone T-SQL. You would need to rethink the flow control of your original SSIS package and split that into 2 separate packages. The first SSIS package would execute all that is needed before the T-SQL step, the next step would execute the actual T-SQL needed to aggregate, then the last step would call the second package, which would complete the process.

I'm offering this advice with the caveat that it isn't advisable. What would work best is to communicate with your DBA, who will be able to offer you a service account to execute your SSIS package with the elevated privileges needed to truncate the staging table that will need to exist for your process to manage.

plditallo
  • 701
  • 2
  • 13
  • 31
0

I actually want to post a non-answer. I tried to follow the advice above as well as I could, but nothing worked. My script was supposed to run, and then the data pump was supposed to, essentially copy the content of a global temp to another server/table. I was doing this as two steps, and tried to use SSIS to do it all in one step. there wasn't really a need to pass values within SSIS from component to component. It doesn't seem like this should be that hard.

In any event, as I said nothing worked. Ok, let me tell what I think happened. After making a lot of mistakes, a lot of undo's, and a lot of unsuccessful attempts, something started working. One of the things I think contributes is that I had set the ResultSetType to ResultSetType_None, since I wouldn't be using any results from that step. If anyone thinks that's not what happened, I'm happy to hear the actuality, since I want to learn.

I consider this a non-answer, because I have little confidence that I'm right, or that I got it by anything other than an accident.

hmmmmm
  • 15
  • 8