I have a T-SQL script that I must execute weekly and was wanting to automate it via SSIS. One of the commands in the script is ALTER ASSEMBLY [INA.Common.Security.SQL] DROP FILE ALL ADD FILE FROM 0xEFBBB... AS N'Decrypt.cs', 0xEFBBB... AS N'EncryptionAES.cs', 0xEFBBB...AS N'Properties\AssemblyInfo.cs', 0x4D696...AS N'bin\Debug\INA.Common.Security.SQL.pdb'; I believe the SQLStatement properto of the SSIS Execute SQL Task is too small to hold the full values as I am getting an error about missing file names. My question is what is the size of the string that the SQLStatement will hold?

I don't think that is your problem. That statement is not very long. It looks to me like the files cannot be found. In your statement, make sure you change the path for the files to the full path rather than a relative path. The default folder is probably not the one you expect.Craig Bryden - Please mark correct answers

Hello, The code you have does not look that lengthy, Editor can handle way lengthy code. Here is post you can see for the length of statement you can have in Editor http://www.bigresource.com/Tracker/Track-ms_sql-CpbMEMX3/ Thankshttp://sqlage.blogspot.com/

I have a T-SQL script that I must execute weekly and was wanting to automate it via SSIS. One of the commands in the script is The other replies have probably answered your main qquestion however I'd like to know why you're creating a SSIS package just so you can schedule execution of your T-SQL? You would be better off just creating a SQL Agent Job to execute your script. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

The text I included in the OP is not the complete statement, the complete statement would disclose the security key of the functions - and fill up several pages of your browser. Running a word count on the T-SQL script (that does execute in SSMS) I get 65223 characters. I've tried breaking that script into 2 different SSIS SQL tasks but that did not help. Thanks for the response.

I have a T-SQL script that I must execute weekly and was wanting to automate it via SSIS. One of the commands in the script is The other replies have probably answered your main qquestion however I'd like to know why you're creating a SSIS package just so you can schedule execution of your T-SQL? You would be better off just creating a SQL Agent Job to execute your script. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD One of the steps is unzipping a pwd protected file, another is zipping into a new pwrd file and yet another is sending the zip file via ftp and there are a couple file system tasks as well.

The designer is limited to 32767 chars. I suggest you use a stored procedure instead of raw SQL to run your T-SQL code. Another possible approaches: 1) Use the variable as the source for the query and you will fit 8000 chars (which might be enough); and 2) Use File as your source of the query. Arthur My Blog

The designer is limited to 32767 chars. I suggest you use a stored procedure instead of raw SQL to run your T-SQL code. Another possible approaches: 1) Use the variable as the source for the query and you will fit 8000 chars (which might be enough); and 2) Use File as your source of the query. Arthur My Blog Ahh, yeah that's the problem. The longest line is 35,891 characters. Let me expound on the process. Every week a contractor (hired by former boss with no input from IT) sends us a backup of a 50 GB database, I have to restore that to our server and then send it to a third party. The database contains personally identifiable information (PII) that the third party is not allowed to see. The contractor has implemented the security functions to encrypt the PII and this is what I'm having trouble with at the moment. After some file system tasks, the first database related task is to restore the backup to our server, overwriting the existing database. If I were to create a stored proc it would be lost when the next backup is restored as are the previous week's created assemblies.

Then use either approach 1 or 2.Arthur My Blog

Then use either approach 1 or 2. Arthur My Blog Approach 1 won't work because, as I posted, the line length is over 35K characters. And I'm trying to obtain the file from the contractor, but they're very recalcitrant. But, you answered my original question which is that the SQLStatement property is too small for the amount of data that I have to send to the server. Thanks.

The variable will hold 8,000 characters which is enough for your SQL statement.Arthur My Blog

The variable will hold 8,000 characters which is enough for your SQL statement. Arthur My Blog How can I fit 35K+ characters into a variable with max size 8K?

A variable inside SQL like http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-2000-when-8000-characters-is-not-eno the trick is you store it the SQL Sever as textArthur My Blog

But then yes, the file is the natural choice.Arthur My Blog